How to create row groups, parent and child groups to get totals or averages in SSRS?

Problem: How to create row groups, parent and child groups to get totals or averages in SSRS?

Solution: Row Groups and Column Groups

In SSRS we can group the available dataset inside of a table or matrix to represent the grouping we can use either row or column and we can do nested groups called child groups inside of a parent groups.

After grouping we can get the totals or sum of a group by adding some details columns.

If you see in the designer, there will be two grouping options in the bottom of the report designer. You can either do a drag drop or right click to add groups.

Example:

In this article, I am going to show we can group by year and months for counting number of orders by year and month.

Step 1: after adding year, month and itemid from the dataset of the report data. Right click to add a parent group. 

Step 2:  Select the Tablix group properties to group by columns, in this case I am grouping it on year of invoice.

OUTPUT:

As we can see the items are grouped on years hierarchy and each day is shown in the months. 

Step 3: Add a child group to select the month as group to get the list of items sold on that month of the year.

 Step 4: Delete the extra columns from the Tablix after the double dotted lines.   

Step 5: Output

As we can see the items are grouped on months and years hierarchy but the list of items are not totaled.

Step 6: Add total on the row groups

Step 7: You should be seeing the output to count or sum or any aggregate by changing the value field to set the aggregation properties. In this case I am going to count the orders by items.

 

Step 8 : Final output:

 

 As we can see the items are grouped on months and years hierarchy and the list of items are not totaled.