KPI Report with Reporting Services 2008 R2

There is a new gem in SQL Server 2008 R2: Indicator control. It enables us to create KPI style report in easy way. You may ask how is it difference with Gauges that already exists in SQL Server 2008?

Well, Gauges is cool by displaying speedometer style indicator. But sometime they are just too much. I don’t want the fancy circle style, instead I want it to be a simple traffic light or dot indicator like this:

image

If you are a Reporting Services veteran then you know what to do: upload couple of traffic light symbols and bind them to a cell. Then write an expression to hide or unhide each symbol accordingly. We don’t need to do this anymore in R2. Here is the way to achieve it in easy way.

First, as usual we need to provide a cell as placeholder for this indicator. Second, place indicator control inside the cell then bind them to corresponding columns in the dataset.

image

The last step is configuring the expression logic for them. By default is only showing 3 state: Red, Yellow, and Green. But we can add as many states according to requirement. Once its done, just render the page to show KPI report.

Here is my sample KPI report. It requires Reporting Services 2008 R2 and AdventureWorksDW2008 database.

The same concept also applied to display trend in R2. I posted about creating trend sparklines in SSRS 2008, which is require to do a bit workaround. Not in R2 version, because It has dedicated sparklines control as well.