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 Add New Report option from the context.

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

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.
• Embedded 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

Select Tabular as the report type and click Next.

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.

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

Change the default report name and click the Finish button.

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.

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

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