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 screenshot shows the data set that we use for this example.
The Sql query that we used above 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 DataSet. Next, to create a list report, right-click on the empty space, select insert, and choose the List option.
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.
From 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.
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.