Information générale
Catégorie:
Gestionnaire de rapports & Rapports
Peter,
SQL formats a field based on the first one it sees, so if the first item in field#1 is 0 and the first item in field#2 is 3.45, you will get "rounding" in field#1 and not in field#2.
The solution for this is SELECT MyField + 0.00 AS MyField. (For characters SELECT PADR(MyField, 20) AS MyField.)
Another thing I have run into is the formatting of the report. Instead of 99,999.99 , a couple of times I have typed 99,999.00 - which always shows zero's in the cents area. Took me awhile to find those!
Something to check outside of the report, take your cursor and do what the report does. Try CALCULATE SUM(MyField#1), SUM(MyField#2) - the results will show in the Fox desktop. This will help you decide whether the problem is in the cursor, or the report calculations, or the report formatting.
Happy debugging!
>I have a report that has two - almost identical - fields. The data for the report is gathered by a large SQL statement and stored in a cursor. Both fields are SUMS of field in a table based off of a starting date. The ONLY difference - field #1 is data from a date in the middle of the month, field #2 is data from the first of the month.
>When I print the two fields on the form, field #2 (the "more" data) prints perfectly (ex: $7.50)... field #1 (the "less" data) prints $8.00. More research shows that their is DEFINATELY no additional records for either SUM that causes the descrepency. In addition, any "client" on this report, the "change" is rounded up for field #1.
>
>I have gone as far as copying field #2, pasting the copy on the form and editing the field name to verify their isn't any "hidden" mathematics or other settings causing the difference.
>
>
>
>
>Select client.firmid,client.clientnum,client.clientname,log.logdate,;
> sum(iif((code.codetype=="Billings" or ;
> code.codetype="WIP Adjustment" or ;
> code.codetype="Time" or ;
> code.codetype="WIP Start Bal") ;
> and log.logdate> iif(code.codetype=="Billings",log.rate*-1,log.rate),0)) as BegBal,;
> sum(iif((code.codetype="Time" or code.codetype="WIP Start Bal" or ;
> code.codetype="Expense")and log.logdate>=gStartDate,log.rate,0)) ;
> as Charged, ;
> sum(iif((code.codetype="Time" or code.codetype="WIP Start Bal" or ;
> code.codetype="Expense")and log.logdate>=gYearBeg,log.rate,0)) ;
> as ChargedYTD, ;
> sum(iif(code.codetype="WIP Adjustment" and ;
> log.logdate>=gStartDate,log.rate,0)) as Adjusted, ;
> sum(iif(code.codetype="WIP Adjustment" and ;
> log.logdate>=gYearBeg,log.rate,0)) as AdjustedYTD, ;
> sum(iif(code.codetype=="Billings" and ;
> log.logdate>=gStartDate,log.rate,0)) as Billed, ;
> sum(iif(code.codetype=="Billings" and ;
> log.logdate>=gYearBeg,log.rate,0))as BilledYTD ;
> from Timepro!client inner join timepro!log on client.clientid = log.clientid ;
> inner join timepro!code on log.codeid = code.codeid ;
> where client.firmid=gFirmid and log.logdate<=gEndDate;
> into cursor cursWIPsum ;
> group by client.clientname ;
> order by client.clientnameHere is the SQL for reference:
>
>
>Any other suggestions?
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement