Generating multiple rows from a single record with start and end columns
Working on a project where the reporting requirement is to take a single record containing the overall total, the start and end period and generate multiple rows including all periods between the start and end. I knew that I could get the results that I needed with a common table expression (CTE) but time was of the essence so I turned to my trusty friend the ‘Cursor’. I know Cursors are for the weak and the lazy, but i needed to prototype something really fast and for me this was the easiest way. For those of you wondering why is he blogging about being weak and lazy, I did return to the CTE method and was rewarded with the required results using a fraction of the SQL and produced a stored procedure which is easier to modify and tweak as reporting requirements change.
Below you will find some sample code used to generate multiple rows from a single datarow containing a start and an end column. In this example I show both using integers and dates to denote the beginning and end and end values.
/*using integers as starting and ending values*/ create table #cteTempInt ( id int identity (1,1) ,amount numeric(18,2) , Divisor int , starting int , ending int ) insert into #cteTempInt Values(1200, 7, 1, 6) insert into #cteTempInt Values(4300, 10, 2, 11) insert into #cteTempInt Values(2400, 4, 3, 6) print 'results from integer sample' ;with t(id, periodAmount, period, ending) AS ( select id, amount/divisor as periodAmount, starting as period, ending from #cteTempInt union all select id, periodAmount, period +1 as period , ending from t where period+1 <= ending ) select id, periodamount, period, row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period DROP TABLE #cteTempInt /*using dates as starting and ending values*/ create table #cteTempDate ( id int identity (1,1) ,amount numeric(18,2) , Divisor int , starting date , ending date ) insert into #cteTempDate Values(1200, 7, '2012-01-01','2012-06-01') insert into #cteTempDate Values(4300, 10,'2012-02-01','2012-11-01') insert into #cteTempDate Values(2400, 4, '2012-05-01','2012-08-01') print 'results from date sample' ;with t(id, periodAmount, period, ending) AS ( select id, amount/divisor as periodAmount, starting as period, ending from #cteTempDate union all select id, periodAmount, dateadd(mm, 1, period) as period , ending from t where dateadd(mm,1,period) <= ending ) select id, periodamount, period, row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period drop table #cteTempDate
results from integer sample id periodamount period paymentNumber ----------- --------------------------------------- ----------- -------------------- 1 171.4285714285714 1 1 1 171.4285714285714 2 2 1 171.4285714285714 3 3 1 171.4285714285714 4 4 1 171.4285714285714 5 5 1 171.4285714285714 6 6 2 430.0000000000000 2 1 2 430.0000000000000 3 2 2 430.0000000000000 4 3 2 430.0000000000000 5 4 2 430.0000000000000 6 5 2 430.0000000000000 7 6 2 430.0000000000000 8 7 2 430.0000000000000 9 8 2 430.0000000000000 10 9 2 430.0000000000000 11 10 3 600.0000000000000 3 1 3 600.0000000000000 4 2 3 600.0000000000000 5 3 3 600.0000000000000 6 4 results from date sample id periodamount period paymentNumber ----------- --------------------------------------- ---------- -------------------- 1 171.4285714285714 2012-01-01 1 1 171.4285714285714 2012-02-01 2 1 171.4285714285714 2012-03-01 3 1 171.4285714285714 2012-04-01 4 1 171.4285714285714 2012-05-01 5 1 171.4285714285714 2012-06-01 6 2 430.0000000000000 2012-02-01 1 2 430.0000000000000 2012-03-01 2 2 430.0000000000000 2012-04-01 3 2 430.0000000000000 2012-05-01 4 2 430.0000000000000 2012-06-01 5 2 430.0000000000000 2012-07-01 6 2 430.0000000000000 2012-08-01 7 2 430.0000000000000 2012-09-01 8 2 430.0000000000000 2012-10-01 9 2 430.0000000000000 2012-11-01 10 3 600.0000000000000 2012-05-01 1 3 600.0000000000000 2012-06-01 2 3 600.0000000000000 2012-07-01 3 3 600.0000000000000 2012-08-01 4 (20 row(s) affected)
Squarifed TreeMaps Update
Just updated the code section of my previous post, fixed the formatting and the copy – paste into html editor mistakes.
Squarified Heat Maps SQL Reporting Services Part 2
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;
//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]), Convert.ToInt32(dr[1]), dr[2].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(" 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 b.productsubcategorykey, 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)
- http://prologika.com/CS/blogs/blog/archive/2009/08/30/heat-maps-as-reports.aspx
- http://www.codeproject.com/KB/recipes/treemaps.aspx
- http://www.win.tue.nl/~vanwijk/stm.pdf
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.
SQL – Converting an integer to datetime
Sometimes in life you come across integers that represent a datetime value and you want to convert it to make it more appealing . Here is a handy little helper that will convert integers represented as ‘YYYYMMDDHHMM’, ie 200805051010;
select CAST(STUFF(STUFF(STUFF(STUFF(CAST(200805051010 as varchar(20) ), 5, 0, ‘-’), 8, 0, ‘-’),11,0,’ ‘), 14, 0, ‘:’) as datetime)
as dt.
(You could also use the convert function if you wanted a different date format, I know I could have cast the int to a varchar(8) and gotten a valid date without all the stuffing, but I needed to keep the time part of the datetime)
As with just about everything that I do, I am sure there is a much better more efficient way to do it that involves less typing, but it works and that is my ultimate goal
Power View aka ‘Project Crescent’ Installed and Working
Well it took the better part of the weekend, but I got finally got SQL Denali and Power View working side by side with SQL 2008 R2. Warning this was not a pleasant experience and should not be attempted unless you enjoy being frustrated and swearing uncontrollably at your computer.
Now the fun begins.
PPS Cascading Filters Save the Day
The Scenario:
Time Intelligence filter based on a typical date dimension filtering scorecard and Analytical reports, needed to add an Excel Services Report with a Parameter based on the Year attribute of the time dimension. I tried connecting the TI filter to year parameter available in the Excel Services report with a connection formula like: EXISTS([Date].[Years].members, <<UniqueName>>,”MeasureGroup”) expecting it to work, problem was that excel did not like it. Cascading filters to the rescue.
The Solution:
Created a Member Selection filter from the descendants of the Years Attributes, connected the TI filter to the Member Selection filter with the above connection formula, passed the member selection filter to the report, excel is happy again
The Gotchas:
- Cascading Filters filter Nulls, so if you need to return members that are in fact null you need to create a plug measures and set that as your filter measure
- Member Selection filters not based on a Function like Children or Descendants that you pass a connection formula into get created with a ‘With Set’ Expression and not the expected ‘strtoset’ expression and do not work very well (my experience anyway)
Will post a how to shortly.
Migrating Custom SharePoint Column nightmare
Well I had to migrate custom columns from one SharePoint 2010 environment to another, thanks to the following post, I was able to script it and migrate.
Easy right, not so fast, went to check my columns out and got this great error when accessing site columns under site settings
Field type is not installed properly. Go to the list settings page to delete this field.
Turns out I made a typo and left a column type blank. after scouring the internet, i came across somebody brave enough to delete from the content database.
So I looked in the contenttypes table in the Content DB, found the offending record, deleted it and SharePoint started to work again.
I hope this helps.
