SSRS – Custom expressions for subtotals in a matrix

Posted by

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

5 comments

  1. I suspect I have to use the inscope function to get this working correctly in my matrix report
    =IIf(Sum(Fields!ACP_hrsPosted.Value)<First(Fields!SOW.Value, "DataSet1") , "Red","Black")
    here is the xml….

    Like

  2. Hi,
    I am new to SSRS matrix report. I am currently facing the following problems:
    . I have no column grouping
    . I have two rows grouping Matrix1_level1 and Matrix1_level2
    I have the following expression inside a cell in a matrix data region.
    =iif(inscope(“Matrix1_level2″),
    “Within Group2 Scope”,
    iif(inscope(“Matrix1_level1″),
    “Within Group1 Scope”,
    iif(inscope(“matrix1″),
    “Within Matrix Scope”,
    nothing)))
    But this is not working. The result I get is this.
    On both group1’s line and group2’s line as “Within Group1 Scope”.
    and subtotal
    “Within Matrix Scope” message is showing up ok.

    Like

  3. Hi Sandy,
    A good way to “debug” the InScope function is to change the background color of your cells instead of showing the text.
    Use the full script to change the background color of each part of your matrix with another color. This way it’s easy to see what’s happening!
    Good luck and if you face any other problems, let me know!
    -Jorg

    Like

  4. Hi,
    I am wondering if you can help with my forecast report.
    My Source is like this:
    Quote_Number     AAA-001           AAA-002
    Customer         ABC Ltd           XYZ Co.
    Item1_Price      2500.00           1200.00
    Item2_Price      1200.00           1400.00
    Order_Date       18/11/2011        15/12/2011        
    The expected Invoice Date of Item1_Price is 30 days after the Order Date.
    The expected Invoice Date of Item2_Price is 15 days after the Order Date.
    We want to report it like this:
                           Nov-2011         Dec-2011        Jan-2012
    Quote   Customer     Order  Invoice  Order  Invoice   Order  Invoice
    AAA-001 ABC Ltd        37       12      0       25      0         0  
    AAA-002 XYZ Co.         0        0     26       14      0        12
    Total                  37       12     26       37      0        12
    Note: Display is in thousands.
    I know how the orders will work in a matrix.  But the Invoice column is tricky.
    Any suggestion on how to do it?
    Thank you very much.
    Kay

    Like

  5. I was able to get the data area, the column and row groups to behave but could not get the total in the bottom right to work out.
    =IIf(InScope(“Division”),
    IIf(InScope(“AcctMonthHrs”),Sum(Fields!LastYearBillableDollars.Value), First(Fields!LastYearBillableDollars.Value)),
    IIf(InScope(“AcctMonthHrs”),Sum(Fields!LastYearBillableDollars.Value), First(Fields!LastYearBillableDollars.Value))
    )
    This gets me the first record in the divisions
    =IIf(InScope(“Division”),
    IIf(InScope(“AcctMonthHrs”),Sum(Fields!LastYearBillableDollars.Value), First(Fields!LastYearBillableDollars.Value)),
    IIf(InScope(“AcctMonthHrs”),Sum(Fields!LastYearBillableDollars.Value), Sum(Fields!LastYearBillableDollars.Value))
    )
    This got me the sum of the acctMonthHrs
    I don’t get it. How come changing a single field changes the whether it looks at the columns or the rows. I’m very confused.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s