SQL Server Reporting Services Cross Chart Filtering


By:   |   Updated: 2016-10-20   |   Comments (2)   |   Related: > Reporting Services Charts

Problem

A dashboard is full of charts presenting various visual informative data. SQL Server Reporting Services (SSRS) provides many components to build interactive charts, but the challenge arises when one wants to filter the entire set of charts in the dashboard by selecting any one data point on a chart and reflect the selection in the rest of the charts. In a nutshell, cross chart filtering is a challenge to implement in SSRS reports, but in this tip we cover how this can be done.

Solution

Charts used in SSRS reports have an option to configure a Report Action. Using this option and some logic, we can configure the charts to select a data point as filter criteria which in turn can update multiple charts based on this selection.

Building a SSRS Cross Chart Example

To demonstrate SSRS cross chart filtering, we have a supplier products review table where each supplier’s product review is captured at certain intervals.

Suppliers Table

We have to perform the following steps to apply cross chart filtering:

  • First Step: Create a dataset in an SSRS report to fetch he data from the Supplier Table.
  • Second Step: Create a parameter which will hold the selected value of the Supplier Name.
  • Third Step: Create a few charts to show different data.
  • Fourth Step: Configure the series "Action" of a chart to get Supplier Name (data point) from a parameter.
  • Fifth Step: Highlight and filter the selected data point in the rest of the charts.

Step 1: SQL Server Reporting Services Dataset

Create a dataset to fetch data from the Supplier table as shown in the image below.

SQL Server Reporting Services Dataset Properties

Step 2: Create an SSRS Parameter for the Supplier Name

Create a parameter named "SupplierSelect" as shown below. This parameter will store the selected supplier name on the charts.

SupplierSelect Parameter in SSRS

Step 3: Build SSRS Charts

For this example, we will build three charts using the same dataset:

  • First chart Final Review shows the supplier product review score in a stacked column chart type for each supplier and product.
  • Second chart Monthly Review shows the scoring for each supplier based on product and review period.
  • Third chart Product Review shows information about a single supplier for all products and review periods.

Here is an example output of the three charts:

SSRS Dashboard with the Supplier Review Report

Step 4: Configure an SSRS Series Chart

To configure a series in a chart, right click on the chart series and select "Series Properties" > "Action". Under "Enable as an action" select "Go to report". Select the report from the dropdown. Also select the parameter and field as "SupplierSelect" and "Supplier Name" just like shown in the image below. This needs to be done for the Final Review and the Monthly Review charts.

Configure Action in the SSRS Series Chart

Step 5: Select the Data Point and Expression

To highlight the selected data point ("Supplier Name"), right click on the chart series and select "Series Properties" > "Fill". Select "Fill Style" as "Solid" and in the color enter the below expression.  This expression decides which supplier to highlight in this color.  This needs to be done for all three charts.

=IIf(Fields!Supplier_Name.Value = Parameters!SupplierSelect.Value,"#db73f9","#00000000")

Select the Data Point and Expression

To filter with the selected data point ("Supplier Name"), click on chart "Series Groups" > "Supplier Name" and select "Series Group Properties". Select the "Filters" tab and add an expression to match the "Supplier Name" field with the "SupplierSelect" parameter as shown below. This will automatically filter the chart "Product Review" on a click of any data point in the chart.

Expression to match the Supplier Name with the SupplierSelect parameter

Running the SSRS Report

If we click on Supplier3 in the Final Review chart you will see the other charts highlighted with the color we used in the above Expression.  Also, the "Product Review" chart will filter based on the Supplier that was clicked on.

Final Report with Supplier3 selected

If we click on Supplier2 the charts will change accordingly.

Final Report with Supplier2 selected

Conclusion

In this way SSRS reports can have cross-chart filtering and highlighting based on what is clicked on in the report.

Next Steps


Last Updated: 2016-10-20


get scripts

next tip button



About the author
MSSQLTips author Rahul Mehta Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, May 22, 2019 - 3:12:55 PM - Kendall Irwin Back To Top

This is helping our agency immensely in displaying our relevant data!

I have one issue, however. How do you get all values to show in the "Product Review" chart, as you have displayed here? I have a similar chart filtering by area names, but it only shows me one area at a time in that chart.

Otherwise, this was very in-depth and accomplishes our goals on displaying data in a friendly manner.


Thursday, October 20, 2016 - 11:48:48 AM - vinod kumar Back To Top

 I sir thanks for you're helping nature .,..

I have one question sir ...

How to remove password on SQL server report password ,

Its taken server password I what open with out password on report. Pls.. Tell me solution.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools