Reporting Service problem - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Reporting Service problem

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2012-07-13 : 09:41:14

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

sridhar3004
Starting Member

34 Posts

Posted - 2012-07-16 : 01:30:12

I've got the solution. There is a workaround to this by writing a piece of code

Thanks again
Sridhar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources