Rotating Tables with Indicators in SSRS 2016

When creating SSRS (SQL Server Reporting Services) dashboards, I like to use indicators to give clear and colorful data visualizations. In this post I am going to cover the use of indicators and demonstrate how to rotate your tables so that you can display the indicators horizontally. The method in this post has been tested on SSRS 2016 RTM with Visual Studio 2015 and SSDT v14.

Rotating your table

Start off by creating an empty report. Do not bother with the report wizard builder for this. Once created, add an SSRS Matrix object to your report.

Below the report you should see two sections, one for your Row Groups and one for your Column Groups. In order to rotate your table you need to delete the details out of the Row Group section and add it as a Child Group in the Column Group.

Go ahead and delete the related rows and columns as well.

To add the Child Group, right-click the ColumnGroup and select Add Group > Child Group.

Select Show detail data and click OK.

At this point you have rotated your table. As you can see below, we end up with an extra row for some reason and trying to delete the bottom row does not work.

Instead, you need to move the fields down and delete the top row.

With that pesky row removed. You can see the results of your work.

Using indicators

If you are looking for a tabular report you can stop here. This does not look very nice on a dashboard, however. Now I will show you how to convert this into a grouping of colorful indicators.

Remove the [Rating] field from the bottom row and create an indicator by right-clicking on the placeholder and selecting Insert > Indicator. Then select the type you would like.

A gauge panel will be created with an indicator within it. In this demonstration we are looking to use the town rating as a singleton value and do not expect there to be multiple rows because we rotated the table. To do this, you need to edit the field in the expression editor and remove the SUM() function.

Now that the field has been selected, you should customize the colors and indicator levels to suit your needs and data set. I typically change the States Measurement Unit to Numeric and then either hard-code or create expressions to dynamically derive the indicator state thresholds.

Now that your indicator icon is configured, it is time to label the dots. Right-click on the indicator and select Add Label.

I find that the label is positioned poorly and too small by default. Adjusting the label is more challenging through the designer than I would like. Instead, modify the Height, Width, Top, and Left properties to get the label centered over your indicator and appropriately sized.