Grouping in SSRS Reports
In this article, I have explained how to create a grouped reports in SSRS. Groups are created to organize data on the report or to calculate aggregate summaries. An understanding how to define groups and use group features helps you design reports that are more concise and communicate summarized data in a more compact format.
This Article is based on AdventureWorksDW database which is a sample database for SQL server. The example in this blog is to fetch the Employee information.
We are going to create a grouped table listing out Products by Country, and including subtotals of Quantity and amount.
Create a report, with a dataset showing the country, Product, Quantity and Amount takings for each country. The hierarchy I will use as an example is comprised of 2 fields: Country and Product. And the output of my stored procedure is as shown below.
SELECT salesterritorycountry,
englishproductname,
SUM(orderquantity) AS Quantity,
SUM(salesamount) AS Amount,
salesterritorycountry
FROM dimproduct P
JOIN factresellersales F
ON P.productkey = F.productkey
JOIN dimsalesterritory T
ON T.salesterritorykey = F.salesterritorykey
GROUP BY englishproductname,
salesterritorycountry 
Report Design:-
1. Create a new SSRS project and solution.
• Open SQL Server Business Intelligence Studio (BIDS).
• Select “New Project"
• Select ‘Reporting Services' template.
• Name of “SSIS" (this will be the name of the overall solution).
• Location of existing folder “C:\ SSRS_Reports".
• Check the ‘Create Directory for Solution' box.
• Click ‘Ok' and the Solution will be created.
2. In Solution Explorer window, Right click “Reports" and Click “Add New Item"
3. In Add New Item window, Select “Report", enter the Report Name and click Ok.
4. In Report Data Window, Right click “Data Sources" and Create a new data source. Refer the below image.
5. Create Department Parameter Dataset
• In Report Data Window, Right click “Data Sets" and Create a new data “Add New Dataset"
• In Query Tab, Enter the Data Set Name
• Select “Use a dataset embedded in my report." Radio button
• Expand “Data Source" drop down, Select the Data Source name (dsReport).
• Select “Query Type" as Text.
• Enter the Select Query in “Query" Text Box and Click Ok. Refer the below Screen shot.
6. In Report Design Tab, Right Click, Select Insert And Click Table.
7. Right Click Table and Select Properties and select the Dataset Name as shown below image.
8. Drag and drop the field names into Table. Add the columns which you need to include in the report output.
9. Right-click the detail row and add a new parent group
10. Group on “SalesTerritoryCountry" and choose to add a group header row.
11. Now, when you create row groups for Country, Product and then preview your report, it will look something like this.
12. Add Sub Total in the Report. Right-click on the field “Country “and the select "Insert Row" and then "Outside Group - Below".
13. Now add the sum for Quantity and amount for Product in country wise
14. Right-click on the field “Country") and the select "Insert Row" and then "Inside Group - Above".
15. Now it will look something like this.
16. Delete the surplus column. It is a good idea to add some padding so that you can identify the different levels of the hierarchy (you will set this in the Properties, under "Padding")
17. So now, when you preview your report, it will look something like this:
18. Now create a grouped table listing out Products by Country, and including subtotals of Quantity and amount.
19. There are a lot more cosmetic changes that you can make and even Totals, Average by each group.