Running total based on column values

[satech]

I have a table that looks like this.

I group the records by category and a few formula fields (calculated based on menu name).

I need to generate a report like this

Group - A (e.g.: gls red)

Subgroup of Group A (e.g.: 6 oz)

menu_name, storeId1_qty, storeId2_qty, storeId3_qty, price, ... (other formula fields)

How to generate the sum of the same menu_id for each store_id?

Thanks!

[JWiseman · Accepted solution]

Hi satech, the answer will depend on several things:

• what you want the report to look like
• what objects you're using to build the report
• if you're connecting to a view or stored proc already or if you can write a Command object or SQL Expression in your report
• if you want a running total (i.e. show the incrementation through the data) or a subtotal...for the suggestions below I am assuming you want a subtotal showing one value for each Store...let me know if that's not what you want

If you want you can create a separate Cross-tab (or Chart) in your report based on the Store ID which is quick and easy but this depends on how you want your info to display.

If you need to show something like your screenshot but have the subtotal for each Store in a new column then your options could include:

1. If you're using DB tables in your report you could do a self join  in the report to bring back the subtotal for the Stores
2. If you're using DB tables in your report you could also write a SQL Expression that does the subtotal based on a grouping on the Store ID...note that this doesn't work for all DBs
3. If you like to write your own SQL you can base your report on a Command Object and (i) write a sub-query in there or (ii) write the self join syntax there or (iii) use a CTE
4. If you can't do any of the above you can create a Report Header subreport that is grouped on the Store ID and add those values into an array, pass them to the main report, and parse them out based on the ID...this is probably the hardest / most annoying method but works if the above 3 aren't possible.

[satech · Accepted solution]

Thanks! I decided to use SQL to group, and use SUM to get quanitiy for store_id