SQL 2012 – Exciting times

Just installed SQL 2012 and it was the most pleasant installation experience that I have had with SQL Server, no work arounds or config file modifications; double clicked setup.exe and clicked next until finished. Make sure you uninstall any Denali/2012 pre RTM instances including PowerPivot that you may have and remove old Denali/2012 PowerPivot and SSRS service applications in SharePoint Central Admin

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