Here’s the scenario. Your SQL Server Reporting Services (SSRS) report has a matrix, showing the sales of each salesperson per year. You put conditional formatting in place so that cells in the matrix that have sales over 1000000 are in a different colour – which is done by putting a formula in the property ‘color’, like =iif(me.value > 1000000, “Red”, “Black”)
But then you put subtotals into your matrix (right-clicking on the column/row field to get the option), and you find that all the subtotals are being coloured Red, because together, they add up to more than 1000000.
And this becomes a problem, because you can’t put different formatting on the subtotal as you have on the rest of the inner cells of the matrix. That is, without having a slightly trickier formula. The function you want is INSCOPE.
First look up the name of the group, by right-clicking on the field that contains the row and choosing “Edit Group”. That might tell us that the group is called ‘matrix1_SalesPersonID’, or something similar. You can rename it if you need to. (And yes, I know… in real life you’d be using the SalesPerson’s name – this is only an example)
Now, change your formula so that there’s a different criteria to set the formula if it’s within the group, rather than outside the group (as is the situation for the subtotals).
=iif(inscope(“matrix1_SalesPersonID”), iif(me.value > 1000000,”Red”,”Black”), iif(me.value > 50000000, “Red”, “Black”))
Now, within the matrix proper, the fields will be Red if they are over 1M, but in the subtotals at the bottom, they will be Red only if they’re over 50M.
To do the same for the columns, just look up the group name, and consider doing something like:
=iif(inscope(“matrix1_SalesPersonID”) and inscope(“matrix1_Year”), …
So now we have a matrix which has different conditional formatting for the subtotals, compared to the ordinary values within the matrix. You’ll notice in the image here that the subtotal of $32M isn’t red, even though it would’ve been without the InScope function.
This Post Has 29 Comments
Hi,
I found your post while trying to work out exactly how to do this… then a co-worker pointed out that in the designer if you right-click on the little green triangle in the subtotal label cell, then select properties the properties window is for the subtotal values cell!
Much handier than doing it in code, especially if you have a few different subtotal levels!
Yes, John – true.
Hi,
I found your post very interesting, especially that I’ve been banging my head over how to set the conditional formatting on a subtotal in a matrix report.
But even if I use InScope in my Color expression, I’m getting a compile error. I think it’s being always caused by my use of “Me.Value” in the expression in the Subtotal properties:
[rsCompilerErrorInExpression] The Color expression for the matrix ‘mtxReport’ contains an error: [BC30456] ‘Value’ is not a member of ‘ReportExprHostImpl.mtxReport_MatrixExprHost.MatrixDynamicGroup_matrix1_TaskCode_MatrixDynamicGroupExprHost.Subtotal_StyleExprHost
Thoughts?
This was a great example thank you.
I am still challenged with trying to find a way to display the differences or Variance (Var) between two rows of the same column in reporting Services.
I can code for it and put it in that way, but I was hoping that there was a way to calculate not a sub total but a variance between the values of the same column using SSRS.
Catherine,
Do you just want the Previous function?
http://msdn2.microsoft.com/en-us/library/aa255825.aspx
Rob
Hey Rob,
I’m following up on Catherine’s question – it is similar enough to what I want to do. The link to the var function you provided above states that the expression can’t be an aggregate, but the data region of a matrix has to contain an aggregate. Is the expression the matrix group, not the data level? Also the Previous function states (in msdn) that is doesn’t work in a Matrix 🙁
My specific requirement is to compare the values of columns in a matrix and format based on the proportion (actuals vs. budget). I am using a matrix because our data is Currency based and I would have to create new columns manually for new or removed currencies. Using a table is an option but not my preference.
Can you let me know if you know of any function that will allow me to reference the values in another column to get the proportion.
Thanks,
Alex
Alex,
Yeah – the Previous function doesn’t work in a Matrix. Depending the report, I’d be tempted to use a query which returns the data pivotted, and then use a table to display it. You say you’re dealing with currencies, which I’m hoping is relatively stable.
I think you’d be able to do this in SSRS 2008, using the Tablix control. I’d have to try it though.
Rob
does the condition color function works on the string? I got an error message “error bc30201” when it tried to display NY in Blue color.
Can anyone please help? thanks
How to collapse the data in the matrix based on the column value?
You can still do the standard visibility stuff, and use expressions in there too. If you’re worried, then get it working in a table and transfer the logic to the column.
how can we display 3, 4column names from the table in a report column vertically nd the corresponding values of that columns is just to the next of the report column
plz help me its urgent
I think I know what you mean… Try putting a Rectangle into your table cell, and then putting textboxes into the rectangle. You should be able to have a much better degree of flexibility this way.
Rob
what happens if I can’t base the expression (like percentages) on a value and I just want the background color gray for the enitre subtotal row?
Try the green corner bit, and then set it there. But otherwise, you can just use InScope like this:
=iif(inscope(“matrix1_SalesPersonID”), “White”, “Gray”)
I have created a matrix and have used the Add Row command to include additional aggregations within the matrix. Using the Add Row command created a Group called [Static Group] within the Rows groupings of the matrix. I am able to format the first row group within my matrix, since this is part of one of the named groups I created. However, the additional rows in my matrix are part of the [Static Group] and do not respond when I use the InScope function. Does anyone know how to format rows in a matrix which are part of a Static Group (or static row)? Thanks in advance.
Please ignore my previous comment about the Static Group. My Matrix has two named groups and I found that if I used the InScope function to look for items outside either named group, the formatting worked correctly.
Just an kind off topic question: Let’s say that I have a matrix whit 5 columns and I have one subtotal row, the thing is that I only want the totals of the numeric columns, not the string ones, is it possible to hide these string columns?
Then I would consider having a separate column, which could maybe be hidden… which tests to see if it’s numeric before displaying it. Then you can use that total instead of the original one.
Rob
Hello,
I have a subtotal row (automatically generated) in my matrix and i would like to color its maximum value in red.
I was wondering if the right way to do this was to use the Max() predefined function but unfortunately it didn’t work.
How can i do this ?
Thank you in advance for your help
Try putting a textbox outside the matrix, containing Max(ReportItems!SubTotal.Value) (or something similar).
Then you should be able to test to see if the value in the matrix is the same as the value in the textbox, and color it accordingly.
Hope this helps,
Rob
Hi Rob,
Thank you for your answer but unfortunately it didn’t work because :
1) I got an error (the textbox must be placed into the header or the footer of the report),
2) When I placed the textbox into the footer of the report, i got no answer (textbox with empty value).
I’m able to reach the subtotal row thanks to this expression (mentionned in the body of my matrix) :
=iif(InScope(“matrix1_Year”),
“Black”,
iif(InScope(“matrix1_Campaign_Status”),
“Black”,
iif(Me.Value = Max(Fields!Number_Opportunity.Value,”DataSource1″), “Red”, “Black”)
))
But the colored cell is wrong and I don’t know why.
Any help ?
Thanks in advance
Ok. Try using the Scope parameter of aggregate functions Sum and Max to get the max of the Sums per thing you’re subtotalling on. You can use it in a similar way to how the OVER clause works in T-SQL.
oh my god. like others i have been banging my head against my desk over this for a while… thanks for explaining it in a way that actually makes sense!
Two questions, why does the green corner right click trick work for some and not others?
Second, I was able to change the background colour, how would I change the format of the text in the subtotal to bold?
Marty – To change the format to bold, look for the FontWeight property.
Oh, and the green corner is useful for when you want to change properties of all the SubTotal boxes – but sometimes you want more control, and InScope can help with that.
Great! Thanks. InScope was just what I needed.
Thanks very much for this post!
very helpful to the work that I’m doing at the moment.
Cheers!
Thank you. This solved my problem