The InScope() Function and the key to formatting subtotals in SSRS Matrix Objects
What it does
The InScope(<named-scope>) function checks to see if the current item is in the specified scope.
Example – So for the simple matrix report in Fig 1.:
- The Green cells fall out-of-scope of the ColumnGroup, and In-Scope for the RowGroup
- The Grey cells fall out-of-scope of the RowGroup, and In-Scope for the ColumnGroup
- The Light blue cells fall In-Scope of both the ColumnGroup and the RowGroup.
- The Dark blue cell falls out-of-scope of both the ColumnGroup and the RowGroup.
What this means
By incorporating an If statement into the party we can decipher which cells should get particular formatting. For example:
Fig 1. A Matrix Object
Application
Now let’s apply the above theory. The object is to format the background colour of the cells as shown above:
- Select the data cell.
- Navigate to the background colour property
- Select Expression from the drop down list.
- Apply the expression:
=IIf(InScope(“ColumnGroup”),
IIf(InScope(“RowGroup”),”LightBlue”, ”Grey”),
IIf(InScope(“RowGroup”),”DarkGreen”, “DarkBlue”)
)
- Run the report. You should get a colour scheme similar to Fig1.
Now this is just a simple example. If you have multiple column and row groups you will have to expand the logic to cater for what is In Scope and what is Out of Scope. The If statements will begin to get a bit messy, but if you use an indented format to initially get the formula out and then reduce it, it becomes easier.
You can apply this logic to format any property of the cells. You can also use the logic to change the data shown in the cells. If, for example, you wanted to show an average in the subtotal column instead of a data value, then you would substitute the colour above for the appropriate data expression
=IIf(InScope(“ColumnGroup”),
IIf(InScope(“RowGroup”),Fields!Total.Value, SUM(Fields!Total.Value)),
IIf(InScope(“RowGroup”),SUM(Fields!Total.Value)/Count(Fields!Month.value), Sum(Fields!Total.Value)/Count(Fields!Month.Value)
)
There is another way to format the colour (or other property) of the Subtotal columns/rows. Using this method will apply the same formatting to all cells in the subtotal column/row, including the Grand Total. So, if you want a simple way to blanket format the subtotal of a column or row:
- Select the Subtotal column/row cell.
- Click the little green arrow that appears in the top right corner of the cell (watch out, it’s a tricky little bugger)
- Now amend the property that you wish to update.
- Done.
Have you figured out how to use this when you have subtotals with multiple row groups? It looks to me like the the subtotal cells for the “inner” row group is always out of scope. I couldn’t find any combination that seemed to identify those cells correctly.
Hi Tom,
I know that there is a trick to getting this right for a matrix that has multiple Row Groups but only one Column group. If you have the same number of Row/Column groups then it’s just a case of nesting/subnesting correctly (and yes, this can be a royal pain).
Why don’t you send me an example of what you’re trying to do and I’ll see if I can help out. –> luke at hayler.com
-Luke
I too am trying to use InScope so as to make sub-totals always be “Bold” while non-sub-totals are “Normal”. I have multiple row groups and one column group.
If I do InScope(“matrixq_GroupA”) where I have GroupA>GroupB>GroupC will I get a True when the report item is in GroupB or GroupC?
I seem to be getting a lot more “True” answers than expected. How do I distinquish between the row sub-totals and the detail lines?
Using Reporting Services 2005.
Hi George,
If your groups (A, B, C) correspond to (for example) Year>Month>Day, then yes, InScope(YearGroup) means that MonthGroup & DayGroup will also evaluate to true. Why? Because the YearGroup ‘contains’ the Month/Day groups. Make sense?
To distinguish between detail and subtotals (in this instance) you will need to do the following:
Using the Year>Month>Day example set:
=IIF(InScope(YearGrp),
IIF(InScope(MonthGrp),
IIF(InScope(DayGrp), DetailData,
MonthSubTotal),
YearSubTotal),
GrandTotal)
In plain english:
If the cell falls within the YearGroup bounds, then:
if the cell falls within the MonthGroup bounds, then:
if the cell falls within the DayGroup bounds, then its a detail cell, Else:
it falls outside of the DayGroup but within the MonthGroup, so its a Month Subtotal, Else:
it falls outside of the MonthGroup but within the YearGroup, so it’s a Year Subtotal, Else:
it falls outside of the year group, therefore it must be a Grand Total (All Years).
Whew!
If you Start from the very outer group and work your way in to the Detail groups then you can (fairly) easily determine the Grand Total & Subtotals – because they fall Out-of-Scope of the group in question.
So, to answer your question. Yes you will get a ‘True’ for Group B/C (for your current query). To fix that you need to specify, for each of Group B & C, the ‘False’ criteria (i.e. what should be shown if not within the bounds of the group in question).
The example I have given above should help you out in distinguishing between the detail, subtotal & grand total lines.
If you have more questions, please let me know. This is a hot topic!
Is this for SSRS 2008? I don’t even see an ‘If’ command in 2005, only an IIF (immediate if).
In any case, I’ve been trying to set the background color for the subtotal/total column at the end (and only the one cell that has the total, not the whole column), but am not finding it easy to determine if its ‘in scope.’ Is there a trick to this? I’ve tried seeing of “Total” or “Subtotal” is in scope, but it never seems to work…
Hi Mike,
You’re quite right about the ‘IF’ & ‘IIF’ (this is written for 2005). I wrote this article without the benefit or having SSRS open at the time, so this error crept in.
I’ll update it shortly.I’ve updated the post to correct the error.With regard to your query; if you are trying to set the colour for the [grand] total, remember this. The Grand Total cell is ALWAYS Out-of-Scope of both Row Groups & Column Groups. So, it should be the last ‘false’ part of your IIF Statement. See Fig 1’s nested if statement (Grand Total).
If you are still having trouble post your expression here and I’ll take a look.
-Luke
Thanks — part of my problem, was that I was trying to do the expression on the Total/Subtotal columns/rows, instead of the ‘data cell’ (as you wrote in this earlier). All of it just seems counter-intuitive to me.
Funny thing; it seems Microsoft doesn’t know, or can’t decide, how to spell ‘Gray’ or ‘Grey’. Depending on the row or column, it’ll be spelled differently (LightGrey/LightGray, etc.) I decided to go with ‘Gainsboro’ to avoid the compilation warnings…
Glad you got it sorted.
Interesting pick up on the inconsistency of the ‘grey/gray’ spelling. I had a look and the difference in spelling is only in the ‘LightGrey’ colour, all others are ‘XXXGray’. Did make me laugh though.
Are you going to SQLBits?
Hi Luke — kind of got it sorted, but after it was all good in my VS2005, I uploaded the new/never-been-uploaded RDL file to the SSRS server, and the conditional formatting now has its own mind. It ‘grays’ the wrong cells, and leaves the rest white/transparent.
I think I’ll just go in the bathroom and weep for a while…
Hi Mike,
If you are still having issues, send me the expression. I’ll take a look and see if I can help.
-Luke
Hi Luke,
I am having a matrix which needs to be linked to a detailed report. The matrix contains a single rowgroup and couple of column groups. Problem is when I click on the Subtotals the parameter passed are the first values for the row and column.
Could you please let me know how can I use the Inscope function to pass the paramters for Subtotals.
Hi Sandeep,
I’ll need a little more information from you before I can give you an answer. If you email me the details of what you are trying to accomplish I will take a look and see if I can help.
This is probably a dumb question, but I’m fairly new to reporting services, so I’ll ask anyway. In your example in fig 1, I don’t understand the “RowGroup2″ reference. In your diagram, it looks like there is only one row group, so what are your referencing with InScope(“RowGrop2″)?
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.
Hi Sandy,
As I have just mentioned to George (previous comments), you need to think of your groups as containers. The outer group ‘containing’ each of the inner groups. Thus, if a cell is within an inner group’s scope, it is by default within the outer group’s scope too. However, and here’s the trick, if the cell is NOT within the inner group’s scope BUT IT IS within the Outer Group’s scope, that’s where you get a subtotal.
So to amend your expression:
Remember: =IIF(InScope(), ‘true part’, ‘false part’)
=IIF(InScope(“Matrix1_level2″), IIF(InScope(“Matrix1_level1″), “Within Group 1 & 2 Scope”, “Within Group 2 Scope Only (Subtotal)”), “Matrix Scope (or Grand Total)”)
If you are still not clear then perhaps I need to do my first screen cast…