Create SSRS drilldown report

Drilldown action can be used to create an interactive report which allows user to expand or collapse report items, rows and columns associated with a group in a table or matrix.

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.

Add drilldown action to a table group

Design a simple table report with a parent group and child group as shown below.

Select SubCat field in the grouping panel, now the group properties will be loaded in the properties panel.

In Visiblity property, set the visibility of SubCat group to show or hide the group when first time you preview a report.

• Enable the checkbox to display the group.
• Disable the checkbox to hide the group.
• To determine the visibility state at run time, set expression for visibility property.

Show group on initial display

Set the visibility for SubCat group as true in the report so that sub-category group values will be in expanded state on preview.

In Toggle property drop-down list, available textbox report items in the report will be listed. Choose the ProdCat field textbox to expand or collapse the SubCat based on product categories.

The text box that you use for the toggle must be in a containing scope that controls the item that you want to show or hide.

To differentiate the values, apply background color and font styles for parent and child group.

Now, click on the Preview button to see the report preview. Here, > symbol before each product category is the drill down action.

Initial Toggle state:

In the report preview notice the drilldown action symbol, the group values are in expanded state but shows collapsed symbol instead of expand symbol. So to maintain the symbol state Initial Toggle State property can be used.

In the ProdCat textbox properties change the Initial Toggle State as false.

Now, click on the Preview button and notice the drilldown symbol is in expanded state.

Hide group on initial display

To hide the SubCat group values when you preview the report, set the visibility for SubCat group as false.

Then, set the Initial Toggle State as True in the ProdCat textbox properties.

Now, click on the Preview button to see the report preview. Notice the sub-category group values are in collapsed state and the drilldown action symbol also is in collapsed state.

To see the sub-category product and their sales value, expand the group by clicking on the > symbol.

Download the above report design from link