The release of SQL Server Reporting Services 2008 R2 was accompanied with much fanfare regarding the new gauges that were available. This blog will look at different ways of implementing KPIs that do not require the gauges or indicators that are available. The reason for this is that, by default, the SSRS Indicator gauges do not always follow the best principles for data visualisation as expressed by experts such as Stephen Few or Edward Tufte. As a note, this blog was part of a SQLPass webinar given by the current author for the ‘Women In Technology’ SQLPass 24 hop webinar series, given in March 2011.
4. Now it is time to change the font to Wingdings. This will give us a simple square, rather than a trumpeting arrow as per the SSRS Indicator gauges. This can be seen in the following image:
In order to be clear, in SSRS, Indicators are minimal gauges that display the state of a single data value. It is intended that this information is taken ‘at-a-glance’. The icons that represent indicators and their states are simple, for example, traffic light indicators. They are used to display trends, states, ratings or conditions. In the SQL Server Performance Dashboard example we will look at, the Indicator is used to display ‘Wait Time’ for a SQL Server.
Before we dive into the implementation in SSRS, let’s take a look at the Indicator gauges. Here is an example of the indicators that are available:
Figure 1 Default KPI Indicators in SSRS
In terms of data visualisation, these traffic lights could be improved by taking on board the following points:
The Red-Yellow-Green colour system is not good for people that are colour blind. Approximately 12% of men, and 1% of women, are colour-blind (Few, 2008). Stephen Few’s rule number 8 specifies that ‘To guarantee that most people who are colour-blind can distinguish groups of data that are color-coded, avoid using a combination of red and green in the same display’. However, for people who are short-sighted with very strong glasses, it is possible that they can experience a strong effect in that off-axis viewing of objects away from the centre of the lens. Specifically, this can result in prisms, and the colours separate. This is known as chromatic aberration, and is the glasses wearer experiences a color fringing affect around strongly contrasting colours, which can be distracting for the eyeglass wearer. It may well be worth pointing out that near-sighted contact lenses wearers do not experience chromatic aberration because the contact lens moves with the cornea, thereby eliminating the chromatic effect.
Given that Red-Yellow-Green is a problematic combination, what can we do to resolve this issue? Stephen Few (2008) recommends using red and blue instead; this is perceptually distinct enough to carry the message of the data visualisation, whilst reducing the impact on colour-blind and very near-sighted individuals.
The current example will use the SQL Server Performance Dashboards to display indicator materials, and will only show Red if there is an issue; otherwise, it will not show anything. Instead of using the default Indicator gauges, it was decided to keep the visual representation as simple as possible. Thus, a small red square is used. Since the red square is only present if there is an issue, this can be viewed as reducing the chartjunk on the page since only essential items are shown. Further, by keeping the display to a minimum, the data/ink ratio is reduced.
In this article, we will improve one of the SQL Server Performance reports to show more visual information. The original Historical Waits Report looks like this:
Figure 2 Original Historical Waits report
One thing we can do to improve this report is to use colour in order to convey a message about the information. From the above report, we can see that the top ‘% Wait Time’ is set at 76.93%. It is possible to use some colour in order to convey the highest wait time, and this is the purpose of the current article.
For this article, the following is assumed:
The SQL Server Performance Dashboards have been downloaded from Microsoft Download Centre
The SQL Server Performance Dashboards script has executed successfully: in order to do this, the column ‘cpu_ticks_in_ms’ needs to be changed to ‘ms_ticks’. Intellisense will help you to identify the incorrect column.
1. Import the SQL Server Performance Dashboards into a SQL Server Project.
2. Open the ‘Historical Waits.rdl’ report and navigate to the column entitled ‘% Wait Time’. This is outlined in the blue box in the following diagram:
Figure 3 Sample Text Box for amendmen
3. Right-click the box and select ‘Text Box Properties’. We will need to do two things: we will change the value of the textbox from the expression to a ‘o’. Then, we will change font to WingDings in order to obtain a nice ‘o’ shape using the ‘o’ that we typed in first.
In order to change the value of the Textbox to a ‘o’, type ‘o’ in the Values box for the Text Box properties. This can be seen in the following diagram:
Figure 4 Changing the value of the Textbox
4. Now it is time to change the font to Wingdings. This will give us a simple square, rather than a trumpeting arrow as per the SSRS Indicator gauges. This can be seen in the following image:
Figure 5 Change Font to WingDings
Once this has been done, click ‘Ok’.
5. Now it is time to update the colour of the square indicator in order to reflect the status of the Wait Time. This rule specifies that, if the wait time is greater than or equal to 70%, then the textbox show a red square; otherwise, nothing is displayed. In order to ensure nothing is displayed for values that are less than 70%, the square is set to white; this means it will be invisible.
In order to update the colour, we will copy and paste the following formula into the ‘Color’ box of the ‘Properties’ window. If the ‘Properties’ window is not showing, click on the textbox, and then choose the ‘F4’ button. This will reveal a dialog box as follows:
Figure 6 KPI Textbox Properties Window
6. Choose the ‘Color’ item. Clear the contents of the expression editor window, and copy and paste the following in its place. When this is done, click OK.
=Switch(
(Sum(Fields!wait_time.Value) / sum(Fields!wait_time.Value, “DM_OS_WAIT_STATS”))
>= 0.7, “Red”,
(Sum(Fields!wait_time.Value) / sum(Fields!wait_time.Value, “DM_OS_WAIT_STATS”))
< 0.7,
“White”)
7.
Save and preview the report. The report should appear as follows:
Save and preview the report. The report should appear as follows:
Figure 7 Completed Report
It is now clear that, instead of percentages, there is a small indicator which specifies that the ‘Other’ Wait Category exceeds a specified criterion.
References:
Stephen Few (2008). Practical Rules for using Colour in Charts. Perceptual Edge, February 2008.
Hi Jen,
Alternately, you could use the KPI set 3rd from the left under “shapes” – it differentiates both by colour and by shape. This KPI set is specifically designed to overcome the problems you describe.
Cheers,
Alan
Hi Alan,
Thank you for reading the blog and getting to the end 😉
With these shapes you mention, it takes time to remove all the gradient, shading, border, change the colour etc; even though the shapes are customisable, they come with chartjunk. It does double-encode, and with the example I've given, you could also change the font size dependent on the criteria. I thought it would be more interesting to show a different way of implementing a KPI which was more in line with best practices rather than show people how to remove borders, shading etc. Thank you for reading it though; this stuff is all 'opinion' and the more I interact, the more I learn from comments from people like you. So thank you Alan 🙂
Jen
Hi Jen,
I like the post. Just want to point out that you can customize the indicators in SSRS to change the fill colors and other attributes. You can also remove the steps so if you just want a single blue indicator and no yellow or green conditions you can configure thatas well.
Regards,
Sean
Hi Jen,
Great post, I am just loving all your posts on data visualization. As for this technique, I had blogged on something similar last year – http://road-blogs.blogspot.com/2010/04/dashboards-in-ssrs-2008.html
I implemented it such that the size as well as the color changes, so that 2 measures can be analyzed at once.
Hello Jason,
Thank you for your post, which I didn't see previous to your comment.
Although I normally double-encode with colour and size, I didn't want to make this post longer than it already is 😉 So i left it although I have done this with other Tableau blogs which you can see in other parts of my blog. Further, in response to the comments made by 'Alan', you will notice that I have already suggested changing the size prior to your comments.
I notice in your blog that you have stuck to red-yellow-green but for the reasons I specify in this post e.g. colour blindness, colour fringing effects, red-yellow-green doesn't suit everybody. If, for example, 12% of males and 1% of females are colour-blind, then this unfortunately excludes a reasonable proportion of your audience. Dataviz is about trying to get the message across to as many people as possible, and colour is a very important aspect of making the visualisation as clear as possible, to as many people as possible, all at once. So this is the challenge!
Thank you for reading and I look forward to your comments in the future.
Regards,
Jen
Thanks for the advice Jen, will make a note of the pallete used 🙂 Is any of the default palette (other than Greyscale maybe) suitable for color-blind people?
Hi Jason,
Yes, I always use 'Color Brewer' to check the colours because I find it hard to tell what suits colour-blind and other factors e.g. photocopyable. Here is the link http://goo.gl/zEYqL You can also download an Excel spreadsheet which holds the RGB numbers, very useful, and I recommend that you do that, and note the ones you like best.
The palettes are Diverging e.g. red – white – blue (or red – yellow – green) as in your example, or Sequential e.g. light to dark blue or Categorical (perceptually distinct but toned) in order to display different categories of data. Hope that helps!