SSRS KPI Indicators for non-numerical values

Today, I would like to share a quick and easy solution to display Traffic-Lights indicator for statuses in a workflow process. I recently implemented a workflow-driven report using SSRS which allows users to mark the receipt of documents and to further update the status of documents as “Signed Off”, “Submitted” etc. The requirement was purely to provide a workflow-driven report to the users. I took advantage of SSRS’ native KPI indicators to include a traffic-lights indicator to show the status graphically as it changes. This proved to be a quick win for my client users.

To start with, I designed the following report with link buttons for users to update the status. These links point to the same report with a dataset calling a stored procedure to update the status. Since this post focusses on the indicators, I shall stick to doing just that.

Image

I have an record for a given document and it is marked as “Received” by default. (Notice the yellow colour traffic light indicator). The user can change the status to “Current” which then turns the colour of the indicator to green as shown below.

Image

Lets take a closer look at the settings to achieve this.

Right click on the report body and select Indicator from the list of available report items.

Image

From the available Indicator types, select the “rimmed traffic lights indicator” and place it on the report. After resizing and adjusting the position of the indicator, right click on the indicator and select Indicator Properties.

Image

In the Indicator Properties dialog, select the Values and Scales tab and notice the preset numerical values for each colour. These range values can be modified to ranges that suit your needs. As discussed earlier, my requirement was to change the colour depending on a string field value which holds the status as “Received“, “Current”, “Submitted” or “Cancelled“. The value expression comes in handy to set numerical values to each of the string statuses.

I assigned a numerical value to each of the string values in my status field. The indicator then uses the corresponsing numerical value to change the colour based on the defined range.

From the expression above, if the status is set to “Cancelled” a zero value is returned which falls in red colour’s range. Thus, the report then displays the red coloured indicator. While this is a simple solution to use non-numerical KPIs to display indicators on your report, more advanced indicators can be implemented along the same lines.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s