http://www.sqlservercentral.com/blogs/samvangassql/2011/12/06/create-drillthrough-reports/

Printed 2014/04/20 05:06PM

Create Drillthrough Reports

2011/12/06

Use Actions!

Your user wants a summary report, containing sales for calendar year, for example. When the user clicks on the report, another detail report should be visible, containing sales for each month of the year, suppose. These type of reports are called drillthrough reports. How a drillthrough report is different from a drilldown report is for some other time.

I use reporting services 2008R2 and AdventureWorksDW2008R2 as the data source for this example.

Step1: Create detail report

I’ve created a report called Sales Detail. It consists of Year, Month, and Sales Amount in a table.

Step2: Add parameter to the detail report

The detail report must only show data for the year on which the user clicks on the summary report. For example, if user clicks on 2006, detail reports should show only data for 2005.

So add a parameter for Calendar Year in the detail report.
drillthrough report

Step3: Create summary report

I’ve created a summary report called Sales Summary. Below is a preview of the report. Notice the underlines for Year. They represent links and encourage users to click on them.
drillthrough report

Step4: Add Actions in the summary report

Add action for the Calendar Year textbox. Property configuration will look like the image below.
drillthrough report

Downloads:

Complete solution can be downloaded from my SkyDrive account.

Please use the email link on the right, if you have any comments or questions.

I’ll leave you with a link to books online for more information on drill through reports.

~Sam.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.