How to sum on parent column but still display child columns

• Guru1

  SSC Rookie

  Points: 29

  More actions

  March 6, 2014 at 8:11 pm

  #290564

  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.

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  March 7, 2014 at 12:50 am

  #1694990

  This MSDN thread offers some solutions:

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

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP