Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Reporting Service Issue
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

34 Posts

Posted - 07/13/2012 :  11:06:23  Show Profile  Reply with Quote
I've 4 levels of data
LevelType Dr Cr
Level 1
Level 2
Level 3
Level 4A 5 10
Level 4B 25 10
Level 4C 15 0
Level 4D 0 5

Level 11
Level 22
Level 33
Level 44A 10 15
Level 44B 5 30

I've created a drill down report in SSRS and I want to show the difference of Dr & Cr values
in either Dr or Cr column depending on which has the greater value

so in the above example for Level 4A, the value shown will be 0 in Dr and 5 in Cr since 10 > 5 and 10 -5 = 0
for level B value of Dr will be 15 and Cr = 0
and so on and so forth

I've done all this in SSRS using the IIF condition to find out which is greater and then getting the value as shown below

Each level total depends on the sum of records below it. At each level the same logic as mentioned above is applied. i.e. the difference between Dr and Cr should be displayed in the column with the greater value

LevelType Dr Cr
Level 1 20 0
Level 2 20 0
Level 3 20 0
Level 4A 0 5
Level 4B 15 0
Level 4C 15 0
Level 4D 0 5

Level 11 0 30
Level 22 0 30
Level 33 0 30
Level 44A 0 5
Level 44B 0 25

So far everything is working fine.
My only hiccup now is finding the grand total which should be total os
Level 1 + Level 11

for grandtotal the Dr should be 20 + 0 = 20 and
for Cr should be 0 + 30 = 30

How do I total the group level 1 to arrive at the grand total

ANy help will be appreciated.

Thanks Sridhar

Starting Member

34 Posts

Posted - 07/16/2012 :  01:32:23  Show Profile  Reply with Quote
I've got the solution. There is a workaround to this by writing a piece of code

Thanks again
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000