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.
Any comments are appreciated. Thanks
asked Jul 16, 2012 at 02:27 PM in Default
@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.
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.
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.
answered Jul 17, 2012 at 11:26 AM