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 :
