Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSRS – KPIs without Analysis Services

During a recent project I was asked if it was possible to simulate Key Performance Indicator (KPI) images in SQL Server Reporting Services (SSRS) without Analysis Services. Off the top of my head I thought of a couple of things, but I decided to do a little searching before I embarked on my latest challenge. After one quick search I found a great video posted on SQL Share by SQL Server MVP, Jessica Moss, Designing a KPI in Reporting Services.

Using her example, with a few modifications I was able to produce exactly what the client needed. See the following screenshot for an example:

clip_image002

So how did I do it?

To start, I had a quick meeting with the client to determine the calculation for the GOAL of the KPI. It was simply a percentage based calculation between current YTD sales and the prior YTD sales ((CurrentYTD/PriorYTD)*100). The value indicators would be determined by the results of the calculations. As seen in the above screenshot, if the calculation was greater than 100 a green arrow should be shown, if it was greater than or equal to 90 and less than 100 a yellow arrow should be show and if the value was less than 90 a red arrow should be shown.

After all the requirements were gathered, the next step was to develop a method to dynamically show the arrows based on the KPI Range Values. First I created a calculated column that used the aforementioned calculation. To do this, add a calculated field to your existing dataset by right-clicking the dataset and select Add Calculated Field. The Dataset Properties window will appear:

clip_image004

Click the Add button and insert your calculation. You can also return the calculation as part of your result set and ignore this step.

Next add a column to an existing Table item on the report. Then add an Image into the column. See the right-most column in the following screenshot:

clip_image006

Next, right-click the image and select Image Properties and the following window will appear:

clip_image008

=SWITCH

(

Fields!KPI.Value > 100, "GreenUpArrow",

Fields!KPI.Value >= 90 AND Fields!KPI.Value < 100, "YellowAngleArrow",

Fields!KPI.Value < 90, "RedDownArrow"

)

Then click the expression button clip_image010 next to the Use this image drop down list. In the expression textbox enter the following or something similar to the following:

 

The SWITCH function will allow you to specify the image that should be displayed based on your KPI values. If you are not familiar with the SWITCH function read my blog posting, Reporting Services SWITCH Function , for more details. In the function I specify the image to display based on the calculated KPI Value.

Next click on the Size in the right pane of the Image Properties box:

clip_image012

Ensure that the radio button labeled Clip is selected and you can also adjust the padding on the images just in case you want to change the alignment of the image. Click ok and run your report. You now have KPI type images in your Reporting Services report.

Downloads

KPIExample

AdventureWorks2008DW

As always, if you have any questions, comments or concerns regarding this posting please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Comments

Posted by Gaurav.Vinodkumar on 9 March 2010

Very nice article...thanks

Posted by prashantlage on 9 March 2010

nice article

Posted by nancy.dandridge on 9 March 2010

Thanks! Good article. My company is heading down the KPI path and this will come in handy.

Posted by mtillman on 9 March 2010

I can't seem to find out how to " add an Image into the column."  In case this helps, I'm using VS 2008, Reporting Services Designers Version 10.0.2531.0.

Posted by Patrick LeBlanc on 9 March 2010

mtillman:  You must first add the images to your report project.  In the Report Data tab, right-click the images folder and select add image.  You then choose your images.  Once complete you can drag the image into a column.

Posted by mtillman on 9 March 2010

'Got it to work!  Thank you.  

Another aspect of this that made me pause was that in the expression for the image in the column, you can't just pick the field by choosing "Fields" and having them display for you.  It says "Report itme not linked to a dataset."  I wish it wouldn't do that...

Posted by Jimmy Ledbetter on 12 March 2010

Is there a way to do this on SQL 2005?

Posted by Patrick LeBlanc on 19 March 2010

Jimmy sorry for the delayed response.  I am in the process of validating the process on SSRS 2005, but I think it can be replicated.

Posted by Jimmy on 3 January 2011

How do I get Images there like green, yellow, red arrows?

Posted by Jimmy on 3 January 2011

Got it, thanks

Leave a Comment

Please register or log in to leave a comment.