In my earlier blog post “Group small values into an ‘other’ category in a Reporting Services chart“, I talked about how to group small values together into a chart under one category called “other”. Currently I am in the process of setting up a similar idea for a matrix. I have generalized the business case as much as possible to make it easy to understand.
In the case, I have several departments where I want to report costs figures. These are the following departments:
In the matrix below, you can see that I have the departments in the columns and the countries in the rows:
The layout of this matrix is quite simple, it uses a column group for the department grouping, and a separate adjacent column group for the totals. I have added this column group for the totals myself:
To make the matrix more readable, I would like to consolidate the Consulting, Development and HR department into a group called “Other”. The matrix would then look something like this:
These are the steps that I’ve taken to accomplish this. It doesn’t look like the most dynamic way since you’re dealing with hard coded department names in an expression, but when these things are set in stone, it is a nice option to use.
1. In the group properties of the column group that groups the departments, I have set a filter to the departments that I would like to have the detailed figures (the departments displayed with the blue column labels):
2. I have added an adjacent column group in between the two existing groups, and I group these on the field “continent”. All these values have the continent Europe, so this setting should make sure that only one column is displayed. So a prerequisite of this method is that you have at least one column where every value is the same!
3. You might think that you have to set the filter in the group properties of this newly added group to the other departments that you want to show. Unfortunately this doesn’t work because you have grouped on a different field. In this case you have to modify the expression in the data part of the column to make this work. Instead of the Sum(Fields!amount.Value), the expression changes to the following:
Switch(Fields!department.Value = “Dev”, Fields!amount.Value,
Fields!department.Value = “Consulting”, Fields!amount.Value,
Fields!department.Value = “HR”, Fields!amount.Value))
Basically what it does here is for every record, the amount field is evaluated in the Switch function and is included in the Sum when it corresponds with the three conditions in the Switch function.
The final result looks like this: