How to create nested group in ssrs

Question

Sorry if this is a basic question, but I just started with SSRS and wasn't able to find something on google. Let's say I have a data that is returned in the form (Column1, Column2, Column3, Column4). I am trying to build a report that would group first on Column1 and then on Column2. For example if my query returns a result:

Column1 Column2 Column3 Column 4
Type A  Item A  Name 1  1
Type A  Item A  Name 2  2
Type A  Item B  Name 3  3
Type B  Item A  Name 4  4

I would like to see something like

Column1 Column2 Column3 Column 4
Type A  Item A  Name 1  1
Type A  Item A  Name 2  2
Type A  Item B  Name 3  3
Type B  Item A  Name 4  4


    Type A
    Item A
Name 1  1
Name 2  2
Total:  3
    Item B
Name 3  3
Total:  3
    Type B
    Item B
Name 4  4
Total:  4

Start by adding a Matrix to the report body from the toolbox. Then add (drag and drop from the dataset) some of you columns where you think they should display into the cells of the Matrix. Play around with it until you end up with something usable. A Matrix does the grouping automatically based on where you drop fields. It one of those things that you have to work with a bit to get used to. — R. Richards

McGlothlin McGlothlin · Accepted Answer · 2017-07-07T22:22:40

Right click on your Tablix and select Add Group -> Parent Group. Group on Column 1. Next, select the same row and do it again but this time select Child Group. Set the grouping to Column 2.

The tablix will automagically group your data based on Column 1 first, then Column 2, and this will be reflected in your totals. To add a total the way you specified in your question, you're going to want to total based on the outer group, so you'd right click on the row and select Add Total. Sometimes it does not allow you to do this (depending on how your report is set up) so you could simply do Sum(Column4) in a textbox below - inside the outer group and outside the inner group - to achieve this result.

How to create nested group in ssrs

2 Answers