matrix report exported to excel

Question

matrix report exported to excel

antonio.collins

SSCrazy Eights

Points: 9600
More actions
June 30, 2008 at 4:14 pm

#193275

When exported to Excel, any fixed columns with repeating values in a matrix report are merged into a single cell. In the example below, MD and VA will be merged into a cell that spans 2 rows and 3 rows respectively.
MD Baltimore
Fredrick
VA Alexandria
Fairfax
Richmond
This makes it next-to-impossible to work with the data in Excel. For instance, the data can't even be re-sorted without unmerging the MD and VA cells. And after the merge is undone, the 2nd, 4th, and 5th rows have blank values in the first column.
Is there any way to force a matrix report to stop hiding repeating values and display AND export all of its data? (I honestly can't believe this ability is not available.)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 1

Well I've tried a number of different things this morning and I absolutely cannot get it to repeat these group values. I'm with you, it's pretty unbelievable really.

I am assuming that the new TABLIX format in SSRS2008 might help with this as it's supposed to provide a true combination of a Table/Matrix.

Regards,

Nigel West
UK

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

i've spent the better part of two days fiddling with every option i could think of. i still think the ability is there, but the product is so poorly documented and bids is so akward to use that i probably just can't find it.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 3

Have you tried using the table control instead?

Note: the Matrix control is designed specifically for this behavior. What is the base requirement here? Reporting or dumping data?

Dave Klug SSCertifiable Points: 5811 More actions · Answer 4

Another option would be to add a table to use only for export to CSV. Hide it when display but set the columns to export. I had the same issue and that's the best option we've found so far.

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 5

How would you go about dealing with a variable number of columns in a table?

Nigel West
UK

Dave Klug SSCertifiable Points: 5811 More actions · Answer 6

The columns would not be the same as in the matrix but would appear the same as the dataset. That is the drawback to this approach.

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 7

OK, I have managed to do this.

First, normally you would have two row groups in this matrix, the first based on the outer grouping (in my case County) and the second based on the inner grouping (in my case Town).

I have changed this and set up a group that is a combination of County + Town. This first step means that you have a single group that will repeat for each combination of County/Town.

The problem then is that you only have one space available to put a column into the row group area of the matrix. I got around this by adding a blank row group (you do this just by adding the word blank where you normally put your expression). This now gives me the ability to put the County into the outer group (repeated because the grouping is on County+Town) and the Town into the blank group (which will be repeated anyway, because it's a blank group).

I attached (I hope I did anyway) the file Test1.Rdl.DOCX which can be renamed to Test1.RDL, this is a report which is not based on any database, it simply has a dataset built into it manually. You will probably need to change the datasource though.

Good luck,

Nigel West
UK

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 8

nigel.c.west (7/2/2008)
OK, I have managed to do this.
First, normally you would have two row groups in this matrix, the first based on the outer grouping (in my case County) and the second based on the inner grouping (in my case Town).
I have changed this and set up a group that is a combination of County + Town. This first step means that you have a single group that will repeat for each combination of County/Town.
...

Thanks nigel. I had tried making the row group based on multiple fields, but I was concatenating multiple fields and with 6 or 7 fixed fields of varying types that wasn't a solution. It never occured to me that the "group on" dialog allowed multiple fields.

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 9

btw, this approach eliminates the ability to have subtotals for row groupings since each 'row' is a group of one. maybe ssrs 2008 will handle matrix reports better.