Squarified Heat Maps SQL Reporting Services Part 2

##UPDATE##
Jason Thomas, check out his blog here http://road-blogs.blogspot.com/,  asked what is the first column in the select statement passed into the stored procedure used for; the answer is nothing so I have removed it and modified the Code and SQL Statement in the post.

Thanks Jason
##

Well it has been a while and I know how annoying it can be when the part II of a part I you are interested in never shows up, all I can hope is that there is somebody out there that is interested.

Steps to creating the Report

  • You will need the Contoso Data Warehouse for the query used in this demo report to execute
  • Launch Visual Studio 2010 and create a new Visual C# SQL CLR Database Project

  • Follow the wizard and setup database connection


  • Add a new stored procedure to the project


Replace the contents of the new .cs file with the code below.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Globalization;

//Enumeration used in the TreeMap creation functions
enum myOrientation { Horizontal, Vertical };

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TreeMapGeography(SqlSingle Height, SqlSingle Width, SqlString select)
    {
        //Check to see if the SQL Statement passed into the proc contains bad words
        if (select.ToString().ToLower().Contains("update") || select.ToString().ToLower().Contains("delete")
            || select.ToString().ToLower().Contains("truncate") || select.ToString().ToLower().Contains("drop")
            || select.ToString().ToLower().Contains("alter") || select.ToString().ToLower().Contains("create"))
            return;
        //Open SQL Connection
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(select.ToString(), connection);
                SqlDataReader r = command.ExecuteReader();
                DataTable t = new DataTable();

                if (r.HasRows)
                {

                    t.Load(r, LoadOption.OverwriteChanges);
                    //Instantiate new TreeMap Object
                    /*sqlString property of the TreeMap will contain the sql statement required
                    to generate the treemap*/
                    TreeMap hm = new TreeMap(t, (Single)Height, (Single)Width);
                    if (hm.sqlString != "")
                    {
                        command.CommandText = hm.sqlString;
                        SqlContext.Pipe.ExecuteAndSend(command);
                    }
                    else
                    {
                        SqlContext.Pipe.Send("TreeMap Function Failed to generate any records");
                        return;
                    }
                }


            }
            catch (SqlException e)
            {
                SqlContext.Pipe.Send(e.Message);
            }
        }
    }


    private class TreeMap
    {
        public class TreeMapData
        {
           /*public int id { get; set; }*/
            public int amount { get; set; }
            public Single scaledAmount { get; set; }
            public Single x { get; set; }
            public Single y { get; set; }
            public Single w { get; set; }
            public Single h { get; set; }
            public string nameColumn { get; set; }
            public myOrientation Orientation { get; set; }

            public TreeMapData(/*int Id,*/ int Amount, string NameColumn)
            {
                /*id = Id;*/
                amount = Amount;
                nameColumn = NameColumn;
            }
        }

        private Single myHeight = 0;
        private Single myWidth = 0;
        private Single aspect = 0;
        private Single yOffSet = 0;
        private Single xOffSet = 0;
        private Single valueScale = 0;
        public string sqlString = string.Empty;

        private List<TreeMapData> valueList = new List<TreeMapData>();
        private List<TreeMapData> vList = new List<TreeMapData>();

        public TreeMap(DataTable TreeMapData, Single Height, Single Width)
        {
            myHeight = Height;
            myWidth = Width;
            foreach (DataRow dr in TreeMapData.Rows)
            {
                TreeMapData h = new TreeMapData(Convert.ToInt32(dr[0]), dr[1].ToString());
                valueList.Add(h);
            }
            if (valueList.Count > 0)
                sqlString = Squarify(valueList);
        }

        public string Squarify(List<TreeMapData> fullList)
        {
            string sqlString = String.Empty;
            Single tAspect = 999;
            Single lAspect = 0;
            int passes = 1;
            int i = 1;
            scaleAmounts();
            for (; i <= fullList.Count; i++)
            {
                lAspect = getAspect(myHeight, myWidth, fullList, passes, DrawingOrientation(myWidth, myHeight));
                if (lAspect >= tAspect || i == fullList.Count)
                {
                    tAspect = 999;
                    layout(myHeight, myWidth, fullList, passes, DrawingOrientation(myWidth, myHeight));
                    passes = 1;
                    if (fullList.Count == i)
                        fullList.Clear();
                    else
                        fullList.RemoveRange(0, i - 1);
                    i = 0;
                }
                else
                {
                    passes++;
                    tAspect = lAspect;
                }
            }
            foreach (TreeMapData t in vList)
            {
                sqlString += String.Format(CultureInfo.InvariantCulture," Select geometry::STPolyFromText('POLYGON (({0} {1}, {2} {3}, {4} {5}, {6} {7}, {8} {9}))', 0) as Geo, {10} as amount, "
                    + " '{11}' as Name \r\n Union all\r\n",
                   t.x, t.y,
                   t.x, t.h + t.y,
                   t.x + t.w, t.h + t.y,
                   t.w + t.x, t.y,
                   t.x, t.y,
                  t.amount, t.nameColumn.Replace("'", "").Trim());
            }
            return sqlString.Substring(0, sqlString.Length - 13);
            //if(getAspect(Height, Width, fullList, DrawingOrientation(Width, Height)) <= 
            //setRectangles(vList, 4, 6);
        }
        public void scaleAmounts()
        {
            Single dataTotal = 0;
            foreach (TreeMapData t in valueList)
            {
                dataTotal += t.amount;
            }
            valueScale = (myWidth * myHeight) / dataTotal;

            foreach (TreeMapData s in valueList)
            {
                s.scaledAmount = s.amount * valueScale;
            }
        }
        private myOrientation DrawingOrientation(Single width, Single height)
        {
            return width > height ? myOrientation.Vertical : myOrientation.Horizontal;
        }
        private Single getAspect(Single height, Single width, List<TreeMapData> fullList, int passes, myOrientation orientation)
        {
            Single tWidth = 0;
            Single tHeight = 0;
            Single total = 0;

            for (int t = 0; t < passes; t++)
            {
                total += fullList[t].scaledAmount;
            }
            switch (orientation)
            {
                case myOrientation.Vertical:

                    tWidth = total / myHeight;

                    for (int i = 0; i < passes; i++)
                    {
                        tHeight = myHeight * fullList[i].scaledAmount / total;
                        fullList[i].h = tHeight;
                        fullList[i].w = tWidth;
                    }
                    aspect = Math.Max((tHeight / tWidth), (tWidth / tHeight));
                    break;

                case myOrientation.Horizontal:
                    tHeight = total / myWidth;

                    for (int i = 0; i < passes; i++)
                    {
                        tWidth = myWidth * fullList[i].scaledAmount / total;
                        fullList[i].h = tHeight;
                        fullList[i].w = tWidth;
                    }
                    aspect = Math.Max((tHeight / tWidth), (tWidth / tHeight));
                    break;
            }
            return aspect;
        }
        private void layout(Single height, Single width, List<TreeMapData> fullList, int passes, myOrientation orientation)
        {
            Single tempHeight = 0;
            Single tempWidth = 0;
            Single tWidth = 0;
            Single tHeight = 0;
            Single total = 0;
            passes = fullList.Count == passes ? passes : passes - 1;

            for (int t = 0; t < passes; t++)
            {
                total += fullList[t].scaledAmount;
            }

            switch (orientation)
            {
                case myOrientation.Vertical:
                    tWidth = total / myHeight;
                    for (int i = 0; i < passes; i++)
                    {
                        tHeight = myHeight * fullList[i].scaledAmount / total;

                        fullList[i].h = tHeight;
                        fullList[i].w = tWidth;
                        fullList[i].Orientation = orientation;
                        fullList[i].x = xOffSet;
                        fullList[i].y = yOffSet + tempHeight;
                        vList.Add(fullList[i]);
                        tempHeight += tHeight;
                    }
                    //myWidth -= tWidth;
                    xOffSet += tWidth;
                    myWidth -= tWidth;
                    tempHeight = 0;


                    // staticWidth -= tempY;


                    break;
                case myOrientation.Horizontal:

                    tHeight = total / myWidth;
                    for (int i = 0; i < passes; i++)
                    {
                        tWidth = myWidth * fullList[i].scaledAmount / total;

                        fullList[i].h = tHeight;
                        fullList[i].w = tWidth;
                        fullList[i].Orientation = orientation;
                        fullList[i].x = xOffSet + tempWidth;
                        fullList[i].y = yOffSet;
                        vList.Add(fullList[i]);
                        tempWidth += tWidth;


                    }

                    yOffSet += tHeight;
                    myHeight -= tHeight;

                    break;
            }

        }
    }
};
  • Ensure that the .Net Framework being targeted is 3.5

  • Deploy the project
  • Create a new Report Server Project (SQL 2012 BI Templates are in Visual Studio 2010)

  • Add a new report to the Project


  • Drag a Map in the report surface

  • Select spatial query option

  • Create new Dataset

  • Create a new data source
  • Click Next


  • In the query designer click ‘Edit as Text’
  • Copy the query below into the query text pane

exec dbo.TreeMapGeography 16, 25, ‘select sum(SalesAmount) as salesamount,c.productsubcategoryname from contosodw..factsales a join contosodw..Dimproduct b on a.productKey = b.productKey join contosodw..[DimProductSubcategory] c on b.productsubcategorykey = c.productsubcategorykey where datepart(yyyy,a.DateKey) = 2009 group by b.productsubcategorykey, c.productsubcategoryname order by salesamount desc’

  • Click Next


  • Uncheck the embed data in report option
  • Click next

  • Choose the Color Analytical Map Option
  • Click Next

  • Choose the current dataset as the Analytical Dataset
  • Click Next

  • Select Sum(amount) as the field to visualize
  • Check Display Labels
  • Choose the Name field as the Data field
  • Click next

  • Delete the Legend, and Scales

  • Right click on the map and select polygon Color Rules

  • Modify the Start Color to Red, Middle to Yellow and End to Green

  • Click Preview

Squarified Heat Maps SQL Reporting Services Part 1

Update:

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.