SQL Server Reporting Services - Custom subtotals in a matrix

Last day I had faced a strange issue related to the subtotal of matrix as following:




By the way, I am using the subtotal of the column group and I am using a static row for calculating the male and the female for each row group, the weird thing that the subtotal did not pay attention to the static row. As you know, you cannot 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....!!!!!!

So the only way to force the subtotal column to calculate the value of our static total is using the custom expression.

so in the data field do the following : in our example :

1) Right click the static total cell
2) Click the expression
3) Put the following expression:

=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.or let it as it and see the result and then you can decide what you can do ..!!!!!

in our example :

=Iif(InScope("matrix1_ColumnGroup1"),
Iif(InScope("matrix1_RowGroup1"),
Fields!male.Value + Fields!Female.Value, sum(Fields!male.Value)+sum(Fields!female.Value)),
Iif(InScope("matrix1_RowGroup1"),
sum(Fields!male.Value)+sum(Fields!female.Value),
"In Subtotal of entire matrix"))


finally Thank you Jorg Klein .who gives me the solution of Custom expressions for subtotals in a matrix

3 Response to "SQL Server Reporting Services - Custom subtotals in a matrix"

  1. Anonymous Says:
    April 15, 2008 at 3:33 AM

    I was hit my head searching how to do this ..thankssssssssssss

  2. Anonymous Says:
    September 16, 2008 at 8:34 AM

    Nice. Thanks so much!

  3. U3 says:
    May 29, 2009 at 2:48 PM

    you welcome