This SSRS article shows how to create a group on page table report without a wizard. It means creating a table report group by column; each group has to be displayed on a separate page. It is effortless to design a group on the page 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 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
How to create an SSRS Group On Page Table Report?
The below screenshot shows the available columns in a Data Set. Next, to create a list report, right-click on the empty space, select Insert, and choose the List option. Please refer to the Create a List article.
Within the list, we added the Country column. Next, under the Row group section, click the down arrow beside the details and select group properties.
In the general tab, click the Add button to add grouping to the list report and select Country as the Group by column.
Next, go to the page breaks tab and checkmark Between each group instance.
If you check the SSRS group on the Page table report preview, you can see each Country represents a page. And there are six pages for six different countries. However, we need the information related to that particular country. To do so, we need a table. So, inside the list, right-click on the empty space, select Insert, and then choose the table option to create a table report within the list.
We have designed a simple table report of state and city-wise orders and sales and formatted the font and colors.
If you look at the report preview, the Canada page displays all the states that belong to it.
In the SSRS Group On Page Table Report preview, Page 4 shows all the states that belong to Germany.
