Shrink SSRS HTML5 rendered document size for gmail

I have a C# app that generates SSRS Reports which are either attached to an email or added to the body of the message if the rendering format is HTML. Emails are looking great and then someone opens it in the gmail web client and sees a nasty [message clipped] message, turn outs gmail clips the contents of an email body if it is bigger than 105kb.

After reviewing some documentation, https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/rendering-to-html-report-builder-and-ssrs, I noticed that the rendering engine will render both a full HTML and fragmented HTML document. Wouldn’t you know it the fragmented version is much smaller, all styling is inline and not so bloated with javascript.

I render my reports using the ReportExecutionService class which contains a method called Render; Render accepts a DeviceInfo parameter that when set to null gives you the full HTML document, however if you set the value of DeviceInfo to   @"<DeviceInfo><StreamRoot>/</StreamRoot><HTMLFragment>True</HTMLFragment></DeviceInfo>" you get a fragmented small html file that renders in gmail perfectly.

The code snippet below provides a simple inline if statement to set the value

string deviceInfo = format == "HTML5" ? @"<DeviceInfo><StreamRoot>/</StreamRoot><HTMLFragment>True</HTMLFragment></DeviceInfo>" : null; 

Advertisements

SSRS SharePoint 2013 Create Service Application error: Could not find SOFTWARE\Microsoft\Microsoft SQL Server\110 registry key Error

This post falls under the category, things to remember when installing the Reporting Services SharePoint add-in for SQL 2012

In order to get SSRS for SharePoint to work the RS Add-in must be installed on all app servers in the Farm, if you are averse to waiting for huge .iso files to copy from server to server, the pain of the SQL Installation and decide to download the SQL Server® 2012 SP1 Reporting Services Add-in for Microsoft® SharePoint don’t. This installer does not create the required registry setting on the app servers where you have not installed the full SSRS for SharePoint Integrated Instance. Use the SQL Server installation .iso select the RS Add-in for SharePoint from the feature selection and things start to work, more up front work, much less back end pain.

Power View Really Improved in Office 2013

I Have recently had the opportunity to experiment with Power View embedded directly in Excel 2013. I can happily say that Power View has come a long way since the initial public release with SQL 2012 RTM, and would like to share a couple of things that I think are pretty cool.

  • Power View now supports Map visualizations with Bing Maps
    • You tell Bing where the data should go by placing members with location values in the Locations area or by placing longitude and latitude members in the provided fields.


PowerPivot 2013 allows you to assign geographical categories to your Column to help Bing geocode

  • You can add Multiple Members to the Axis in Charts and Graphs giving users the ability to drill through into greater detail by double clicking. The interface also provides the ability to navigate back to the previous level


  • Drill Down available in the Matrix table type,, providing an interactive pivot chart like experience


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.

Ragged Parent Child Hierarchies SSRS

Awesome Post explaining how to best setup an SSAS Parent Child hierarchy in SSRS,

http://blog.davyknuysen.be/2010/04/16/parent-child-hierarchies-in-reporting-services/

However if you have a ragged hierarchy you wind up with repetitive members, to address this issue you can use the following visibility expression in the Row Group properties

In the ‘Row Groups’ Pane click the down arrow on the grouping and select group properties.

  • Select Visibility
  • Select Show or Hide Based on Expression
  • insert the following expression ‘=iif(Instr(fields!fieldName.UniqueName, “DATAMEMBER”), true, false)

 

SQL 2012 Release Candidate 0

Just installed SQL 2012 RC0 specifically to check out the new stuff in Power View, have a look at http://blogs.msdn.com/b/robertbruckner/archive/2011/11/17/what-s-new-in-power-view.aspx to see all cool new features, however there are few things that I would have loved to have seen included.

– Support for Hierarchies

– Cards or Tile region slicers, the addition of grid filters is cool, but if you are filtering cards within a region that contains a grid with related measures it does not get filtered.

– Font control within grids and cards

– Image size control

– Dimension attribute aliasing

– The abilty to publish documents that cannot be edited.