SSRS Block Grouped Table Report with Totals

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 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, 
       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.

New table Option

We have designed a simple table report of product sales and formatted the font and colors.

Formatted Table Report

Under the Row group section, click the down arrow beside the details and select Add Group and Parent Group.

Add Parent Group to a Table

Let me choose the subcategory column as the group by option.

Select the group by column

Let me show you the report preview.

Grouped Table report preview

Right-click on the orders cell and choose a total option to get the sub-totals. It will add subtotals to the SSRS Block Grouped Table Report with Totals

Add subtotals to SSRS Block Grouped Table Report

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.

Add sub totals to SSRS Block Grouped Table Report

Select the subtotals row and go to its properties to change the background color to pale turquoise.

Change subtotals Background Color in SSRS Block Grouped Table Report

To add the grand total to the 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.

Add grand totals to SSRS Block Grouped Table Report

Again select empty columns and right-click on them to choose merge cells. Next, select the grand total column and change its background color to Gold.

Change grand totals Background color in SSRS Block Grouped Table Report

Now, you can see the SSRS block grouped table report with subtotals and grand totals.

SSRS Block Grouped Table Report with Subtotals and Grand Totals preview

Let me go to the end of the last page to see the grand total. 

Final Page preview