Drill Through Reports in SSRS

SSRS Drill Through Actions

In SQL Server Reporting Services, Drill Through Action is the most commonly used Action in real-time. This type of action allows the user to navigate from one report to another by clicking an object in main report.

TIP: Drill Through Action can be defined on Matrix, Table, Charts, Data bars, Pie Charts, gauges and so on

Drill Through Reports in SSRS 2014 Example

In this example we will show you, How to enable Drill Through action on Pie Chart with example. For this, we are going to use the below shown report. Please refer the SSRS Pie Chart article to understand the Data Source and Dataset we used in this report.

Our aim is to define Drill Through action on each country region. If the user click on any country region, second report has to display with the corresponding state wise sales. For instance, If user clicks on United Kingdom then second report has to display all the states present in that country.

NOTE: If you want to achieve the same, we have to pass country value from main report to second report. It means second report should be parametrize report.

Below screenshot shows you the second report and this is the report we are going to display on Users Drill through action. Please refer SSRS Data Bars article to understand the Data Source and Dataset we used in this report and we done some formatting as well.

If you observe closely, It’s just a normal report with Basic Parameter and its default value is Australia.

First, We are going to hide the Parameter because we are going to pass the parameter value from main report and it will be annoying to display the parameter in Drill Through reports.

To hide the parameter, Right click on the Parameter and select the Report Properties option. In the general tab, Please change the parameter visibility option from Visible to Hidden as shown below

Here we are going to apply Go to report action (Drill Through Action) on Countries column so, Right click on the Pie Chart and select the Series Properties.. option from the context menu as shown below

Once you select the Series Properties.. option, a new window will be opened to configure the properties. Please Navigate to Action tab and choose Go to report option as shown below.

Under the Specify a report option, Please select the required report from the drop down list as shown below

After selecting the required report, We have to assign the value(s) to parameters in selected report. First, click on the Add button to configure parameter values

Under the name section, Please select the Parameter name of the selected report

Next, We have to assign proper value to the selected Parameter. Here, ParaCountry Parameter is expecting country names so we selected the Country column from the main report.

NOTE: If you don’t find the exact column in the main report, Use the fx button to create one.

Click Ok to finish enabling Drill Through action on Pie Chart and let us see the report preview by clicking preview tab. If you observe the below screenshot, it looks same as normal pie chart but if you click on any region then second report will be displayed with the state details present in that country.

Here, We clicked on United States.

If you observe the below screenshot, our second report is displaying the state wise sales of United States in our second report

TIP: Please click on the Back button (Blue Arrow pointing to left) to navigate from second report to Main report

Let us add the total to second report so that, you can compare the totals displayed in Pie Chart and second report.

This time, We clicked on Canada

As you can observe from the above screenshot, totals are perfectly matching.

Thank You for Visiting Our Blog