Drill Through Report - SSRS 2012

In this post, I am going to explain about the Drill Through report, with example, for that I am going to use “AdventureWorksDW2012”database.In here the main report will be display the Sales Amount based on the product Category, when click on the particular category it will drill through and display the Sub product Category wise Sales Amount and same as when click on Sub product Category report will be display Product wise sales amount. In Drill Down report, there is a one report, but in Drill-Through report there will be sub report per the drill level.

The data set query that I am going to create the parent report as below:

Add three reports as below:

Edit the Category report and make display Sales Amount group by category and make it Sales Amount as Sum.

Report will be display the preview likes below:

Edit the Sub category Report:

Here we can see that parameter is created:

Now we have to make this parameter value dynamically pass from the category report, for that in the Category report, click on the Category Field, and right click and go the action. And the select Go to report, and select the sub category report under Specify a report, then add the parameter under name all the parameter under sub category report will be display for that  select CategoryKey and for the values select CategoryKey.

If you preview the report you can see the preview will display Hand Tool above the category name. If you click the report one of the category, it will display the sub category report for the only particular category.

For more formatting:

• Hide the sub report parameter: Click on the parameter and Hide.

• To make it hyperlink visible to the user make it Blue color, for the category field format as font color as blue and underline.

Do the same steps to Sub Category report to Product report.

Cheers!