Improve Data Visualization in Charts: Part 2

I recently heard a webinar that described Reporting Services reports as “static”. Not true. Reporting Services can be made to be very interactive, you just have to get a little creative. The technique I’m about to describe I like to call the “1-Click Highlight & Filter.” I believe this technique will give you, as report developers and BI consultants, an edge for creating interactive reports using SSRS.

Overview
We’ll use parameters to hold user-selected values (either touched or clicked) on a chart, a table, a point in a map, or any other actionable report item in SSRS. The key to this whole thing is report object actions which you configure to point to the report itself. We can then influence anything on the report with SSRS Expressions. Background colors, borders, font styles, object sizes… Interactivity is an essential characteristics to good reporting, especially on mobile reporting on mobile devices such as iPad.

Let me show you this technique through an example using a column chart. We’ll start by making the chart’s bars highlight when a user selects one of the bars and filter another report object, in this case another table. This is a simple visualization technique to help a reader learn more about their data and work less. Some may argue this is more work for something so simple, but its worth it if your end users take advantage of this data visualization.

The initial chart
Start with the following dataset:

Figure 1. Initial Dataset

Drop a chart onto the report design surface and configure it with the following:

• Size the chart to Width = 6in and Height = 3in. These properties are available in the Properties pane of BIDS.
• Pick column chart and delete the Axis Titles and Legend
• Put the Product_Category_Name field as the Category Group of the Chart Data Pane
• Add Sales_Amount in the Values section of the Chart Data Pane.
• (Optional) Right-click on the Vertical Axis and go to its properties to change the Number format to show in millions with 0 decimal places. With millions, I like to format the $ after the number and go into the LabelsFormat properties and switch out the ‘$’ for ‘M’. Resulting format code in the properties pane is shown as #,0,,’M’;(#,0,,’M’)
• (Optional) Right-click on the chart title and hit the function button next to the Title text and type in the following SSRS Expression: 

The resulting chart and chart data pane will look like so:

Figure 2. Initial Chart in Design View with Chart Data pane displayed.

Add a Parameter called @ProductCat which . Make it a hidden parameter and leave the Data Type as Text. In the Default Values tab, hit the Add button and type the value ALL without quotes. You’ll see later that this default value will be used in the SSRS Expression to display all the data on the report.

Figure 3. @ProductCat Parameter Properties – General

With the report still in Design mode, click one of the bars in the chart to select the series. The series supports report actions to jump to a different URL, a bookmark within the report, or another report altogether in the SSRS Project. In our case, we will jump to the report itself. So in the Series Properties dialog box, click on the Action tab. This is where the magic happens.

Figure 4. Series Properties – Actions

In the Acton properties, change the radio button to Go to Report. Under Specify a report, pick the report itself from the drop-down. Under Use these parameters to run the report, hit the Add button and select DropCalendarYear from the drop down. Hit the function button next to the Value drop-down, and type in the following SSRS Expression: =Parameters!DateCalendarYear.Value(0). Hit the Add button again and pick the ProductCat parameter from the drop-down. This time choose Product_Category_Name in the Values column from the drop-down. Click OK on the dialog.

Now the last part of the visualization: adding the SSRS Expression to the Series color to bring focus to the Product Category selected (either by touch or mouse click). Locate the Color property in the Properties pane. Click on its drop down and choose . For the expression, you will write:

=IIF(Fields!Product_Category.Value=Parameters!Product_Category.Value OR Parameters!Product_Category.Value=”ALL”, “DodgerBlue”, “LightSkyBlue”)

Now you’re ready to test it out. Preview the report and you should see all the columns the same Dodger Blue. Select the column of one of the Product Categories, and you’ll see all the other columns turn to “LightSkyBlue”.

Figure 5. Resulting 1-Click highlight on a column chart

To improve the visualization, I’ve added a black 1.5pt-thick border to the series to appear when the user selects a particular Product Category. To do this add the following SSRS Expressions to the following Series properties:

• BorderColor: =IIF(Parameters!ProductCat.Value = Fields!Product_Category_Name.Value, “Black”, “Automatic”)
• BorderStyle: =IIF(Parameters!ProductCat.Value = Fields!Product_Category_Name.Value, “Solid”, “None”)
• Width: 1.5pt

We can use this same Series Action to filter another data region on the report. Drop a table onto the report design surface just below the chart and drag-n-drop the Product_Category and Sales_Amount fields from DataSet1 as columns in the table. You’ll get the resulting table:

Figure 6. Initial Table using the same dataset as Chart

Now click on the Tablix and locate the Filters property in the Property pane. Click on the ellipses button and you the resulting dialog appears:

Figure 7. Tablix Properties – Filters

On the resulting dialog for Filters, click on the Add button and pick [Product_Category_Name] in the Expression field. In the Operator field, pick Like and then hit the function button in the Value field. Type the following SSRS Expression:

=IIF(Fields!Product_Category.Value=Parameters!Product_Category.Value, Parameters!Product_Category.Value, “*”)

What this is doing is Filtering the tablix if one of the Product Categories matches the Parameter @ProductCat. If not, then it returns all the Product Categories. Note that filtering in SSRS, and asterisk ‘*’ is a wild card character like % is in T-SQL.

When previewing the report and selecting a Product Category in the chart at top, you’ll see the tablix beneath it filter for the same Product Category. In real life, you’ll likely have more columns in the tablix to show better detail that tells more of a story to the end user.

Figure 8. Chart Highlight and Tablix Filter results

So I hope this helps you create better data visualizations through the use of Report Actions. Later, I’ll blog about how to do this technique with Maps (Point Properties) and other types of Report objects. If you’d like to see how this looks on iPad, go out and download the evaluation app Mobi SSRS. Feel free to add comments on this post if you have questions or would like to see any other examples. Thanks for reading!