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:

STRING_SPLIT Function

String _Split is a table valued function, which splits the string into multiple substring based on a separator character.

Syntax:

STRING_SPLIT ( string , separator )

·         Output order can be in any order. Final sort order can be overridden by order by clause.

·         Empty string can be filtered out by using where clause. ( Where value <>' ')

Below are few examples to use string_split funcction

Ex:

Select value from string_split('Red,Black,Blue',',')

Output:

value

Red

Black

Blue

Select value from string_split('Red,Black,,Blue',',')

where value<>''

Output:

value

Red

Black

Blue

use AdventureWorksDW2017

Go

SELECT Color,

       EnglishProductName AS ProductName

          from DimProduct

          where Color in (Select value from string_split('Red,Black,,Blue',',')

where value<>'')

Go

Can be used in join

SELECT Color,

       EnglishProductName AS ProductName

          from DimProduct

          join string_split('Red,Black,Blue',',') on value=Color