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

How to format the subtotals of a Reporting Services matrix differently, using InScope - Rob Farley: "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.