Pages

Saturday, March 10, 2012

Heat Maps for SSRS using Map Control

I have always been inspired by a particular quote from Arthur C Clarke - “The only way of finding the limits of the possible is by going beyond them into the impossible”. The more you think of it, the more you make sense out of it. If you decide in your mind that something is impossible, you will never even try to make it happen. Consider the example of the 10-second barrier in athletics. Rather than a physical barrier, it has always been more of a psychological barrier. How else can you explain the significantly high numbers of athletes that have broken the barrier in the last 2-3 years when compared to the 20 years between 1968 and 1988? Being mindful of that, I have always tried to be open when discussing requirements and always keep a secret list with me of the requirements that I have not been able to fulfil. One of my earliest list items was that of a squarified Heat Map (or Treemap as they are generally called) in SSRS. Even though there is no out of the box way to do it in SSRS, it was said to be possible by using the map controls and has been shown by Teo Lachev a long time ago. I had even progressed as far as making a T-SQL procedure which calculates the coordinates of the heat maps but it just wasn’t that elegant enough to be used in a production system. However, a recent blog by Richard Mintz on the Squarified Heat Maps has finally made me strike off this requirement from my list as completed.

HeatMaps in SSRS

To implement heat maps in your reports, follow the steps below:-

1) Download the HeatMap.dll file from here and save it in a location in your hard-drive, say C:\HeatMap (You should see the Download option on the File Menu). This dll file is the compiled version of the code given in Richard’s blog.

2) Open SQL Server Management Studio and then execute the following code in the database that you want to create the assembly.

CREATE ASSEMBLY HeatMap from 'c:\HeatMap\HeatMap.dll' WITH PERMISSION_SET = SAFE

3) After that, execute the following code to register the stored procedure

CREATE PROCEDURE dbo.CreateHeatMap (@Width real, @Height real, @SqlStrng nvarchar(4000))
AS
EXTERNAL NAME HeatMap.StoredProcedures.TreeMapGeography

In case you get an error saying that CLR is not enabled run the code below to enable CLR before creating the procedure again

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go

4) Now create a new report and a new dataset. For this report, I am going to be using a SQL query from the AdventureWorks database which will give me the Subcategory Name and Order Quantity. Order quantity will be the measure which will determine the size of the rectangles within the heat map. Enter the code below for the dataset

exec dbo.CreateHeatMap 20, 25, 'select  sum(FIS.OrderQuantity), EnglishProductSubcategoryName
from dbo.DimProductSubCategory SC
inner join DimProduct P
on SC.ProductSubcategoryKey=P.ProductSubcategoryKey
inner join dbo.FactResellerSales FIS
on FIS.ProductKey =P.ProductKey
group by EnglishProductSubcategoryName
order by  sum(FIS.OrderQuantity)  desc'

The first 2 parameters within the CreateMap procedure are used for setting the width and the height of the heat map and this can be customized as per your needs. The third parameter is basically the select statement which should return the measure used for determining the rectangle sizes as well as the names used for labelling the rectangles within the heat map. Ensure that the Text option has been selected for the query and it should look like shown below

Spatial Dataset

5) Now make another dataset which will have the analytical data used for visualizing the rectangles with colours. For this demo, I am using a MDX query from the AdventureWorks cube which will return the Subcategory Names and Reseller Gross Profit.

Analyticaal Dataset

6) Drag and drop a Map report item from the toolbox to the design layout and select SQL Server Spatial query as the data source. On the next screen, select the  spatial dataset which we had created in Step 4.

Choose spatial dataset

7) On the next screen, ensure that Geo is the spatial field and the Layer type is Polygon. Click next.

8) Choose Color Analytical Map  the map visualization and click next. Then choose the dataset that we created in step 5 as the analytical dataset.

Choose analytical dataset

9) Specify the match fields as Name and SubCategory as shown in the screenshot below and click on Next

Specify the match fields

10) In the next screen, choose the field to visualize as Reseller Gross Profit and click on Finish.

Fields to visualize

11) Now if the report is previewed, you can see a nice heat map which will have the dimension size based on the Order Quantity and the colours visualized based on the Gross Profit.

HeatMap Preview

If are interested in map/spatial reports, you might want to check out some of my related posts here.

Update (11/03/2012)

To make it culture insensitive, the code has been modified and a new version of the compiled code has been posted here.

10 comments:

  1. Asgeir GunnarssonMarch 11, 2012 at 7:21 AM

    Hi Jason.

    Nice timing that. I have just been asked by my manager if I could make a heatmap in SSRS for our IT architecture repository.

    Now I'm going to be a hero :-)

    Asgeir Gunnarsson

    ReplyDelete
    Replies
    1. @Asgeir : Now you definitely need to get me a beer for that when we meet! ;)

      Delete
  2. Hi,

    I tried it, but I get the following error message in the Mgmt Studio

    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
    System.FormatException: 24141: A number is expected at position 26 of the input. The input has ,.
    System.FormatException:
    at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
    at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringText()
    at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePolygonText()
    at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
    at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
    at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
    .

    Can you help?

    ReplyDelete
    Replies
    1. Can you let me know at which step you get this error? Also can you paste the code that generated this error?

      Delete
    2. I'm at step 4. I tried to write the SQL Statement in MgmT Studio.

      exec dbo.CreateHeatMap 20, 25, '
      SELECT isnull(round(sum(vcc.Umsatz),0),0) as Sales,isnull(vcc.ContractPart,''NoContract'') as ContractPart from v_cont_contracts vcc
      group by vcc.ContractPart
      order by sum(vcc.Umsatz) desc'

      If I limit it to 1 row, it works, as soon as there are more than 1 rows as result, the error is thrown.

      Delete
    3. Can you make sure that there are no null or blank rows returned in the result? If you still have the issue, can you mail me at jason143@gmail.com so that I can have a look at your code?

      Delete
  3. Hi Jason,

    I tried your solution but got this error
    A .NET Framework error occured during execution of user-defined routine or aggregate "CreateHeatMap": System.Overflow.Exception: Value was either too large or too small for an Int32.System.Overflow.Exception: at System.Convert.ToInt32(Double Value) at System.Convert.ToInt32(Int Value)

    ReplyDelete
  4. Can you post your "select statement" as well as some sample results of the select statement to jason143@gmail.com?

    ReplyDelete
  5. Thanks Jason. The solution worked.The error got resolved.
    Wanted to know do you have any post on implementing localisation in ssrs

    ReplyDelete
  6. Glad to hear that you were able to resolve it. And no, I dont have any post on localisation at the moment.

    ReplyDelete