How To Create Heat Maps In Sql Server Reporting Services

Recently I’ve been doing some work with Microsoft Sql Server Reporting Services. One of the tasks I was given was to create a report with a heat map, which means you transform a report that looks like this

Plain Report

to something that looks like this

Bobs Heat Map Report

As you can see the a heat map is excellent way to visualize data.

This was done without the use of any plugins. I implemented the heat map in Reporting Services by following Bob Meyers excellent solution in his ‘Add Excel-like “color scale” conditional formatting to your reports‘ blog post.

Exporting to Excel

One of the requirements for my work was for users to be able to export the report into Microsoft Excel. Unfortunately it was at this point Bob’s solution didn’t work out for me. The problem was I was getting cells with a black background color as shown below.

Errors Exporting To Excel

So I came up with an alternative solution.

An Alternative Sql Server Reporting Services Heat Map Generator

So instead of using hex codes for the color like Bob does, I wanted to play it safe and use Microsoft colors i.e. those in the System.Drawing library.

The code below shows the GetHeatmapColor function which takes in three arguments and returns a string for the color

Public Class SqlReportingServicesHeatMapGenerator
    Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
        Dim colours As String() = New String() {"Green", "LimeGreen", "Yellow", "Orange", "OrangeRed", "Red"}

        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 Integer = (maxDataSetValue - minDataSetValue + 1) / 5
        Dim index As Integer = textBoxValue / divider
        GetHeatmapColor = colours(index)

    End Function
End Class

This function can be unit tested like this. Yes I know this falls a long way from 100% test coverage, but this is just an example ;)

Imports NUnit.Framework

<TestFixture()> _
Public Class When_Getting_The_Fill_Color
    Private _heatMapGenerator As New SqlReportingServicesHeatMapGenerator()       
    <Test()> _
    Public Sub Should_Return_Green_For_Zero()
        Dim result = _heatMapGenerator.GetHeatmapColor(0, 1, 100)
        Assert.AreEqual("Green", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Green_For_Lowest_Value()
        Dim result = _heatMapGenerator.GetHeatmapColor(1, 1, 100)
        Assert.AreEqual("Green", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Red_For_Highest_Value()
        Dim result = _heatMapGenerator.GetHeatmapColor(100, 1, 100)
        Assert.AreEqual("Red", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Green_If_All_Values_Are_The_Same()
        Dim result = _heatMapGenerator.GetHeatmapColor(1, 1, 1)
        Assert.AreEqual("Green", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Red_Even_If_Cell_Value_Is_Greater_Than_The_Maximum()
        Dim result = _heatMapGenerator.GetHeatmapColor(1164, 1, 896)
        Assert.AreEqual("Red", result)
    End Sub
End Class

Implementing The Solution

The colors on the report are shown using the fill property of a text box and an expression to determine what the fill color should be. Follow the steps below to create a heat map in Reporting Services using either Report Builder or Visual Studio.

First create a data source and dataset (we will not be connecting to any databases). Right click on the dataset and select query

Right click on the dataset and select query

In the query for the dataset paste in the sql below

DECLARE @LogTable TABLE (WebServer VARCHAR(10),[Hour] TINYINT, NumberOfUsers INT)
INSERT INTO @LogTable VALUES ('WebServer1',0,0) 
INSERT INTO @LogTable VALUES ('WebServer1',1,2) 
INSERT INTO @LogTable VALUES ('WebServer1',2,4) 
INSERT INTO @LogTable VALUES ('WebServer1',3,6) 
INSERT INTO @LogTable VALUES ('WebServer1',4,8) 
INSERT INTO @LogTable VALUES ('WebServer1',5,10) 
INSERT INTO @LogTable VALUES ('WebServer1',6,12) 
INSERT INTO @LogTable VALUES ('WebServer1',7,14) 
INSERT INTO @LogTable VALUES ('WebServer1',8,16) 
INSERT INTO @LogTable VALUES ('WebServer1',9,18) 
INSERT INTO @LogTable VALUES ('WebServer1',10,20) 
INSERT INTO @LogTable VALUES ('WebServer1',11,22) 
INSERT INTO @LogTable VALUES ('WebServer1',12,24) 
INSERT INTO @LogTable VALUES ('WebServer1',13,22) 
INSERT INTO @LogTable VALUES ('WebServer1',14,21) 
INSERT INTO @LogTable VALUES ('WebServer1',15,20) 
INSERT INTO @LogTable VALUES ('WebServer1',16,18) 
INSERT INTO @LogTable VALUES ('WebServer1',17,16) 
INSERT INTO @LogTable VALUES ('WebServer1',18,14) 
INSERT INTO @LogTable VALUES ('WebServer1',19,12) 
INSERT INTO @LogTable VALUES ('WebServer1',20,10) 
INSERT INTO @LogTable VALUES ('WebServer1',21,8) 
INSERT INTO @LogTable VALUES ('WebServer1',22,6) 
INSERT INTO @LogTable VALUES ('WebServer1',23,4) 

SELECT WebServer, [Hour], NumberOfUsers FROM @LogTable

Next create a table, using hour as the column and the number of users as the data field

Creating the table

Now in right click on the report designer and select the “Report Properties” option, and then the “Code” tab. Copy the GetHeatmapColor function (NOT the SqlReportingServicesHeatMapGenerator class) and paste it into the window

Entering the VBA code into the report

Right click in the text box that you want to generate the heat map for and select “Text Box Properties” (or use the properties window)

Text Box Properties

Then choose the “Fill” tab and click the Expression button.

Entering the Expression

In the expression window enter the line below

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

As Bob says his post “The argument ‘Dataset1′ defines the scope in which the min or max value is calculated, which must be a parent scope of the current scope.”

If you preview the report you should see the following

Jags Heat Map Report

Comments

  • Lalitha

    Hi,

    I also have a similar problem when exporting the heat map report to excel.

    But i cannot fix the values as well, since the values vary each time the report is generated.

    The color combination wht is given in Bob’s solution is what excatly i require as well.

    Is there any other solution apart from harded coding the background color.

    Thank you,
    Lalitha

  • http://www.arrangeactassert.com Jag Reehal

    Hi Lalitha,

    The values in the blog post are hard coded but the solution will work for values that vary each time the report is generated.

    The colors for the heat map have to be hard coded at some point as they will determine what will be used for the min and max values. I do this in the array and Bob does this using class variables.

    Cheers,

    Jag

  • Xomaly

    I have a problem similar like this. But I need to know how to create heat maps per row.
    Imagine you have two Webservers and you need to create heat maps for each rowWebserver1Webserver 2
    When I implemented this solution it was giving me heating maps for all the matrix, I need a heat map per each row

    Thank you