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

Hide-and-Seek with expressions in SSRS Reports

This will be a quickie as I’m posting from my tablet for the first time on this site and it is just before going to bed. It’s about an issue I came across with an SSRS report this evening. It was a perfectly running report in production for over a year. The report’s layout has a dashboard-like feel with a summarised tablix of budgets and expenses representing my client’s business expenditure. The highlight of the summary was a Gauge Panel to show graphical representation of the data in every row. I had to modify the dataset dsExpenses ( which contained the results of an MDX query) to reflect the changes I had made to the cube from which the report was generated. I sucessfully ran the new dataset’s MDX query from the dataset’s query designer and changed all references in the report’s tablix cells to use the new fields from the newly modified dataset dsExpenses. One of the fields that was changed in dsExpenses was Payment_Amount which is now referred to as Payment_Amount_Programs.

I changed all references in the summary tablix from Payment_Amount to Payment_Amount_Programs manually by checking on each cell’s properties. I avoided modifying the report’s code as there were other tables that had other datasets with the same field name Payment_Amount. After carefully checking on each cell’s properties and values, I ran a quick build of the report and to my surprise the Gauge Panel’s background colour expression was thrown on the error report as it still had references to the old Payment_Amount. I knew, for sure, that I had checked the background colour expression for any outdated references. But when I checked the report’s xml code, I found that there was an expression hiding under the gauge data series’ back colour. I changed the expression to refer to Payment_Amount_Programs instead of Payment_Amount and the report ran successfully. This was a good lesson learnt the hard way to always check for expressions hiding in the code holding old references after a dataset is modified.

Posted to The No-Brainer Lab using Android