How to create rollup for reporting?

0

Hey, I have two tables... ones give me a whole bunch of financial data, at the transaction level (ie: dept/account/$ amount/invoice #/etc...) and another table that is an account tree (the fields are ACCT / PARENT / LEVEL).

For reporting purposes, people don't want to see the financials at the account level, they'd rather see it at a rolled up level (ie: Materials & Supplies, or Human Resources).

My initial thought was to create 6 additional fields in my details table (I have 6 levels, ie: Level1, Level2...) and then pull in the parent for each account, linking it all the way to the top of the tree... I think that should work, but it seems rather inefficient.

@TelepathicSheep2 Since it is for the reporting purpose and I have seen your frequent use Reporting Services for reporting, you can handle it pretty easily in the Reporting Services.

TSQL part

Get SUM of all DEBITS+CREDITS with grouping on the account from the Transaction table. Make a RIGHT JOIN with all the Accounts you have in Accounts Table. You should pick all the related fields like ACCT, PARENT, LEVEL etc. Also I guess you would handle the Opening Balance thing.

SSRS part

Add Group on Acct.

Group Properties -> Advanced -> Recursive Parent = [Parent]

Add an aggregated column using SUM with Recursive option i.e. something like Sum(Fields!Amount.Value,"WHATEVERTHEGROUPNAMEIS",Recursive)

This is what I remember we did sometime ago. But our requirement was to have a toggled display. Meaning if someone wants to drill down to see the breakdown, then it would be possible and it added great flexibility to the report. So I would recommend you looking into this cool feature.

Now in your case, if you want to show only Top level accounts, then you just need to suppress the fields against all the lower levels.

Row Visibility -> Show or hide based on expression -> =Fields!LEVEL.Value <> 1

Thank you very much for your answer Usman! This has helped me a lot! I will look into the toggle, because that would be a big hit, and I'll also need to look into how to sort, (ie: if there is any way to have level 3 of the hierarchy sorted in a specific way)... but I'm impressed, this functionality made this ridiculously easy. I thought I was going to be in for a painful SQL adventure. Thanks for your help, you saved me a lot of time!