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:
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:
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:
Next, right-click the image and select Image Properties and the following window will appear:
|
Then click the expression button 
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:
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
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.
