Tuesday, September 24, 2019

How to Pass Parameter from one report to another in SSRS | Drillthrough Report


Step 1:

First Create the Details Report.
 I have used the example from AdventureWorksDW2017 database.

Create the multi value Parameter: Product Name . This will accept the value passed from the summery report. Make the visibility property to Hidden.

Similarly Create the Single value Parameter: Color . This will accept the value passed from the summery report. Make the visibility property to Hidden.

Create the data set which contains details of Product, Sales Order, Sales amount and sales country details.

SELECT Prod.Color,Prod.EnglishProductName AS ProductName,Fact.SalesOrderNumber, Fact.SalesAmount,
              Fact.TotalProductCost TotalProductCost,Fact.TaxAmt,Sales.SalesTerritoryCountry
       FROM FactInternetSales AS Fact
              INNER JOIN DimProduct AS Prod ON Prod.ProductKey = Fact.ProductKey
              INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey
           Where Prod.EnglishProductName in (@ProductName) and Prod.Color in (@Color)

Insert table into report and format it as below. I have created a row group on product name.

 Step 2:

Create the Product Summery report.

Create the data set which contains the product and sum of sales amount and product cost.

SELECT Prod.Color,Prod.EnglishProductName AS ProductName, sum(Fact.SalesAmount) As SalesAmount,
              sum(Fact.TotalProductCost) TotalProductCost,sum(Fact.TaxAmt) TaxAmt
       FROM DimProduct AS Prod
              INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey
       where Prod.EnglishProductName in (@ProdName)
              group by Prod.EnglishProductName,Prod.Color

Insert table into the report and format it as below.

Create another dataset Product Name with below script

Select distinct Prod.EnglishProductName
from DimProduct Prod
 INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey
Create Multi Value parameter ProdName and link the available value from above product Name dataset

Go the text box properties of Product Name highlighted above

Go to Action->Go to Report->Select Details report
Finally configure the parameter as mentioned below.
Name : Parameter name of the details report
Value : Color from summery report and Product Name from Parameter.

Run the Summery Report

Click on color it will go to details report

Go to the Next page and so on you can see the list of product Name which are selected only on summery page.

You can see :

No comments:

Post a Comment