Friend of mine, let’s call him Gerald, asked me what is the point of this Report, to which I said ‘Green is good and Red is bad’, not such a good answer. I suppose the best way that I can describe the value of this type of visualization is; for a reasonably sized data sets you are providing the end user with a report that displays each members relative merit not only in relation to the parent or the whole but also in relation to its siblings. By Leveraging report actions in SSRS you can click through to see how the children of any member are contributing to the relative success or failure of the member. Please let me know your thoughts on the merits of this report and my implementation.
One day I was thinking about what it would take to create a bunch of rectangles within a larger rectangle based on the relative weight of the smaller rectangles to the larger rectangle, to create a heat map in SSRS.
So I Googled it and found a couple great posts from Teo Lachev and Jonathan Hodgson and the “Squarified Treemaps” paper by Mark Bruls, Kees Huizing, and Jarke J. van Wijk of the Eindhoven University of Technology, Dept. of Mathematics and Computer Science: (thank you very much)
Using these three resources I began my journey.
First I created a simple windows forms application with a multiline text box. The only thing that this app does is display a string comprised of unioned select statements so I could test the effectiveness of my code in SSMS. After about 1000 attempts it actually worked. Given a set of data, width and height, the application generated a select statement that would deliver the correct ‘Squarified’ Heat map visualization.
The next step was to create a CLR Stored Procedure that accepts the following parameters Height, Width, and Query. I made sure that the procedure would not execute the if it contained key words like, delete, insert, update, drop, truncate and alter.
Basically what the Procedure does is.
- Executes the query passed in and loads a datatable
- Instantiates a HeatMap object, passing in the datatable, Height and Width
- Sets the Commandtext of a sqlCommand to the sqlString attribute of the HeatMap object previously created
- Executes the sql command and send the results back.
Once the Stored Procedure was created and working it was time to put it to use. I created a New SSRS Report added a Map report item, used the SQL Server Spatial query option, set the stored procedure as my spatial query passing in the appropriate parameters; resulting in the report below. The data for this report is sourced from the Contoso Dataset
In Part Two I will go through the source code and provide step by step instructions for creating the report.