Today I received a request for report that was in general a detailed table of some data from the DWH database, but with dynamic number of columns. I looked it over and at first sight I decided that there is only one solution – using a matrix component in reporting services. So far, so good. By the catch here is that when you use a matrix with row groups over more than one column (you have three groups over three different columns) the task is not that easy. The problem is that all sales for a certain group get merged in a single one and in some way repeating values in a group are suppressed. As my assignment was not to have merged columns I had to find a solution, which I will share now. The idea is to build the matrix report just as usual, but to use a “trick” in order to accomplish repeating values to show in a group. The key of the trick is actually the grouping expression of the outer row group in the report to be defined in such way that this expression to create groups that are made by distinct values of all used dataset fields. I know it sounds a little strange but with the following example I guess it will become clearer.
So first to demonstrate the problem:
The Status column has merged cells and that is not required by the users. So what I did is to modify the grouping expression only on the left-most column (in our case – Status) to be a concatenation of all fields on the rows:
=Fields!Status.Value & Fields!VendorID.Value
And there is the result:
This expression allowed the creation of distinct groups which in their turn override the merging. You can use this trick in many other situations so remember it!