In Reporting Services, the syntax for changing the text color of a table cell, say to red if the value is negative, is to set the "Color" property to this expression:
=IIf(Me.Value < 0, "Red", "Black")
Another useful formatting tool is changing the background color of a cell depending on the cell's value, perhaps to achieve a "heatmap" effect. I find it's far better to do this using code than the macro-type language I used above.
Setting the background color in code can be achived by putting an expression like the following into the "BackgroundColor" property, replacing the section in square brackets with the same expression in the cell's "Value" property:
=Code.GetHeatmapColor([cell value or calculation], "White")
The second parameter is for a default color. You may or may not want to use it.
My sample code for "GetHeatmapColor" will be a simple SELECT CASE. First you need to define how the "heatmap" will work. For this example, "0" (or below) is the worst possible value and will appear dark red. "10" (or above) is the best possible value and will have no color at all.
Here's the code for "GetHeatmapColor", which accepts a double:
' Returns a background color from dark red to light pink (hey, that's what you get
' when you mix red and white) based on how far off target (in this case, 10) the
' passed value is.
Public Function GetHeatmapColor( _
ByVal dblActual As Double, _
ByVal strNeutralColor As String) As String
' measure how far the result is from the target (10), and grade the colors accordingly
' in blocks of 2 (you get the idea, anyway)
Select Case CInt(dblActual - 10)
Case < -10
' below zero (way off our target of 10)...return worst color
Return "#FF8282"
Case < -8
' off target - either 0 or 1
Return "#FF9191"
Case < -6
Return "#FFA0A0"
Case < -4
' Noticeably off target
Return "#FFAFAF"
Case < -2
Return "#FFBDBD"
Case < 0
' mildly off target - either 8 or 9
Return "#FFCACA"
Case Else
' on or above target (10) - return neutral color
Return strNeutralColor
End Select
End Function
And lastly here's a screenshot of a simple report that uses this exact code, the first column is ascending and the second is mixed up. Note how easy it is to pick the worst and best values in the second column because of the "heatmap" colors:
![]()
Click here if the picture does not display
I generated the colors by finding the darkest acceptable color (not quite red) that black text was still readable on top of, then creating a gradient using Paint.NET fading from this color to white, then finally using a "pixelate" effect in Paint.NET to remove the gradient smoothness and get definite colors. I'm sure you can think of better ways to do this!
I hope this code is able to help someone. As always, it worked on my machine; I make no guarantees for yours!
Tags: reporting services, heatmap, color, visualisation