How to remove cell merging in row group in Reporting Services’ Matrix Component

 

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:

image

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:

image

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!

10 Replies to “How to remove cell merging in row group in Reporting Services’ Matrix Component”

  1. Ivan,
    I can’t tell you how much time this has saved, it has been the top of my list of annoying things I can’t fix for ages! It is so simple that I am kicking myself.
    Thanks!

  2. Ivan –

    Fantastic! Probably a year or two ago I ran into this problem and didn’t figure it out… because of your blog this time it is solved in no time at all.

    Thanks a ton!!
    Eric

  3. thank you worked for me too. just added to the grouping expression to include all similar columns and one unique column. Only had to do it on the parent row grouping.

  4. I’ve tried this method & it didn’t work. I’m using BIDS 2010 that came with Denali.

    I have 4 fields I need to concatenate but anything more than 2 fields in the grouping formula & I get an invalid definition error. Even using just 2 fields, it ignored it anyway and displayed merged cells with the wrong information. I started the matrix from scratch & got the invalid definition error again.

    Any ideas?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.