June 10, 2010 at 3:27 pm
The data is number intensive and has 3 levels of drill down.
1. Customer name
2. Contract
3. Invoices
The report does some aging calculations across the row and sums one column up at each level of the report.
I calculate the subtotals and grand total in the SQL it becomes a column in the sql for the data set. The report is run and exported as drill down (not expanded). The subtotals and grand total are duplicated for every record in the dataset (but this is hidden).
Consequently, when the users run and export to Excel, everything is fine except if they want to remove and some rows and recalculate totals. Because of possible duplication of the subtotals--if a contract has several invoices in the date range--any recalculation of totals doesn't work. I tried Excel's =SUBTOTAL(9,G2..G99) but it added the amounts in the non-expanded rows. Any thoughts or assistance would be greatly appreciated.
June 14, 2010 at 10:14 am
Just to check, if I have mis-understood let me know but your report does work as intended and the sub-totals and totals are correct even when exported into excel. However users want to be able to delete some rows/levels and then re-calculate the subtotals and totals... Have I understood this right...
If so then I would recommend that instead of the work being done in excel where users may make errors in the calculations I would apply parameters so that users can select what they want to see in what ever levels etc. This way the calculations would stay the same with little or no fuss from users as they interact with the report to display only what they want...
June 14, 2010 at 11:44 am
That's sort of where we're heading. Its a little more complicated since I posted, but we're getting there. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply