Drill Down Reports in SSRS

The Drill Down Reports in SSRS means allowing Users to Show or Hide the Column Data by providing plus and minus symbols on a text box (In short, Providing Interactivity to the Users).

In this article, we will show you how to Create Drill Down Reports in SSRS with an example.

We are going to use the below-shown table report to explain, How to create Drill Down Reports in SSRS. Please refer to the Table Report, Data Source, and Dataset articles to understand the creation of Data Source, Dataset, and SSRS Table report.

The SQL Query for the Embedded Dataset We used for creating the above report is:

-- Query for Drill down reports in SSRS
SELECT prod.Color, 
       Prodcat.EnglishProductCategoryName AS [Product Category Name], 
       prodSubcat.EnglishProductSubcategoryName AS [Product Sub Category Name], 
       prod.EnglishProductName AS [Product Name],
       SUM(fact.SalesAmount) AS Sales, 
       SUM(fact.TaxAmt) AS [Tax]
FROM DimProduct AS prod 
       INNER JOIN
     DimProductSubcategory AS prodSubcat ON 
         prod.ProductSubcategoryKey = prodSubcat.ProductSubcategoryKey 
       INNER JOIN
     DimProductCategory AS Prodcat ON 
         prodSubcat.ProductCategoryKey = Prodcat.ProductCategoryKey 
       INNER JOIN
     FactInternetSales AS fact ON 
	 fact.ProductKey = prod.ProductKey
GROUP BY  prod.Color, 
	  Prodcat.EnglishProductCategoryName, 
	  prodSubcat.EnglishProductSubcategoryName,
	  prod.EnglishProductName

How to Create Drill Down Reports in SSRS

In this example, we will show you step by step approach to create drill down reports in SSRS.

For this SSRS drill down action demonstration, we show you, How to hide Product Category Name and Product Sub-Category Name columns on Report Preview and

• Allowing a user to select the Color Name to show or hide the Product Category
• Allowing a user to select the Product Category to show or hide the Product Sub Category Column Names

Before we start enabling the SSRS Drill Down action, we have to group the columns using grouping technique.

In our previous article, we already explained the grouping. So, Please refer to the Grouping in Table Reports article to understand the below-shown screenshot.

If you observe the below screenshot, we applied the Row Grouping by Color as parent Group. Next, Product Category Column Name as Child Group and Product Sub Category Name as Product Category Child.

SSRS Drill Down Action

To enable the SSRS Drill down action, first, go to Row groups pane, and right-click on the Product Category Name will open the context menu. From the context, Please select Group Properties.. option, as shown in the below screenshot.

Once you select the Group Properties.. option, a new window will be opened to configure the Group Properties. Please select the Visibility tab on the left-hand side.

Visibility tab has multiple options for SSRS Drill down reports:

• Show: When you run the report initially If you want to show the Prod Category Name then select the Show option
• Hide: When you initially run the report, If you want to Hide the Product Category, then select the Hide option. In this example, we want to hide the Product Category Name initially, so we chose this option.
• Show or Hide based on an Expression: You can write your own expression to show/hide the Product Category by clicking the fx button.
• Display can be toggle by this report time: If you chose this option, Product Category Name would show/hide based on the selected report item. In this example, we want to toggle the Product Category Name based on the color chosen by the user. So, select the Color column from the list.

Within the Row groups pane, right-click on the Product Sub Category Name and select Group Properties.. option.

Here, we want to toggle the Product Sub Category Name based on the Product Category Name selected by the user. So, choose the Product Category Name column from the list.

Click Ok to finish enabling the SSRS Drill Down functionality on Table Reports. Now, Click on the Preview button to see the report preview

Here, + symbol before each color is the SSRS drill down action.

Let us drill down or expand the Color column to see the data present in the Product Category column

Let us expand or drill down the Prod Category column to see the data present in the Product Sub-Category column as shown below

TIP: Please refer Drill Down Matrix Report article to enable the Drop Down functionality in Matrix Reports