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.

vs-version1

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.

matrix1

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.

del-row-group1

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

del-row-group2

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

add-child-group1

Select Show detail data and click OK.

add-child-group2

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.

del-row-1

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

del-row2

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

horizontal-table-preview1

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.

add-indicator1

add-indicator2

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.

add-indicator3

add-indicator4

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.

add-indicator4a

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

add-indicator5

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.

add-indicator9

Next, right-click on the label and select Label Properties to configure the field setting.

add-indicator6

In the Text section, input the [Town] field. You can also use the fx button to the right for dynamic labeling.

add-indicator7

Congratulations! You are done.

You have created a horizontal table of indicators, letting you know at-a-glance which town is the best.

final-preview

 

This article has 1 comment

  1. […] Derik Hammer shows how to display a set of indicators horizontally instead of vertically in a Report…: […]

Leave a Reply

%d bloggers like this: