SSRS Table using Report Wizard Page, Group, and Details

This SSRS article shows how to create a table using a Page, group, and details section in the report wizard with an example. For this Page, group, and details demo, we use the shared data source pointing to Adventure Works DW 2019. 

SSRS Table using Report Wizard Page, Group, and Details

In the Solution Explorer, right-click on the Reports folder and select the Add New Report option from the context.

Add New Report option

It opens the report wizard with the following welcome Page. Click the next button to continue.

Welcome Page

The Next Page is to select the data source. You can either select the shared data source you created earlier or choose a New data source to create a new one. Here, we choose the Adventure Works DW shared data source.

Shared data Source

The Sql query we use in the SSRS design query section is:

SELECT Geo.EnglishCountryRegionName AS Country, 
       Geo.StateProvinceName AS State,
	   Geo.City,
       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 DimCustomer 
      ON DimCustomer.CustomerKey = Fact.CustomerKey
	  JOIN DimGeography AS Geo ON DimCustomer.GeographyKey  = Geo.GeographyKey
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City
Sql Query for DataSet

Select Tabular as the report type and click Next.

Tabular Report Type

On the next Page, we have to design the SSRS Table using Report Wizard Page, Group, and Details table with the available fields. Here, there are three sections: 

  • Page – Column that represents the Page. For example, the Country where the United States page will have the records belongs to the USA.
  • Group – The table is the group by this column. For instance, Group by Product category.
  • Details – All the columns need to create a table.
SSRS Table using Report Wizard Page Group and Details

Let me choose the Table Layout as Block and checkmark the Include subtotals option.

SSRS Blocked Table using Report Wizard Page Group and Details

Change the default report name and click the Finish button.

Name the Report

Now you can see the auto-generated SSRS table report with subtotals using the wizard page, group, and details. Let me do some quick formatting to the Table font and colors.

Format Table and Colors

The report preview shows an Australia page with all the cities belonging to Australia, and the city sales are grouped by state.

SSRS Table using Report Wizard Page Group and Details Preview

Let me check the next Page to see Canada. You can also notice six pages because there are six unique countries.

SSRS Table with subtotals using Report Wizard Page Group and Details