Customizing ‘Subtotal’ expression by using Inscope function in Matrix Reports

In SSRS Matrix reports there is an inbuilt functionality to have the Subtotal of columns/row fields depending upon the scope of the group in which subtotal is defined. If it is defined in the scope of any row group it will sum up all the values across all the column groups but taking the specified row group as a whole; as defined by the group scope. Similarily if it is defined for any column group, it will sum up all the values acorss all the row groups but taking the column group as a whole; as defined by its scope.

Generally this Subtotal functionality is used to sum the values within a specified group/sub group. And thus by default for every Data field Sum function is there which will display sum of all the values for a specific row & column group. And by the effect of which, Subtotal will also display the sum of all these sum values.

But if we have requirement to display default sum values in the Data field but we want some other aggregations like AVG in place of Subtotal. There is no direct formula to be put in the Subtotal expressions as we don’t have any option to modify the subtoal value (though we can modify the lable for it).

Inscope function: SSRS provides a function ‘InScope()’ which returns boolean value as per the status of the field in the mentioned group scope. If it is in the scope (mentioned in the Inscope function), it will return 1 (true) else 0 (false).

We can make use of this function in the DataField’s expression box, to check whether it is in scope of a row group i.e. it is a Data value (Sum value) or it is not in the row group’s scope i.e. it is the value of Subtotal. And based on that we can use SUM or AVG functions to give the desired results.

=IIF(InScope(“matrix1_AccountNumber”), Sum(Fields!CustomerID.Value, “matrix1_AccountNumber”), AVG(Fields!CustomerID.Value, “matrix1_AccountNumber”))

: 11013 , 11014 so on showing sum of customerIDs for all the customers of type ‘I’, with account no# AW00011013, AW00011014 respectively and having TerritoryID = 1. But Average is showing the average value of all these sum values. Average here is subtotal functionality of the Matrix report.

In this same way, these expressions can be used to modify other properties like color.

Using Expression :

=IIF(InScope(“matrix1_AccountNumber”), Sum(Fields!CustomerID.Value, “matrix1_AccountNumber”), IIF(InScope(“matrix1_CustomerType”), AVG(Fields!CustomerID.Value, “matrix1_CustomerType”), AVG(Fields!CustomerID.Value, “matrix1_AccountNumber”)))