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.
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.
thanks
ReplyDeleteSomeone Sometimes with visits your blog regularly and recommended it in my experience to read as well. The way of writing is excellent and also the content is top-notch. Thanks for that insight you provide the readers! how to make A BOW DRILL
ReplyDeleteYou know your projects stand out of the herd. There is something special about them. It seems to me all of them are really brilliant! podiatry drill
ReplyDeletePretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. learn more
ReplyDeleteI found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information. chiropodists drills
ReplyDelete