Need technique to calculate percent change from other grouped row

[gagnonconsulting]

I have built a report that shows 2 rows of sales data from each of a bunch of store locations. My table has the date, the location and the sales_amount. I grouped my query on locations. The resulting rows include a date and the sales_amount, and I have been able to get the sales_amount for a requested date (through a parameter query) and the sales_amount from the same week in the previous year, and this is shown correctly for each location. But I now need to be able to show the $ change in sales_amount and the percent change in sales_amount for each location. This means that I somehow need to access data from (both of) the grouped rows and calculate and display the results. How do I access data from multiple rows? Do I have to create the report manually using VBA, and if so, any examples out there?

Thanks in advance

 

[apr pillai]

I don't know this will help you, if it does then try it on the Report itself. Both rows on the Report have different locations, right? Create two Text Boxes on the Footer of the Report. In the first Text Box write the Formula like:
=Sum(iif([location1]="Ohio",[Sales_Amount],0)).

Second Text Box

=Sum(iif([location2]="New Jersy",[Sales_Amount],0))

Give both Text Boxes Unique Names and use a third Text Box for percentage caculation. The drawback here is using the Constant Value for comparing Location Names.

If both rows can be created in two separate Queries and for the purpose of Report Source, combine them in a Union Query. In that case both source queries will remain separate entity and you can use dlookup() function directly to bring in the values for comparison in place of the Constant Values. I know this is not an efficient way to do, but I can't think of anything else now.