Sometime in January, visualization expert Stephen Few expanded on Tufte’s idea of Sparklines and came up with this new visualization called Bandlines. Bandlines use horizontal bands of color in the background of the plot area to display information about the distribution of values and you can read a detailed description on bandlines in Stephen’s article. Companies like XLCubed have already started integrating this within their products and it was only a matter of time before yours truly tried to replicate the same within SSRS. Read on for the solution.
One of the most important steps to reproduce this in SSRS is to find the percentiles (25th and 75th) in your dataset query. You can do it the hard way by coming up with the logic and then computing it within your dataset. I chose not to reinvent the wheel and used Richard Mintz’s (yes, the same guy who came up with the code behind Squarified Heatmaps in SSRS) SSAS stored procedure for the same. He has written an excellent post on how to compute percentiles in both SQL and MDX, so you can use his code depending on whether your source is a relational database or a multidimensional cube. For this demo, my source would be the AdventureWorks multidimensional cube. Follow the steps below to reproduce bandlines:-
1) Download the dll from here and add it to your SSAS instance as per the instructions at the end of Richard’s post. I have named the assembly as SPPercentile.
2) Make a dataset query which will show the Internet Sales amount for a product category and month with the year as a parameter (optional, dataset query for year parameter is not shown here). Also make 2 calculated measures which will show the maximum and minimum values for internet sales by category across the months.
WITH MEMBER measures.[maxP] AS
Max (
StrToSet ( @DateCalendarYear ) * [Product].[Category].CurrentMember * [Date].[Month Name].[All] * [Date].[Calendar].[Month].AllMembers,
[Measures].[Internet Sales Amount]
)
MEMBER measures.[minP] AS
Min (
StrToSet ( @DateCalendarYear ) * [Product].[Category].CurrentMember * [Date].[Month Name].[All] * [Date].[Calendar].[Month].AllMembers,
[Measures].[Internet Sales Amount]
)
SELECT NON EMPTY
{ [Measures].[Internet Sales Amount], measures.[maxP], measures.[minP] } ON COLUMNS,
{
NonEmpty ( [Date].[Month Name].Children, [Measures].[Internet Sales Amount] ) * [Product].[Category].[Category].AllMembers
} ON ROWS
FROM [Adventure Works]
WHERE StrToSet (
@DateCalendarYear
)
Note that the maximum / minimum will be the same for a category across all months. This is required for doing the scaling of the bandlines, and will be explained in a later step.
3) Make a dataset query for getting the 75th and 25th percentiles.
WITH MEMBER measures.[25th percentile] AS
[SPPercentile].ValueAtPercentile ( NonEmpty (
( StrToSet ( @DateCalendarYear ) * [Date].[Calendar].[Month].AllMembers * [Product].[Category].Children,
[Measures].[Internet Sales Amount] )
),
[Measures].[Internet Sales Amount],
.25,
TRUE,
“INC” )
MEMBER measures.[75th percentile] AS
[SPPercentile].ValueAtPercentile ( NonEmpty (
( StrToSet ( @DateCalendarYear ) * [Date].[Calendar].[Month].AllMembers * [Product].[Category].Children,
[Measures].[Internet Sales Amount] )
),
[Measures].[Internet Sales Amount],
.75,
TRUE,
“INC” )
SELECT NON EMPTY
{ measures.[25th percentile], measures.[75th percentile] } ON COLUMNS
FROM [Adventure Works]
Note that we are using the [SPPercentile].ValueAtPercentile() stored procedure and you can find the syntax in Richard’s post. Now we should be having the following three datasets.
4) Now that we have found out the percentiles, store their values in two hidden report parameters respectively – PC25 and PC75. Make sure that that the available values and default values are obtained from the corresponding field in DST_Percentile dataset as shown below.
5) Now drag and drop a matrix into the layout. Use the Categories on the rows and drag and drop a line chart to the values as shown below. In the chart, use Internet Sales Amount as the Values, Month Name as the category group and Category as the Series Group.
6) Make the max and min of the vertical axis as the maxP and minP fields. This will help in scaling the sparkline (yes, by now we have got the basic structure of a sparkline).
Also, remove the vertical axis, horizontal axis, legend and all other titles.
7) Add a value to the chart with the expression below.
=iif(Fields!maxP.Value <= Parameters!PC75.Value, Nothing, Fields!maxP.Value)
Make the chart type as Area Chart and color as #f5f5f5
This will act as the band for the values between 75 and 100 percentile, so ensure it is at the very top as shown in the picture above. If the max value for the category is lesser than the PC75, we don’t want to show this band.
8) Add another value to the chart with the expression below
=iif(Fields!maxP.Value >=Parameters!PC75.Value, Parameters!PC75.Value, Fields!maxP.Value)
Make the chart type as Area Chart and color as #d7d7d7
This will act as the band between 25 and 75 percentile and should be appearing on top of the 75-100 band, so ensure that it appears just under the previous value as shown in the picture above.
9) Add the last band by adding the expression below
=iif(Fields!maxP.Value <= Parameters!PC25.Value, Fields!maxP.Value, iif(Fields!minP.Value>=Parameters!PC25.Value, Nothing, Parameters!PC25.Value))
Make the chart type as Area Chart and color as #adadad
Again ensure that this value appears just under the previous one, as shown in the picture above.
10) Now, we can preview it and see the results.
From the markers, you can also note that there are 24 values. Out of that, there are 6 values in the 75-100 band, 12 values in the 25-75 band and 6 in the 0-25 band. So the percentiles are indeed working! And very quickly, you can make out that Bikes have more of their values in the 75-100 percentile, an extra bit of information that you wouldn’t have got with sparklines. That is the power of Bandlines.
11) Now with a bit of formatting, this is the final end result and you can download the completed report rdl (SSRS 2012) from here.
