How to Create Drillthrough report in SSRS

A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. The data in the drillthrough report is not retrieved until user click on the link in the main report.

Step by Step process to create Drill through report.

Two reports are required to create, Summery and Details report

Step 1:

First Create the Details Report.

 I have used the example from AdventureWorksDW2017 database.

Create the Parameter: Product Name . 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.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)

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.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

              group by Prod.EnglishProductName

Insert table into the report and format it as below.

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 : Product name from summery report.

You can change the product Name text box to hyperlink format so that easily noticed during run time.

Run the Summery report::

Click on an of the product name which will Go to the details report.

Find Video below: