Session 18 : SSRS Drill Through Report AnandFriday, December 19, 2014 1 comments

                                Session 18 :  SSRS Drill Through Report

Drill Through Report or Hyperlink to other SSRS Report Passing Values from one Report to Another in SSRS

Drill Through Report or Hyperlink to other SSRS Report Passing Values from one Report to Another in SSRS

This post is about the scenario when click on any values of Parent Report you want to Go to child Report. You can also pass clicked value of parent Report to filter child Report.

let us take example of Adventure work Database (AdventureworkDW2008)

we will take two table DimProduct category and DimProductSubCategory

First we will create two Report one with table DimProduct category and another Report with DimProductSubCategory


Parent Report
Create Dataset with SQL

SQL -Select * from DimProduct category

Screenshot

Child Report

Create Dataset with SQL

select * from DimProductsubCategory

screenshot

above we created just two simple report now in click of Parent Report-Product Category Key we want to navigate to child Report i.e Simple Drill through Report or Hyperlink to a Report.

for this go to Parent Report (Product Category Report)

Select Product CategoryKey column
Textbox Properties
Action
GO to Report
Specify a Report-Select your child Report
Child Report
ok

Screenshot

Preview the Parent Report

click on any Product Category field it will open the Child Report

Now we want to Pass value from parent to Child report.

so this is the final Part where clicking on the ProductiCategoryd will filter the child Report i.e will show data of only clicked Product category Id in the child Report .

for example clicking on Product categoryid -1 on Parent Report will display only product categoryid -1 data in child Report

For this what we will do we will pass the Product categoryid from Parent Report to child Report to filter the child Report


In order to achieve this

Add a Parameter to child Report

just change the child report dataset query to

select * from DimProductsubCategory where ProductCategoryKey =@ProductCategoryKey

click ok

This will automatically add a Parameter to child report


Now go to Parent Report
Select Product CategoryKey column
Textbox Properties
Action
use parameter to run the Report
In Name write the name of child report parameter( the name should be same as Child Report Parameter
in value dropdown add Productcategory key

preview the Parent Report

click on any Product category key in my case i am clicking on category 4 in Parent Report it will open the child report with only data having Product category key 4 in child report..

if you don't want to show the parameter in child Report.
Make it hidden by Right click Product category Parameter parameter properties-visibility-hidden.

Screenshot of child Report with category 4 Data.