SSRS Report table grouping issue

Question

ColumnA  | ColumnB  |  ColumnC  |  ColumnD  |  ColumnSubD
   A          B           ID1       TypeA      TypeA.1
   A          B           ID1       TypeA      TypeA.2
   R          Z           ID2       TypeA      TypeA.1
   R          Z           ID2       TypeB      TypeB.1

I have a Stored Procedure that return 4 rows of records as above. After creating the dataset and configured the tablix properties to bind the SP, the table is showing 4 records exactly like above.

I'm trying to display the data like below, which is first group by ColumnC then another subgroup on ColumnD

ColumnA  | ColumnB  |  ColumnC  |  ColumnD  |  ColumnSubD
   A          B           ID1       TypeA      TypeA.1
                                               TypeA.2
   R          Z           ID2       TypeA      TypeA.1
                                    TypeB      TypeB.1

The first step I tried was to create a rowgroup grouping on ColumnC, what happened was it automatically created a duplicated columnC on the left.

May I know what is the proper way to achieve what I want and if can please just list down the steps. Many thanks.

Have you tried using a Matrix, rather than a Tablix? A Matrix does this grouping for you. — R. Richards

Dan Scally Dan Scally · Accepted Answer · 2017-02-27T12:03:50

With the caveat that I am assuming if ColumnA = A then ColumnB will always equal B, and ColumnC will always equal ID1

Add a table
Add ColumnSubD into one of the columns and delete the other 2
Right click the Details row in Row Group, and select "Add Group" > "Parent Group"
Set Group by to "Column D"
Right click the new group and select "Add Group" > "Parent Group" again
Set Group by to "Column C"
At this point you should have the leftmost column showing [ColumnC]. Right click that column and click "Insert Column" > "Inside Group - Left" twice Add columns A and B to those 2 new columns.

You'll end up with something like this:

SSRS Report table grouping issue

1 Answers