SSRS make aggregation on both row and column scope

Question

I have a dataset like this:

RowGrp1 RowGrp2    Col     Value
---------------------------------------
1       1          A       1
1       2          B       2
2       1          A       3
2       2          B       4

When designing SSRS matrix, I've set RowGrp1 and RowGrp2 into row groups and Col into column group. In the data zone I write my expression to calculate SUM(value) within row group 1, as =Sum(Fields!Value.value, "RowGrp1").

I found my column group was not effective when applying this calculation. The result of my matrix is as below:

RowGrps  A     B    
-----------------
1  1     3     3
   2     3     3
2  1     7     7
   2     7     7

How can I make my value still grouped by column group while I have to use scope in my expression (for unmentioned purposed here)?

Alan Schofield Alan Schofield · Accepted Answer · 2019-06-26T12:07:38

Remove the scope from the expression, just use

=Sum(Fields!Value.value)

By setting the scope you are telling SSRS to sum at that specific scope. By omitting the scope you are telling SSRS to sum at the scope of the 'cell'.

SSRS make aggregation on both row and column scope

1 Answers