I am a fan of Jon Peltier for his work around Excel visualisation. It is incredible how many ways we can tweak Excel charts to get great results – out of the box, with a little bit of development effort. In terms of flexibility and power, SSRS is very similar to Excel and two of its charting components – the Stacked Column chart and the Stacked Bar chart allow us to create some interesting non-default charts.
Let’s see an example of a typical stacked graph based on a simple T-SQL dataset:
SELECT ‘A’ AS description, 0 AS fPoint, 100 AS sPoint
SELECT ‘B’ AS description, 50 AS fPoint, 200 AS sPoint
In the designer:
And when rendered:
This is a typical bar chart and there is nothing new here. The interesting part starts when we change the colour of the fPoint to No Color. The same graph, rendered starts looking like something else:
Hmm, it looks like…a Gantt Chart! If we were to replace A and B with task names and the X axis with date/time values we have the opportunity to create a chart somewhat resembling a real Gantt chart. In the data set we must observe the rule that fPoint shows time when the task has begun and sPoint – its duration. For a detailed walk-through you can check out this article on SQL Server Central.
But the post does not finish here. With the same technique (different data set) we can create the following, as well:
A pyramid chart! The data set has its own set of rules, which are different to the Gantt chart. The data set used for this example was:
SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint
The easiest way to show how to build the data set is to imagine we have a third series – tPoint:
SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint, 5 AS tPoint
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint, 3 AS tPoint
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint, 2 AS tPoint
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint, 0 AS tPoint
The sum of fPoint, sPoint and tPoint should always be the same and equal to the base of the pyramid and to maintain the symmetry, we want fPoint and tPoint to be the same. In general, the formula for this is:
fPoint = (Max(sPoint) – sPoint) / 2
We have to guard against 0/2 as we will always have one case (for the Max(sPoint)) in the data set where this equals 0.
And one last example of the power of the stacked charts in SSRS:
Here the Stacked Bar is changed to Stacked Column chart type in SSRS and the data set used is:
SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint
SELECT ‘C’ AS description, 2 AS fPoint, 1 AS sPoint, 0.1 AS tPoint
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint
The tPoint is the marker for our targets and we can adjust the thickness by altering our data set. One limitation is that we cannot have targets lower than the top of the actual (i.e. the tPoint will always appear above, and not in the middle of, the fPoint bar), but this could be useful if we know that tPoint must appear above or just on top of fPoint (e.g. sPoint = 0 will give us a marker capping the bar). We can change this by adding one more series – topPoint, which will be 0 when we are below, or on target and will show the amount above target when we have some:
SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
SELECT ‘C’ AS description, 1 AS fPoint, 0 AS sPoint, 0.1 AS tPoint, 1 AS topPoint
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
Then we can add it to the chart and colour the series in the same colour as the fPoint series:
When we render this we get:
There we go, the limitation has been lifted.
Note that for each different variation of the charts we are using customised data sets. The data transformations needed may not be trivial in some cases, but for most purposes they will be very simple and easy to work with in T-SQL; and because we keep this logic in T-SQL and just feed it ready for plotting to SSRS these charts render fast – a good practice worth re-iterating here.