This SSRS article shows an example of creating a stepped grouped table report. It is effortless to design a stepped grouped table using a report wizard, but there is a workaround to make it manually.
Right-click on the Datasets folder to create a new DataSet for the SSRS stepped grouped table report. The below section uses the Adventure Works DW database as the DataSource and embedded DataSet that we use for this example.
The Sql query that we used for this SSRS 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
How to Create SSRS Stepped Grouped Table Report?
We have designed a simple table report of product sales and formatted the font and colors. To create the SSRS stepped grouped table report, 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 and checkmark the Add group header option. The Add group header option will add an empty row (Stepped) beside each subcategory.
Now, you can see the stepped grouped table report. Let me show you the report preview.
To add subtotals, right-click on the orders and choose the Add Total option. Then, do the same for the Product cost, sales, and Tax columns.
Next, right-click empty columns below the product name and color and choose the Merge Cells option to combine them. Next, set the background color for subtotals.
Select the subtotals cell and go to its properties to change the Row background color. To get the grand total, right-click on the Sum of orders subtotal column and choose to add the total option. Then, do the same for the Product cost, sales, and Tax columns.
Change the background color of the grand total row. Next, right-click, select all the empty columns below the Header, and choose the Merge Cells option. Please refer to the Add Totals and Subtotals article.
In the SSRS report preview, you can see a stepped grouped table report subtotals for each product subcategory and grand total. Go to the end of the last page to see the subtotals and grand total.
