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

Advertisements

40 thoughts on “Squarified Heat Maps SQL Reporting Services Part 2

  1. Great post. Infact, I was going to blog about it within a week but I was going to do without the C# part (yups, entirely with SQL) which was sort of clumsy. This is a more elegant solution.

  2. Hi Rich,

    I was implementing the same solution of yours in my project.
    I have two different servers. One has Visual Studio 2010 installed where I created the Stored Procedure but I don’t get the option of report server project and other server has SQL Server 2008 R2 i.e. reporting services. how can I use the Stored procedure cs file in my reporting services project on another server?

    1. Hi,

      You need to deploy the CLR Stored Procedure to the database that you will be querying, easiest way to deploy is by using the deploy menu command in Visual Studio 2010. Once the SP is deployed you execute it like any other SQL Stored Procedure in Report Builder or BIDS.

      Rich

    1. Well, it was much easier than I thought. Under the Map Properties, General, in the Tooltip section click the fx function expression and click on Datasets, Item, and Values and select the First(Name) value.

      Thanks for this awesome solution Rich!

      1. Sorry, the correct location and value for the tooltip function is under the Map Polygon Properties, which is under the layer eye dropdown when you have layer selected, then select drop down on ToolTip and select [Name].

  3. For the query that gets passed into the CLR stored procedure, I’d like to pass a SSRS paramater entered by the user running the report so i can add a where statement to the query. When I do this I cannot design the map because it complains no spatial data is being returned by the data set (since no value has been entered for paramater). Can anyone provide any insight how I can generate a heatmap after the user has provided paramater choices to filter on a specific subset of the data?

      1. For my dataset query I use type of “Stored Procedure” and call the following procedure. If I connect the dataset to a table the geographical data is returned when I run the report and supply the paramater. However, when I try to supply the spatial data to the map I get an error “Unable to determine the spatial data type due to connection error.” Followed by “Procedure or function ‘RunHeatMapbyFeature’ expects parameter ‘@Feature’, which was not supplied. No spatial data available. The map display contains sample spatial data.”

        PROCEDURE [dbo].[RunHeatMapbyFeature]
        @Feature nvarchar(50)
        AS
        BEGIN

        Declare @SQLString nvarchar(4000)

        Set @SQLString = ‘select NumberOfTests, ReqID from dbo.FeatureHeatMap36 where Feature = ”’ + @Feature + ””
        exec dbo.TreeMapGeography 10, 14, @SQLString

        END

      2. I found that I had to use a default value for my stored procedure paramater and ensure that the spatial data column names are returned by the stored procedure. This allows the SSRS map designer to have enough info to correlate the spatial and analytical data. Below is my updated stored procedure.

      3. PROCEDURE [dbo].[RunHeatMapbyFeature]

        @Feature nvarchar(50) = ‘Dummy’

        AS
        BEGIN

        Declare @SQLString nvarchar(4000)

        Create Table #temp(
        Geo geometry,
        Amount int,
        Name nvarchar(50)
        )

        Set @SQLString = ‘select NumberOfTests, ReqID from dbo.FeatureHeatMap36 where Feature = ”’ + @Feature + ””

        INSERT INTO #temp
        exec dbo.CreateHeatMap 10, 14, @SQLString

        Select * From #temp

        END

  4. Very nice work, Richard! Along with Jason Thomas’s CLR wrapper of your logic, we have a great SSRS tool now.

    Just one additional question: have you ever considered incorporating one level of subgroups into your squarified heat map logic?

    For example, if you had geographic data based upon state and city, the idea would be to first create the heatmap based upon an aggregate value by state. Then, within each state, aggregate the same value on each city.

    I’ve attempted to do such a thing by using Jason’s CLR recursively (one level deep), but it’s proving quite a task in SQL! Also, if done recursively, there would be no marking for the first-level group (eg: State), so visually, it wouldn’t be as apparent without a change in polygon color.

    Thanks again for a great starting point!

    1. Hi Jeff,

      I have actually thought about modifying the code to accomodate nesting, will try to take a look at it again see if inspiration strikes. As a work around you could generate a heatmap at the parent level then provide an action that generates a heat map of the children.

      Richard

  5. Hi Richard,

    Your squarified heat map code (wrapped in a CLR assembly thanks to Jason Thomas) has come in very handy!

    Could I ask you a small favor? Can you please try a simple dataset for me and see what the result is in SSRS?

    I’m using this text query in a shared dataset, and reduced the consumptions to small integers for simplicity:

    ===============================
    DECLARE @SQLExec VARCHAR(1000)
    CREATE TABLE #MyTable (
    Consumption INT,
    Location VARCHAR(50)
    )

    INSERT INTO #MyTable (Consumption, Location)
    SELECT 72, ‘Location 1’

    INSERT INTO #MyTable (Consumption, Location)
    SELECT 16, ‘Location 2’

    INSERT INTO #MyTable (Consumption, Location)
    SELECT 7, ‘Location 3’

    INSERT INTO #MyTable (Consumption, Location)
    SELECT 3, ‘Location 4’

    INSERT INTO #MyTable (Consumption, Location)
    SELECT 2, ‘Location 5’

    SET @SQLExec = ‘Select * FROM #MyTable’

    EXEC spLocal_CreateHeatMap 60, 200, @SQLExec

    DROP TABLE #MyTable
    ===============================

    For me, SSRS won’t draw Location 5!

    Silly thing is, if I change the first two parameters above to 30, 100, then it does.
    Just want to know if you see the same thing.

    Thanks,
    Jeff

      1. Thanks for checking that, Richard. I always see the 5th element in SSMS… It’s SSRS that is acting weird for me.

  6. Hi Jeff, could you elaborate on this? I’m also interested in representing sub categories and I’m not sure how to approach it.

    Richard Mintz :
    Hi Jeff,
    I have actually thought about modifying the code to accomodate nesting, will try to take a look at it again see if inspiration strikes. As a work around you could generate a heatmap at the parent level then provide an action that generates a heat map of the children.
    Richard

  7. Hi Richard,
    I followed your steps and was very excited to create my first heatmap. However, if I runTreeMapGeography stored procedure in my MSSMS, it could run forever for certain data set… Have you noticed this issues?
    Thanks a lot,
    Z

  8. This is an outstanding blog and my team and I are very interested in looking to implement this. I have been searching for a way to do this for a number of years, ever since I discovered the “Map of the Market” on Marketwatch.com. This squarified tree map, bu the way, does contain multi-level grouping/aggregation or nesting as has been referenced above, and is a feature we are extremely interested in doing. Thank you for this blog and the details on it above. We will be experimenting with this. Our BI tool already has the aggregation categories built into our heirarchies, so getting the nesting in place is of real interest to us. If you have any additional thoughts on this, we are very open to hearing them. Great job on this!

    http://www.marketwatch.com/tools/stockresearch/marketmap

  9. I’m very interested in this solution for a project that I’m working on.
    I’m trying to use health state data from Operations Manager which is represented as a numeric value 0, 1, 2 or 3 depending on the state in a heat map. I have my servers grouped by application and a query to display the max health state of the servers in each application group.
    My problem is that for each application listed I’d like a heat square, fixed size and correlated color to the state. Like 0=grey, 1=green, 2=yellow and 3=red. I’m getting geometry error leading me to think that I’m trying to use data that this solution isn’t exactly meant for.
    Any help would be greatly appreciated. The data from my query looks like this:
    ServiceName HealthState
    SMTP Relay 3
    Amcom – Prod 1
    App-V 2
    Infrastructure Testing 0
    Active Directory 2

    Thanks, Brett

    1. If you are looking for a fixed size to represent each server, could you not use a basic table and background colours to identify the state. or you could pass a static value into to the function along with the then use the 1,2,3 as the background colour property for each region in the map. Hope this makes sense, I will try to dummy something up for you.

      1. As long as it would be dynamic in that the number of applications can change. For instance the business may add a new application that I’m monitoring. Or, decommission one.
        Thanks,
        Brett

      2. Richard,

        A colleague of mine saw my SQL query and results and pointed out the problem. I was setting a null value to 0 and that was causing the errors that I was getting. Now, I set the null to 5 and it’s working for me. I still would love to set each polygon to a fixed size rather than having polys of different sizes on the report. Thanks again for the post. This is going to be a great way to visualize the data outside of the OpsMan console.

        Brett

      3. Hi Brett,

        The size of the polygon is governed by the value you pass in, if you pass in the same value for all items the polygons should all be the same

        On Thursday, January 22, 2015, Richard Mintz's BI Blog wrote:

        >

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