SSRS – How to Add a Column Group to an existing Table

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.

 

About these ads
About

Having laid the foundation for my career in database technical services and enterprise reporting, I have found my calling in helping people use Microsoft SQL Server Business Intelligence Solutions to provide better decisions. Specialties Business Intelligence Consulting, Architecture and Solution Development, Data Modelling, Training, Database Management, Database Consolidation and Virtualisation, SQL Server and Storage Consolidation, SQL Server Virtualisation, Data Migration, Database development and Database administration.

Posted in Report (SSRS Report Builder Power View)
4 comments on “SSRS – How to Add a Column Group to an existing Table
  1. [...] clear guide that just saved me some time – check it out here at the POP-BI blog. Thanks! Hint – you need to select a cell containing a measure in your Tablix before bringing [...]

  2. Robert Bang says:

    Thank you. You’ve just saved me an hour! :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

PopBI
Tags
1.#INF 70-461 70-462 70-463 70-464 70-465 70-466 70-467 access 97 excel 2010 ssis add foreign key add primary key alter column information_schema.columns case senstive search tsql certification certs convert string to number cross join sample cte parent description datatypes dependsondimension property displaying ssrs multi select parameter values in a report drillthrough duplicate attribute key Error: Subreport could not be shown format ssrs column headers with line breaks formatting 000's indexed views for reporting Learn Excel management studio status bar colours many to many relationships ssas mdx for starters MDX Member Does not Exist mdx query designer mdx top items and other items mdx top topcount over() performance point 2010 external access performance point deployment performance point error 11861 performance point security primer performance point unattended account planning ssrs security pps 2010 time intelligence functions processing cube job step publish report parts from report designer rds removing all member reportserver catalog reportserverdb reportservertempdb rsds select into vs insert into sql 2008 r2 to sql 2012 sharepoint integrated sql 2012 feature comparison sql server 2012 Denali Exam sql server service packs and cumulative updates sql server upgrade advisor 2012 2008 R2 sql server virtualisation vsphere srss textbox value expression SSAS Reporting Actions ssis cache lookup ssis casting ssis timestamp expression ssrs catalog report list ssrs integrated mode feature comparison ssrs parent child charts ssrs pie percentages strtomember Subreport tablix inside a list tablix inside a list - custom heading The LocaleIdentifier property error There is an error in XML document winlogon.exe windows 8 shutdown error your first power view
Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: