Given that comparison is a starting point to any investigation of the data, it is important to ensure that people are not blindly going through SSRS chart wizards, believing the representation to be correct. As this illustration shows, it is important to double-check that the SSRS mechanisms are being correctly deployed to produce the correct visualisation. This blog will help you to do that, using sparklines as an example.
Few (2009), in his work Now You See It, described comparison as ‘the beating heart of analysis’ since it is so fundamental, so vital to the analytics activity. As Few rightly points out, this is constituted of two complementary activities:
– Comparison – looking for similarities
– Contrast – looking for differences
Fundamental to this activity is the comparison of magnitudes i.e. whether one value is greater than, smaller than, or the same as, another value. There are plenty of data visualisations which can help with this evaluation, including line graphs, bar charts and sparklines. However, to ensure the integrity of the visualisation, it is important to ensure that any missing data does not mislead the message of the data.
In order to ensure that this does not happen in the case of sparklines, SQL Server Reporting Services 2008 R2 introduced a new feature called ‘Domain Scope’. Essentially, this allows the report writer to align and synchronise group data, thereby a column is present regardless of whether or not there is data for a given quarter, for example. For comparison purposes, it is better to show a gap for missing data for a given category member, so that this feature of the data can be compared with a category member where data is present.
This feature is particularly apparent when sparklines are being created. I first saw this noted in the Reporting Services Recipes book by Paul Turley, but I have extended it to include vertical axis comparison as well. Also, from the data visualisation perspective, I always think it is important for people to understand why they are conducting an activity to produce a report; so hopefully the comments will be helpful to you as we proceed.
Notes before you begin
This uses the database called AdventureWorksDW2008R2
This assumes you are using SQL Server Reporting Services 2008 R2
I have included a sample stored procedure for you to use. You may find it here: http://goo.gl/YeC5B
If you click on any of the pictures, it will take you through to my flickr account so you can take a closer look.
If you click on any of the pictures, it will take you through to my flickr account so you can take a closer look.
1. Create a new report called Sparkline Dashboard
2. Create a Dataset using the DS_CategoryResellerAndInternetOrderSales shared dataset.
3. Drag a Matrix element onto the canvas
From the ‘Report Data’ section on the left hand side, navigate to the ‘DS_CategoryResellerAndInternetOrderSales’ dataset.
The screen will now appear as follows:
4. Drag ‘Reseller Order Sales’ over to the box marked ‘Data’.
5. It’s then necessary to make a column for the sparkline to be put in place. This is done by clicking on ‘Order Year’ in the Column Groups section, then navigate to the ‘Add Total’ menu item, and choosing ‘Before’.
6. The tablix is prepared, ready to enter in the Sparkline chart. To do this, right click on the column marked ‘Total’ to produce a menu. Select ‘Tablix’ -> ‘Insert’ -> The screen will now appear as follows:
7. Once the sparkline is in place, it requires the correct data. From ‘Report Data’ on the left hand side, drag ‘ResellerOrderSales’ to the ‘Values’ section .
8. On the ‘Details’ item under ‘Category Groups’, enter the following expression for the ‘Grouping’ expression:
=Fields!OrderYear.Value & Fields!OrderQtr.Value
9. On the ‘Details’ item under ‘Category Groups’, enter the following expression for the ‘Sorting’ expression:
=Fields!OrderYear.Value & Fields!OrderQtr.Value
The expression should appear as follows:
10. Change the colour of the sparkline to use ‘GrayScale’
11. Now, preview the report. It will appear as follows:
It’s clear that it’s impossible to compare properly, since the bars are not aligned with one another. To do this, we need to do some more work to ensure that the bars are aligned together for comparison purposes. Next, here are the steps to do this:
12. On the sparkline chart, right click and choose ‘Horizontal Axis’. A property dialog appears as follows:
13. In the section entitled ‘Axis Range and Interval’, click the checkbox next to ‘Align Axes in:’ and select the name of the Tablix. In this example, the Tablix is called ‘Tablix_CategoryResellerAndInternetOrderSales’. Instead of the axis being aligned to the category i.e. Accessories, Bikes and so on, this means that the axis is aligned to the grouping set up on the Tablix. In this case, our grouping is set up on the Order Years and Order Quarters. The outcome of this is that, if any of the columns are missing, there will simply be a gap where the data should be located. This helps the process of comparison, because if there is no way of identifying how the columns relate to one another, then the visualisation is misleading.
14. Click on the ‘OrderQtr’ group and look in the ‘Properties’ pane on the right hand side under ‘Group’, for the property item ‘Domain Scope’. Paste the name of the Tablix in this section. In this example, the Tablix is called ‘Tablix_CategoryResellerAndInternetOrderSales’. This window will now appear as follows:
15. The only issue is that the comparison may be made more difficult if the vertical axis is not aligned. To do this, right-click on the sparkline, and select ‘Vertical Axis Properties’. As before, under the ‘Axis Range and Interval’ section, tick the box next to ‘Align Axes in’ and choose the Tablix called ‘Tablix_CategoryResellerAndInternetOrderSales’.
16. Once you have made the right-most column invisible, the final report looks as follows:
It’s possible to see that the columns are aligned with each other horizontally and vertically. This allows us to see that, for example, ‘Helmets’ and ‘Bike Racks’ were the biggest sellers. On a more detailed level, we can see that ‘Locks’ and ‘Pumps’ did not sell at all in the last two quarters, but that ‘Hydration Packs’ and ‘Bike Racks’ did sell during that time.
With data that has ‘dates’ as a category, normally a line graph is appropriate. However, in this case, since there are ‘missing’ data for certain quarters, the lines do not appear properly. Here is the same example, completed using line graph sparklines:
Since some of the data is missing, it seems more difficult to compare the ‘points’ of the line graph. However, this seems easier with the bar chart, since the bars serve as distinct points, representing each quarter.
To summarise, this report would form part of a dashboard, and it’s not intended to replace detail. Instead, it is supposed to help the data consumer to formulate patterns in their head in order to help them to draw conclusions from the data.