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 example.
We are going to use below shown report to explain, How to create Drill Down Reports in SSRS. Please refer SSRS Table Report, Data Source and Dataset articles to understand the creation of Data Source, Dataset and Table report. If you observe the below screenshot, It’s just a normal Table report with Product Name, Sales Amount and Tax Amount columns.
SQL Query for the Embedded Dataset We used for creating the above report is:
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
Steps to Create Drill Down Reports in SSRS
In this example we will show you, How to hide Product Category Name and Product Sub Category Name columns on Report Preview and
- Allowing user to select the Color Name to show or hide the Product Category Name
- Allowing user to select the Product Category Name to show or hide the Product Sub Category Column Names
Before we start enabling the Drill Down action, we have to group the columns using SSRS grouping technique. In our previous article, we already explained the grouping so, Please refer Grouping in SSRS 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 then Product Category Name as Child Group and then Product Sub Category Name as Product Category Name Child.
First, goto 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 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 have multiple options:
- Show: When you run the report initially, If you want to show the Product Category Name then select the Show option
- Hide: When you run the report initially, If you want to Hide the Product Category Name then select the Hide option. In this example, we want to hide the Product Category Name initially so we selected this option.
- Show or Hide based on an Expression: You can write your own expression to show / hide the Product Category Name by clicking fx button.
- Display can be toggle by this report time: If you select this option, Product Category Name will be show/hide based on the report item we selected here. In this example, we want to toggle the Product Category Name based the Color selected by the user so, select the Color column from the list as shown below.
Within the Row groups pane, right-click on the Product Sub Category Name and select Group Properties.. option as shown in below screenshot.
Here, we want to toggle the Product Sub Category Name based the Product Category Name selected by the user so, select the Product Category Name column from the list as shown below.
Click Ok to finish enabling the Drill Down functionality in SSRS Table Reports. Now, Click on the Preview button to see the report preview
Let us expand the Color column to see the data present in the Product Category column
Let us expand the Product Category column Data to see the data present in the Product Sub Category column as shown below
TIP: Please refer Drill Down Matrix Report in SSRS 2014 article to enable the Drop Down functionality in Matrix Reports
Thank You for Visiting Our Blog