Wednesday, October 23, 2013

SSRS 2012 Custom Indicator

In this demonstration, I will show how I used custom indicator graphics to create this report.

I'm using SSRS 2012 in SharePoint 2010, however, I believe the content will work with 2008 R2 and above versions of Reporting Services either in native or SharePoint mode.

Background Story

A week or two ago, I was given the assignment to use SSRS to mimic a report the executive management team used in Roambi.  Roambi is a mobile app that displays data in very rich, graphical ways.  All the executives use this report on their iPads and they love it.  Since Roambi only has a mobile platform, the executives cannot access the report on their desktops—hence the reason for me to mimic the report in SSRS.

The Unique Chart Image

Roambi has many more chart types in its interface than SSRS.  One that was used heavily in this report was the horizontal bar chart with a vertical goal.  The images below show what the charts look like.
75% of goal
125% of goal

These two images represent two different states.  The one on the left shows a value that is under target.  The one on the right is slightly above target.  These graphs visually display one metric compared to a goal or target value (think sales vs a sales goal).  If the metric is under the goal, the bar is displayed in red, and if it is at or above the goal, then the bar is green. The closest thing SSRS has to this chart type is a bar chart—however, there is no way (at least one that I could see) to display the vertical goal line, and change its bar color based on a calculation.  The SSRS Bar chart is simply a column chart flipped horizontally.

Realizing that the report will require custom graphics, I went to work using GIMP to create the following images to represent the various scenarios/states (creating these images is a tutorial in an of itself, but here is a link to a great article that helped me in creating these).  By the way, if you like my graphics, you're welcome to download them and use them yourself--or if you want the entire GIMP file that I used to create them, send me an email and I'll be happy to send you my project file.

I created images to map the range from 0-300+% of the goal.  I added the images to my SSRS project. The images folder in my SSRS project looks like this (there are more images in my folder than just these custom graphics).  I labeled all of the custom graph images as "BarToGoal_".

Add the Indicator Control Type

In the cell that I wanted to display the graph, I selected the indicator control type from the report toolbox or from the ribbon if using Report Builder 3.0

Visual Studio--Tool box

Report Builder 3.0

Configure the Indicator

Once the Indicator is added, it pops up a window to select an indicator type.  It doesn't matter which one is chosen since it will be customized.  For this example, I selected the "5 Quarters" one.

I open up the configuration options by right clicking on the indicator and selecting "Indicator Properties".

When the Indicator properties window opens, I select the "Values and States" option from the left-hand menu of the properties window.

Set the Value

In my example I want to compare one field to another.  I open up the value box and type in my expression. A simple example could be something like =Fields!SalesDollars.Value/Fields!SalesGoal.Value

Set the Measurement Unit

I change the "States Measurement Unit" drop down to "Numeric".

At this point, my properties window looks like this:

Set the Image

In this example, the indicator has 5 defaulted images and values to use. I want to override those with my own images and values.  I click the drop down in the Icon column and select "Image".

The image source is "Embedded" and the name of the image I want is BarToGoal_10.  I set the Start value to 0.0 and the End value to 0.10. What this means is that if the value of my metric is between 0 and 10% of the goal, then this image will be displayed.

I continue to configure the remaining indicator states, adding additional ones as needed.

Note:  I made sure to include an indicator for all values ranges.  If the value evaluates to something not within a specified indicator Start and End value, no indicator will display.

That is how to configure custom indicator graphics in an SSRS 2012 report.  You may also find Microsoft's documentation helpful.

No comments:

Post a Comment