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.

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.

Step4: Add Actions in the summary report

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

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.