BLOG

Drill Down Reports in SSRS

04-06-2019  0 Comment(s)

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 report to explain, How to create Drill Down Reports in SSRS. Please refer to 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.

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 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 SSRS grouping technique.

In our previous article, we already explained the grouping. So, Please refer to 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. Next, Product Category Column Name as Child Group and Product Sub Category Name as Product Category Child.

Comment Here

Comments

No Comments to Show

WE ALWAYS WORK WITH :