You have created a table (tablix control) in SSRS which is working well however now you want to pivot the dates along the top of the report (column groups) to analyse your data over a Year – Quarter – Month 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 add a new column to the table (in this example will be the right most position for simplicity)
2. Drag the measure you are interested in analysing into the cell of the newly created column. This is most likely going to be a 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. For example if you want to analyse the measure by Year, Quarter, Month you will want to first select the lowest level of the column grouping i.e. Month, then work your way up adding addition Parent Groups for Quarter and then Year. Be sure to 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 – 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 additional parent groups higher up in the hierarchy e.g. Quarter and Year, highlight the cell (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 Quarter Group above Month. You can achieve the same result by Right clicking on the Month 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.