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 the 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 Example
In this example we will show you, How to enable Drill Through action on Pie Chart with an 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 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 display all the states present in that country.
NOTE: If you want to achieve the same, we have to pass country value from the main report to the second report. It means the second report should be parametrized 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 have done some formatting as well.
If you observe closely, It’s just a normal report with Basic Parameter and its default value in Australia.
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 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 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 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 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.
Here, 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.
Thank You for Visiting Our Blog
