SQL Server Reporting Services Cross Chart Filtering

By:   |   Comments (2)   |   Related: > Reporting Services Charts


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.


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


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

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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 (43604)

 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.

get free sql tips
agree to terms