SSRS – Custom expressions for subtotals in a matrix

If you want custom expressions for your subtotals in a matrix, for example to calculate an average instead of the default sum, you need to use the InScope() and Iif() functions in your data field…

When you create a matrix with SSRS you get the following default groups:
A row group named:               matrix1_RowGroup1
A column group named:          matrix1_ColumnGroup1

With the normal functionalities you can’t change much on the behavior of your subtotals in your matrix. When you create a subtotal it calculates a subtotal and that’s about it 😉

If you use the following expression in the data field of your matrix you can take full control on the behavior of all your subtotals:



                                “In Cell”,

                                “In Subtotal of RowGroup1”),


                                “In Subtotal of ColumnGroup1”,

                                “In Subtotal of entire matrix”))


Replace “In Cell”, “In Subtotal of RowGroup1”, “In Subtotal of ColumnGroup1” and/or “In Subtotal of entire matrix” with the expressions or fields that you want.

For example, if you want to calculate an average:

Replace “In Cell” with Sum(Fields!Amount.Value)

Replace “In Subtotal of RowGroup1” with Avg(Fields!Amount.Value)

More information about the InScope() function on MSDN