Dave's Blog

Custom expressions for background and font properties in a matrix

Jorg, a friend and colleague wrote an interesting blog about using the InScope() function in a matrix to take control on the behavior of your subtotals. I’d like to extend his example to illustrate how you can use this function to change the style of the (total)cells in your matrix.

As Jorg already explained you get the following default groups when you create a matrix in a SSRS 2005 report:
A row group named:                matrix1_RowGroup1
A column group named:          matrix1_ColumnGroup1

When you use the following expression for the Background color property of the data field in your matrix, you can make the Background color of the group total differ from the color of the data field(s):

=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"))

You can replace "In Cell", "In Subtotal of RowGroup1", "In Subtotal of ColumnGroup1" and/or "In
Subtotal of entire matrix" with any color you prefer. For example:

=IIF(InScope("matrix1_ColumnGroup1"),

IIF(InScope("matrix1_RowGroup1"),

                                "White",

                                "Blue"),

      IIF(InScope("matrix1_RowGroup1"),

                                "#ff8000",

                                "Red"))

This will give the following result:

If you'd like to change the font settings for the totals, replace the expression for the Color property to: