Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSRS 2008 R2 KPIs with bullet graphs


By:   |   Last Updated: 2011-06-10   |   Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services KPI

Problem

Key Performance Indicators are typically displayed in a scorecard with stop light indicators, which are either red, amber or green light icons. The limitation for these kind of indicators is that you can see the actual and target values in two different fields as well as see the status of the KPI in red, amber or green color. If the user wants to figure out the thresholds associated with the KPI, these values are generally not visible. Further, representing the threshold values in the scorecard itself defeats the purpose of the scorecard. The scorecard should display the KPI's status in the most summarized form and use a minimal amount of space on the dashboard. In this tip we would look at how to address this issue.

Solution
Before we start addressing the issue, it would be helpful to take a view of what we are trying to address. The image below shows a typical PerformancePoint scorecard. Here the user can easily see the actual and target values, but not thresholds associated with it. If the thresholds are displayed in the form of additional fields, this would consume more space on the dashboard. Another option is to display the values in tooltips, which is not an efficient solution and it might not be even acceptable to the users.

looking at kpi on a performance point scorecard

The solution to this problem is to use bullet graphs, which is a linear gauge representation of the data available in SSRS 2008 R2. Let's follow the steps below to create a sample bullet graph, and once it is generated, compare it with the above scorecard to evaluate how bullet graphs solve the problem as opposed to stop light indicators.

Step 1: Create a sample dataset as shown in the screenshot below. KPIs generally have actual and target values as well as thresholds associated with them which are divided into red, amber and green status. This dataset is a typical example of a few KPIs that are tracked for project management purposes.

creating a bullet graph in ssrs 2008 r2

Step 2: Create a new SSRS report, and configure it to use the above data in a dataset. Right-click on the report area, select Insert -> Gauge -> Bullet Graph (from the Linear section). Right-click on the gauge, select gauge properties and set it's Frame style to "None" as shown in the screen shot below, so that we can fit the gauge in other containers smoothly.

create a new ssrs report

Step 3: Select this gauge, and set it's DataSetName property to the dataset created in Step 2.

Step 4: Right-click on the gauge and you would be able to see the GaugePanel properties as shown in the screen shot below. One fundamental thing to keep in mind before configuring this gauge is that there are two pointers (actual and target) and three scale (red, amber and green) by default. The first linear pointer, which is a horizontal bar, is usually used for the actual value and second linear pointer, which is a vertical bar, is usually used for the target values. Linear Range 1, 2, and 3 can be mapped to red, amber and green thresholds respectively.

gauge panal properties

Step 5: Select LinearPointer1 properties and set it to the value of the sum of the actual as shown in the below screenshot. Do not get confused with the Sum function, this will get clearer when we place the gauge in a container. Set the pointer fill color to Solid style and Black color. Repeat the same steps for LinearPointer2 and set the value to Target instead of Actual, Color style to Solid and Color to Red.

select linear pointer properties

Step 6: Select LinearRange1 properties and set the "End range at scale value" property to sum of Red as shown in the screen shot below. This means that the first scale range would start with zero and end with the value of the Red field. Change the Fill style to Solid and color to Tomato Red. Accordingly change the start and end ranges, as well as respective colors for LinearRange2 and LinearRange3.

linear scale range properties

Step 7: Add a table to the report, and delete the third column. Right-click the detail row in the table, and add a group based on the KPI field as shown in the figure below. The reason for this is so that we would add the gauge to the detail area and the gauge in each row would have access to values of a single KPI as we are grouping by the same. Add KPI field to the first column and the gauge to the second column.

add kpi field to the first column

Step 8: After you are done , preview / execute the report and you should find a result similar to the screen shot below. Your font, color, border size, etc. may be a bit smaller or bigger than the screen shot below. Try tuning different UI related properties and you should be able to adjust your result similar to the one shown below.

preview / execute the report

Compare the result with the values we have in the dataset, then compare this result with the scorecard with the stop light indicators. You would be able to see how the user can determine the actual, target, and threshold values in the same graph. In general, dashboards do not require exact KPI values, but every piece of information in the dashboard needs to be as concise as possible. In my opinion, bullet graphs solves the problem in question and fits smoothly into the theme of a dashboard.
Next Steps


Last Updated: 2011-06-10


next webcast button


next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, March 15, 2017 - 10:25:30 AM - Srividhya Back To Top

 Can you please explain a bit more about Step 7 'Adding a table to the report'?

 

Thanks,

Srividhya

 

 


Friday, March 07, 2014 - 8:07:51 AM - Neelam Back To Top

Good Article


Learn more about SQL Server tools