x

How to create rollup for reporting?

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
more ▼

asked Jul 16, 2012 at 02:27 PM in Default

TelepathicSheep2 gravatar image

TelepathicSheep2
140 14 15 17

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@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
more ▼

answered Jul 17, 2012 at 11:26 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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!
Jul 17, 2012 at 02:16 PM TelepathicSheep2
@TelepathicSheep2 Glad to know that it helped. I guess you underestimated the power of SSRS ;)
Jul 17, 2012 at 04:06 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x14

asked: Jul 16, 2012 at 02:27 PM

Seen: 1448 times

Last Updated: Jul 17, 2012 at 04:06 PM