Report shows calculation of check value wrong

[emma313823]

All,

I'm trying to understand why the report I created sums the data the way it does.

I've tried to create a (what I think is a simple) report and I've tried creating it from a table and a query with the same results. I added a total to the report to sum my check value field, however regardless of new data being added for the filtered period it shows the same value.

After creating the report, I went to layout view, put my cursor into the Check value field, then went to Report Layout Design and selected Totals from the Grouping & Totals ribbon and selected Sum. A calculated field was placed on the report.

I then placed my cursor into the Pay_Date field, then on the Home Ribbon, selected the Filter and simply selected 07/2024.

I attempted this another way, by ensuring no filters were on the data, then placed the cursor in the date field of the report layout view, went to the Sort & Filter on the ribbon and this time selected Selection and this time selected Between 7/1/2024 and 7/31/2024 and this now shows the correct grand total.


this one is using the selection between dates yielding a value of $170,200.75

this one is just using the filter and then selecting the mm/yyyy option yielding a value of $115,390.93 and it does not matter how many new check values I add in July date range...the value remains unchanged.

Would anyone have any feedback why one worked, and one did not?

Emma

 

[The_Doc_Man]

I've tried to create a (what I think is a simple) report and I've tried creating it from a table and a query with the same results. I added a total to the report to sum my check value field, however regardless of new data being added for the filtered period it shows the same value.

This sentence gives me a CLUE (nowhere near a certainty). Reports ignore some elements of your GROUP BY and ORDER BY clauses and require you to use the report's built-in ordering and grouping tools. The query's grouping and ordering have no meaning. Be sure that you are not relying on the query's aggregate computations because the report's aggregations could be done with different grouping that the query and if you didn't know to look for it, you would never realize it.

You DID say that you went through the report ORDER and GROUP options, so there is some hope that you did that right. However, it IS the first place I would look for issues with apparently incorrect grouping or aggregate results dependent on the grouping.

 

[Mike Krailo]

That sure is odd. What is the reports record source look like (SQL View)? You might have a persistent filter string in there that has to be removed manually while in design view.

You can check what the filter is actually set to from the immediate window. While the report is currently open and it does not have any filter applied, press Alt-F11 to go to code view, Then press Ctrl-G to move to the immediate window. You can look at the full filter string as follows. Use your actual report name instead rptName. Make sure there is no filter string. Then Alt-F11 back to the report and set the desired filter. Go back to the immediate window and check that filter string again and make sure it being correctly set.

?reports!rptName.Report.Filter

It should look something like this if the filter string is correct

([rptName].[Pay_Date] Between #7/1/2024# And #7/31/2024#)

 

[Pat Hartman]

Reports are linier. Each record is processed one after the other. Accumulations happen and totals are displayed at group breaks and at the end of the report. The point is that the total of the report is not known until you have processed the last record. You have the total in your report header. That means that if the report is smart, it figures out that it need to perform the calculations in two passes. It needs to run the report to calculate everything and then go back and change all the headers so that the displayed value represents the ending value.

I've done this so I'm pretty sure it works just as page 1 of x works using the same technique. However, I'm not sure I allow the user to filter the report while it is open. That may be interfering.