Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.

Create Drillthrough Reports

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.


Comments

Leave a comment on the original post [svangasql.wordpress.com, opens in a new window]

Loading comments...