Drill Through Reports in SSRS

The SSRS Drill Through Action is the most commonly used Action in real-time. The SSRS Drill through reports allows the user to navigate from one report to another by clicking an object in the main report. SSRS Drill Through Action can define on Matrix, Table, Charts, Data bars, Pie Charts, gauges and so on

Drill Through Reports in SSRS Example

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

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

NOTE: If you want to achieve this Drill through in SSRS report, we have to pass country value from the main report to the second report. It means the second report should be parametrized report.

The below screenshot shows you the second report, and this is the report we are going to display on Users Drill through action. Please refer Data Bars article to understand the Data Source and Dataset we used in this report, and we have done some formatting as well. If you observe, It’s just an ordinary report with Basic Parameter and its default value in Australia.

SSRS Drill Through Reports Action

To enable the SSRS drill through Action on report parameters, first, We are going to hide the Parameter. Because we are going to pass the parameter value from the 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

Here we are going to apply Go to report Action (SSRS Drill Through Report Action) on Countries column. So, Right-click on the Pie Chart and select the Series Properties.. option from the context menu

Once you choose the Series Properties.. option, a new window will open to configure the properties. Please Navigate to Action tab, and choose Go to report option to configure SSRS Drill Through report action. 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 a 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 chose 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 the preview tab. If you observe the below screenshot, it looks the same as a normal pie chart. However, if you click on any region, the second report will be displayed with the state details present in that country. In SSRS Drill Through report preview, We clicked on the 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 the second report to the Main report

Let us add the total to the second report. This way, 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.