SSRS – Why Do We Need InScope Function in Matrix Subtotals

Here is a pretty good article about advanced matrix reporting technique. I’ll show you an example where I need to manipulate the Subtotals in a matrix to get a subtotal that is not the default sum of all the data in the group. (I need to check out if this is still true in SSRS 2008.)

By default, the subtotal in the matrix is the sum of all  numbers in the defined group.  But in many cases, we need to have custom aggregates on a matrix report. Examples are, you need average instead of sum, you need growth percentage instead of sum. In this example, I need to sum only the numbers in bold, and ignore the numbers that are not in bold.

matrix1_RowGroup2: this is the lowest level grouping Dates on the column.

matrix1_ColumnGroup2: this is the lowest level grouping on the row.

To get a custom aggregates, enter this in the Expression editor of the measure cell. (To be continued…)

=iif(inscope("matrix1_ColumnGroup2") and inscope("matrix1_RowGroup2"), IIF(SUM(Fields!ACCT_COUNTER.Value) = 0, "", SUM(Fields!ACCT_COUNTER.Value)), IIF(SUM(Fields!ACCT_COUNTER_SUBTOTAL.Value) = 0, "", SUM(Fields!ACCT_COUNTER_SUBTOTAL.Value)))