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
Jorg Klein's Blog 
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….
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike