How to format the subtotals of a Reporting Services matrix differently, using InScope

Here's the scenario. Your SQL Server Reporting Services (SSRS) report has a matrix, showing the sales of each salesperson per year. You put conditional formatting in place so that cells in the matrix that have sales over 1000000 are in a different colour - which is done by putting a formula in the property 'color', like =iif(me.value > 1000000, "Red", "Black")

But then you put subtotals into your matrix (right-clicking on the column/row field to get the option), and you find that all the subtotals are being coloured Red, because together, they add up to more than 1000000.

And this becomes a problem, because you can't put different formatting on the subtotal as you have on the rest of the inner cells of the matrix. That is, without having a slightly trickier formula. The function you want is INSCOPE.

First look up the name of the group, by right-clicking on the field that contains the row and choosing "Edit Group". That might tell us that the group is called 'matrix1_SalesPersonID', or something similar. You can rename it if you need to. (And yes, I know... in real life you'd be using the SalesPerson's name - this is only an example)

Now, change your formula so that there's a different criteria to set the formula if it's within the group, rather than outside the group (as is the situation for the subtotals).

=iif(inscope("matrix1_SalesPersonID"), iif(me.value > 1000000,"Red","Black"), iif(me.value > 50000000, "Red", "Black"))

Now, within the matrix proper, the fields will be Red if they are over 1M, but in the subtotals at the bottom, they will be Red only if they're over 50M.

To do the same for the columns, just look up the group name, and consider doing something like:

=iif(inscope("matrix1_SalesPersonID") and inscope("matrix1_Year"), ...

So now we have a matrix which has different conditional formatting for the subtotals, compared to the ordinary values within the matrix. You'll notice in the image here that the subtotal of $32M isn't red, even though it would've been without the InScope function.

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”)))