In this walkthrough, we’ll take look at using the Indicator visualization in SSRS. Indicators are a great tool to have at your disposal, particularly if you’re creating “dashboard” reports. The general idea of a dashboard is to give the user a high level overview of how areas of interest area performing, and indicators are a really great visual aid to help users quickly identify the current performance of a metric. Let’s take a look at setting up a simple indicator in SSRS report builder. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)
- Open Report Builder, and create a new blank report
- Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
- Create a data set in report builder and paste in the following SQL into the query window:
SELECT 'Category A' 'Category', 33 'Value' UNION SELECT 'Category B', 66 UNION SELECT 'Category C', 99 UNION SELECT 'Category D', 20
- Add a table to the body of your report
- Drag “Category” and “Value” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below
- Back in the Report Builder design view, navigate to the insert tab, and select the Indicator Visualization
- Place the Indicator into the 3rd column of your table. This should bring up a new window that allows you to choose from a variety of different visualizations. In this example we’re going to use the classic red, yellow, green circles from the “Shapes” category. Make this selection (shown below) and then click “OK”
- Left click twice into the table cell that contains the Indicator to select it. The “Gauge Data” window should appear once it’s been selected
- Click the “Gauge Properties” button on the “Gauge Data” window.
- Click on the “Values and States” menu option
- In the dropdown labeled “Value” select [Sum(Value)] from the available options
- In the dropdown labeled “States Measurement Unit” select “Numeric” from the available options
- Click “OK”
- Preview the report.
At this point your table should look like this:
Note that if we look back to our “Indicator Properties” screen and compare the “Start” and “End” values (often called thresholds) for each differently colored indicator, we can see that the red indicator has an end value of 33. Since “Category A” falls into the range of 0-33 it’s marked with a red indicator in our table. One important thing to note about the list of start/end values for each indicator listed on the “Indicator Properties” screen is that they’re evaluated in order from top to bottom, so the order that they appear in is important. If there happen to be ranges that overlap, SSRS will only look as far as the first matching range and then look no further (sort of similar to a switch statement). To further illustrate this point, we could set the “Start” value to zero for all three indicator colors in the properties window and our table would look exactly the same when the report is run. The order of the indicators can be easily modified by simply using the up and down arrows found on the “Indicator Properties” screen.
The “Indicator Properties” screen also allows you to modify just about everything else you might want to modify about your indicator states, such as the color and shape that is presented. If you’re feeling really feisty, you can even add additional indicator states by simply clicking the add button. Who knows, maybe a blue sad face emoticon will be a really useful range/state to add. While you can go kind of crazy with the colors and shapes if you want, make sure to keep the overall goal of your report in mind. Indicators are really good at enabling users to quickly identify good/mediocre/poor performing areas “at a glance”, but you need to keep the number of states low. Also red/yellow/green are pretty much universally accepted as bad/ok/good. Creating an indicator that has 20 states and every color under the rainbow will probably be pretty useless to the report user.
I think that about wraps up our intro to indicators, hopefully you found it useful. Until next time, happy reporting!