This SSRS article shows how to create a Multi group drill down table report with examples of subtotals and grand totals. It is effortless to design multiple grouping and drill down actions using a report wizard, but there is a workaround to create it manually.
Right-click on the Datasets folder to create a new DataSet. Writing the below code inside the SSMS shows our data set for this SSRS Multi level grouping drill down table report example.
The Sql query that we used in this SSRS Multi Group Drill Down Table With Totals example is:
SELECT Cat.[EnglishProductCategoryName] AS Category,
SubCat.[EnglishProductSubcategoryName] AS SubCategory,
Prod.EnglishProductName AS ProductName, Prod.Color,
SUM(Fact.OrderQuantity) AS Orders, SUM(Fact.TotalProductCost) AS ProductCost,
SUM(Fact.SalesAmount) AS Sales, SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact
INNER JOIN DimProduct AS Prod ON Fact.ProductKey = Prod.ProductKey
INNER JOIN DimProductSubcategory AS SubCat ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey
INNER JOIN DimProductCategory AS Cat ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
GROUP BY Cat.[EnglishProductCategoryName], SubCat.[EnglishProductSubcategoryName],
Prod.EnglishProductName, Prod.Color
Create SSRS Multi Group Drill Down Table With Totals
We have designed a product sales table report and formatted the font and colors. To create the SSRS Multi group drill down table report with totals, we need a group by column. So, under the Row group, click the down arrow beside the details and select Add Group and Parent Group. Let me choose the group by option as a subcategory column. Next, checkmark the Add group header option to add an extra empty row (Stepped) beside each subcategory. Please refer to the Add grouping to the Table report article.
Right-click on the orders and choose the Add Total option to add subtotals. Do the same for the Product cost and sales columns. Next, right-click empty columns below the product name and color and choose the Merge Cells option to combine them. Select the subtotals cell and go to its properties to change the Row background color.
To add the SSRS Multi-Level grouping drill down table report with totals, click the down arrow beside the SubCategory and select Add Group and Parent Group. Please select the category as a group by option, and checkmark the Add group header option.
To get the Category level total, right-click on the Sum of orders column and choose to add the total option. Then, do the same for the Product cost and sales columns. Please change the background color. Next, right-click the empty columns to Merge those cells.
To add the grand total, right-click on the Category level totals and select the Add Total option. Please change the background color to Gold. Next, merge the Empty cells at Category and Subcategory levels. Please refer to the Add Total to the Table article to understand the steps.
Drill Down Action
Go to the Details section and choose Group Properties.
Click the Visibility tab, and select the Hide option to hide the full details row when the report initially runs. Next, checkmark the Display can be toggled by this report and select SubCategory from the available list.
Next, go to the group properties of the subcategory.
Under the Visibility tab, select the Hide option to hide the Subcategory row. Next, checkmark the Display can be toggled by this report and select Category.
In the SSRS report preview, you can see a stepped Multi-level grouped table report with drill down the action, subtotals for each product category, and grand total.
In the report preview, you can see a little plus button beside each sub-category, and it also displays the subtotals for each product subcategory. Click the plus button to view the sub-categories and expand another plus to see the detailed report of that particular subcategory.
If you are uncomfortable with extra empty row cells besides the Category and sub-category, right-click and select the delete rows option. Let me show you the SSRS Multi Group Drill Down Table With Totals report preview again.
Again, it looks much cleaner than the previous one because there is no empty row. Let me expand a few categories and sub-categories to see the subtotals and grand total.
