Need Previous Field but grouping does not give correct field

[modglin]

I have a report that is grouped by Date and then by Location.
In the database there is a field EndingGallons.
In the report I need to get the Ending Gallons from the prior day.

Since I am grouping by Date first and not location, when I try to pull in the previous({EndingGallons}) it does not give me the previous for that particular location.

Some of the fields that are used in the table

{Date}, {Location}, {Product}, {EndingGallons}, {Receipts}, {GallonsSold}

The report should look like this example:

11/1/06
Location (Field Names)                   Product 1
100       previous({EndingGallons})         6,992	
          {receipts}                        4,000
          Total                            10,992
          {Ending Gallons}                  5,816
          Qty Acct For (Ttl-End Gal)        5,176
          {GallonsSold}                     5,131
          Over Short (Qty Acct For-Gal Sold)  -45 

200       previous({EndingGallons})         5,451	
          {receipts}                        5,400
          Total                            10,851
          {Ending Gallons}                  5,795
          Qty Acct For (Ttl-End Gal)        5,055
          {GallonsSold}                     5,032
          Over Short (Qty Acct For-Gal Sold)  -32 

11/2/06
100       previous({EndingGallons})         5,816	
          {receipts}                        4,770
          Total                            10,586
          {Ending Gallons}                  5,451
          Qty Acct For (Ttl-End Gal)        5,135
          {GallonsSold}                     5,111
          Over Short (Qty Acct For-Gal Sold)  -24
 
200       previous({EndingGallons})         5,796	
          {receipts}                        5,200
          Total                            10,996
          {Ending Gallons}                  6,607
          Qty Acct For (Ttl-End Gal)        4,389
          {GallonsSold}                     4,360
          Over Short (Qty Acct For-Gal Sold)  -29

Currently using previous({EndingGallons}) does not give me the previous gallons for the particular store that I am needing, instead it is giving me the previous ending gallons for whatever store entered their data into the tables first.
The Beginning Gallons for Store 100 on 11/2 should be the 5,816 that is reflected above, however, in my report it is picking up Store 200’s 11/1 information of 5,795 and Store 200 on 11/2 is picking up 5,451 from store 100 11/2 information.

 

[RobbieB]

previous({EndingGallons}) would just give you the previous record for the EndingGallons field not the previous days ending gallons.
I think the best way for you to do this is to group by date then by location then right click on the location group and insert section below. In this section insert a sub report. when you create the sub report links you will want to join on location and date. Then go into the select criteria and where the date in the sub report equals the main report change it to {?StartDate} = {Date}-1 so the date of the sub repor is actually yesterday. (or day before the date in the main report).
In the main report in section 'b' of the location group create a text box and type 'Previous Ending Gallons' to the left of your sub report. Then put the rest of the fields in your main report as usual.