Watch this video demonstration for how to add a Column Group to an existing table in SSRS, i.e. converting your table from a Tablix to a Matrix

You have created a table i.e. tablix control in SSRS which is working well however now you want to pivot the dates along the top of the report i.e. column groups, to analyse your data over a Year – Date hierarchy. You can either drag a matrix control onto your report at this point and start from scratch however this will lose all the nice formatting and expressions you have put into your table.

To convert a table to a matrix giving column group capability follow these steps…

1. First you will need to have a column in your table that contains your values i.e. in this example will be the right most position for simplicity.

2. If you don’t, then drag the measure you are interested in analysing into the cell of the newly created column or in the right most position i.e when the cell border glows blue. This should be your value i.e. sum, currency or count measure.

3. Now to create the column groups. Right click on the actual CELL i.e. not the column header, for the measure you just configured in the new column, select Add Group under the Tablix section, then under Column Group section of the menu, select Parent Group. Now you should be well on your way. You should now see a column group has been added to the table in the Column Groups section at the bottom of the Report Designer/Report Builder window. Read on to get an idea on how to build the column group hierarchy.

4. Select the column you want to group on. In this example we want to pivot our values by ADate then roll up to AYear. In order to analyse the measure by AYear -> ADate we will want to first select the lowest level of the column grouping i.e. ADate, then work your way up adding an additional Parent Group for AYear. Optionally tick the Group Header and Group Footer boxes as it is simpler and less confusing to add them now then add them later. You can always removing any unnecessary headers and footers later. Note – if you do tick these boxes, a column group footer will add a footer column to the right of the current column…therefore a header column will be added to the left.

5. To add an additional parent group higher up in the hierarchy e.g. AYear, highlight the cell that contains ADate i.e. not the detail cell you just selected in the previous step, where the new column group was just created i.e. month, Right click, select Add Group, then under the Column Group section of the menu, select Parent Group. This should add the AYearGroup above ADate. You can achieve the same result by Right clicking on the ADate group in the Column Groups section at the bottom of the report page.

6. Once the column group is in place you can create the pivot table look and feel by configuring the properties of each group, setting the Visible and Toggle properties accordingly. Optionally, tidy up any columns or rows you don’t need and set Alignment, Format and Font properties.

One Comment

  1. Pingback: SSRS – How to Add a Column Group to an existing Table | POP-BI

Leave a Comment

Your email address will not be published. Required fields are marked *