Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to sum on parent column but still display child columns Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 8:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 8:02 AM
Points: 1, Visits: 18
Hi
I am new to SSRS. I could generate simple reports.
But I am stuck generating the following report.
I have a DataSet which gets the following data.

OfficeId OfficeResources GroupId GroupDesc
10 1000 G1 Desc1
10 1000 G2 Desc2
20 2000 R1 RDesc1
20 2000 R2 RDesc2
20 2000 R3 RDesc3

I want to create a report to display the above result in the following format
OfficeId OfficeResources GroupId GroupDesc
10 1000 G1 Desc1
. . . . . . G2 Desc2
20 2000 R1 RDesc1
. . . . . . R2 RDesc2
. . . . . . R3 RDesc3
---------------------------------------------
OfficeCount = 2 TotalOfficeResources = 3000

But I am getting TotalResources = 8000

Please ignore the "." Substitute it for a space. I used "." here in the post to better format my report.
I am grouping on OfficeId and OfficeResources in SSRS and not in the TSQL.
I used CountDistinct(Field!OfficeId.Value) for Count and I am getting count = 2, which I want.
I used Sum(Field!OfficeResources.Value) for TotalResources, so am getting 8000. which is not what I want.
I want sum on distinct OfficeResources to be 3000.
How can I achieve this.
Any help is really appreciated.
Thank you
Guru.
Post #1548559
Posted Friday, March 7, 2014 12:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 13,275, Visits: 11,061
This MSDN thread offers some solutions:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3c2626d8-e835-4bba-b946-7d6796e9c617/sum-distinct?forum=sqlreportingservices




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1548597
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse