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:

=Iif(InScope(“matrix1_ColumnGroup1”),

Iif(InScope(“matrix1_RowGroup1”),

                                “In Cell”,

                                “In Subtotal of RowGroup1”),

            Iif(InScope(“matrix1_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