Unanswered: Same field data source gives different report values
I was asked to make some changes to an existing job cost report. The report is set up with a job cost header, a subreport for the detail section and a job cost footer (as well as a report header/footer but they are not involved in this question)
The Job Cost header provides information about the job including some cost totals
The detail section provides a list of open PO's with the amount and then calculates the total at the bottom of the list
The footer had some hidden fields that the report creator used to help generate the final total at the bottom of the report.
I have a field in the header that should display the sum of the open PO's from the detail sub-report. I have it linked to the total field in the sub-report. When I run the report for just a single job everything works out just great. When I run the report with two or more jobs it goes haywire.
The total in the sub-report calculates correctly and the report grand total is correct but the value in the job cost header is wrong.
For two jobs both of the header values are the same.
Three jobs results in the following for the header value (again the detail is correct)
job 1: value for job 3
job 2: correct
job 3; same value as job 2
I was trying to figure out what was wrong so I added a field in the job cost footer with the exact same data value as the field in the header and it calculates the value correctly. I tried linking the header field directly to the footer field but the header still display incorrectly!
"Running Calculations" in a report are performed as the report runs - in other words, position in the report will affect the value.
If you need to have the sum of all values in a field in the header of group, make a seperate "totals" query that calculates the sum and add it to your report's source so that it is calculated prior to the report running.
I've found that doing too many calculations in the report can be tricky and not very robust, so I try to "pre calculate" as much as possible without hurting performance.