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.
