Search results

Create SSRS drill through report

Drill through can be used to create an interactive report which allows user to open an report by clicking the link within the main report.

Create data

To present data in the table, create a dataset and bind data to the table data region. In this designing section, the following dataset query is used for dataset creation.

SELECT PC.Name AS ProdCat, PS.Name AS SubCat, DATEPART(yy, SOH.OrderDate) AS OrderYear, 'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales
FROM Production.ProductSubcategory PS INNER JOIN
Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Production.Product P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (SOH.OrderDate BETWEEN '1/1/2002' AND '12/31/2003')
GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

Refer Create Data section and create dataset using the above query. AdventuresWorks database is used here.

Design drill through report

Design the drill through report, before configuring the drill through action in the main report. Refer the Data bar report section and the report design will look like below.

Drill through report

Now, define a basic parameter and name it as Product Category.Basic parameter

Then apply filter condition on the dataset to filter product category and its sub categories at dataset level.

Dataset filter

Add a filter and choose Prodcat field.

Add filter

Assign a Product Category parameter as expression in the value field and click OK.

Add filter

Refer Dataset Filters section to apply filters at dataset level.

Add drill through action to a chart

Now, design a main report and link the drill through report using Link property. Here, we are going to configure the drill through action for chart series.

Refer the Pie chart section and design the report like below.

Main report

  1. Open the chart properties, Chart properties
  2. Choose Sales1 series in choose series dropdown and click edit icon. Choose series
  3. The respective series properties will be listed in secondary panel. Series properties
  4. Under Link category, choose Report option in Link To property. Link to property
  5. In the Report field, browse and set the path of already designed data bar report. Set report path
  6. Click on Set Parameters button. Now, the parameters dialog will open like below. Set parameters
  7. Click on ADD option in the dialog to configure parameter values. Add parameter
  8. In the Parameter Name field, select the parameter name of the selected report. Select parameter
  9. Provide the =Fields!ProdCat.Value expression value in Parameter Value field and click OK. Provide parameter value

    Refer Link Report section to know more about report linking.Set report action

Drill through chart series

Now, click on the Preview button and the report preview will look like normal pie chart.

Main report preview

Then click on any series in the report preview,

Main report preview

The sub-report displays sub-category wise sales amount of Bikes product. Click on the Go to parent icon to navigate from sub-report to main report.

Drill through report

Download the above report design from the links - Parent report and Child report