SSRS Heat Map in a Table or Matrix

Today I wanted to create a Heat Map (not the geographical one, but in a matrix) and stumbled upon this blog post:
http://www.arrangeactassert.com/how-to-create-heat-maps-in-sql-server-reporting-services/

It uses the colors from green to red, but I’m more of a subtle guy who wants to use softer tones for that. So I picked my own set of pre-defined colors for the VB code to use and here is it:

ssrs-heat-map-matrix-table-code-example

Code:

Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
Dim colours As String() = New String() {"White", "WhiteSmoke", "Gainsboro", "Silver", "DarkGray"}
If textBoxValue = 0 Then
Return colours(0)
End If
If minDataSetValue = maxDataSetValue Then
Return colours(0)
End If
If textBoxValue > maxDataSetValue Then
Return colours(colours.Length - 1)
End If
Dim divider As Decimal = (maxDataSetValue - minDataSetValue + 1) / 4
Dim index As Decimal = (textBoxValue - minDataSetValue) / divider
GetHeatmapColor = colours(index)
End Function

Simply copy/paste this code into Report Properties -> Code and use this expression for your BackgroundColor of the cell in your matrix or table:


=Code.GetHeatmapColor(Sum(Fields!NumberOfUsers.Value), Min(Fields!NumberOfUsers.Value, "DataSet1"), Max(Fields!NumberOfUsers.Value, "DataSet1"))

Note: In my case I set the minimum to 8.0 to ignore very low values which will mess up the color scheme.








Advertisements

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