Multiple Summary Report

Discussion created by AjEGfmTech on Nov 18, 2012
Latest reply on Nov 20, 2012 by comment

This is probably pretty simple but I'm having difficulty wrapping my head around it.


I am looking to create a monthly report that shows the weekly sales and monthly total for each of our stores. The database is setup to collect the daily sales for each store.


I'm looking for something like below with each page of the report showing just one store's data, a page for each store.


February Report


SalesWeek Sales

5 2,536.12

6 2,125.42

7 1,956.95

8 2,478.36

9 2,913.54

Total 12,010.39


I can generate a report that shows the weekly sales for one or all of the stores. I can generate a report that shows the monthly sales for one or all of the stores. I just can't figure out how to show multiple weeks of sales and a monthly total for each store individually.


In searching the forum here it seems like I might need to look into using a crosstab/pivot report? However, it seems like that might be overkill for what seems like an array to me? I saw a thread that someone posted looking for something similar and the advice was to create two leading sub-summaries (one summarized by SalesWeek and one summarized by StoreID) and then create two trailing sub-summaries, again by SalesWeek and StoreID. Then sort the report by both SalesWeek and StoreID.


I wasn't sure about the details of how that would work. It seems like I would need two summary field for Sales, one when sorted by SalesWeek and one when sorted by StoreID with each field being placed in the appropriate sub-summary part (SalesWeek with SalesWeek and StoreID with StoreID). Then, in order to get the report I'm looking for, I need to have the StoreID sub-summary (leading) before the SalesWeek sub-summary (leading). However, I'm not sure what the trailing sub-summaries will be doing?


Any help, clarification or direction would be appreciated. Thanks.