This SSRS article shows how to create a block grouped table report with subtotals and grand totals with an example. It is effortless to design using a report wizard, but there is a workaround to create manually.
Right-Click on the Datasets folder to create a new DataSet. The below screenshot shows the data set that we use for this example.
The Sql query that we used above SSRS example is:
SELECT Cat.[EnglishProductCategoryName] AS Category,
SubCat.[EnglishProductSubcategoryName] AS SubCategory,
Prod.EnglishProductName AS ProductName,
Prod.Color,
Fact.OrderQuantity AS Orders,
Fact.TotalProductCost AS ProductCost,
Fact.SalesAmount AS Sales,
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
How to Create SSRS Block Grouped Table Report with Totals?
The below screenshot shows the columns within the DataSet that we created earlier. Right-click on the empty space, choose insert, and then the table option to create a new table.
We have designed a simple table report of product sales and formatted the font and colors.
Let me show you the report preview.
Under the Row group section, click the down arrow beside the details and select add group and parent group.
Let me choose the subcategory column as the group by option.
And the report preview is.
Right-click on the orders cell and choose a total option to get the sub-totals. It will add subtotals to SSRS Block Grouped Table Report with Totals
Do the same for the product cost and sales columns. Next, choose the empty columns below the product name and color, right-click on them, and choose the Merge Cells option.
Select the subtotals row and go to its properties to change the background color to pale turquoise.
To add the grand total to SSRS Block Grouped Table Report, right-click on the subtotal column and choose to add the total option from the context menu. We have selected the product cost and do the same for the orders and sales.
Again select empty columns and right-click on them to choose merge cells.
Select the grand total column and change its background color to Gold.
Now, you can see the SSRS block grouped table report with subtotals and grand totals.
Let me go to the end of the last page to see the grand total.
