SSRS Sum visible parent row group

Question

Hi I have two row groups. and I set the second group to hide unless you click the plus sign of the parent group.

Here is my raw dataset

PROJK   ID1523  EMPLOYEE
100     3       JOE001
100     3       KAM002
100     3       MAT102
201     5       JOE001
201     5       MAT122
203     1       KIM222

and here is my SSRS matrix. PROJK is the parent group. and notice ID1523 is not summarized like sum(fields!ID1523.Value)

because I don't use SUM(fields!ID1523.Value) in the ID1523 column, essentially when SSRS RUN the report looks like this in IE

PROJK   ID1523
+100     3
+201     5
+203     1

even when the rows are expanded ID1523 does not repeat the value in the child group because of the way I set it up on the matrix.

How can I Sum ID1523 ? I expect the sum would be 3 + 5 + 1 = 9

Right now if I add total for PROJK Group it will give me 3+3+3+5+5+1 = 20

I am hoping I can solve this via SQL so the raw dataset will look like this

PROJK   ID1523  EMPLOYEE
100     3       JOE001
100     0       KAM002
100     0       MAT102
201     5       JOE001
201     0       MAT122
203     1       KIM222

Raw data set is a result from two table. with a simple left join on PROJK Table1 looks like this

  PROJK   EMPLOYEE
    100     JOE001
    100     KAM002
    100     MAT102
    201     JOE001
    201     MAT122
    203     KIM222

and table2 looks like this

PROJK   ID1523  
100     3       
201     5       
203     1

Check a similar post stackoverflow.com/questions/44461128/… — niktrs
Thanks. that answer it. I didn't think of partition and row number. — BobNoobGuy

BobNoobGuy BobNoobGuy · Accepted Answer · 2017-07-26T14:42:20

I am using Partition and Row_number to determine each group in sql

then join only to the first row_number of "table 1"

SSRS Sum visible parent row group

1 Answers