How do I export a SSRS matrix to CSV without losing the structure?

Question

Consider the following data source:

declare @Test table (EmpId int, ProdId int, Sold int)
insert @Test (EmpId, ProdId, Sold) values (1, 1, 1)
insert @Test (EmpId, ProdId, Sold) values (1, 2, 2)
insert @Test (EmpId, ProdId, Sold) values (1, 3, 3)
insert @Test (EmpId, ProdId, Sold) values (1, 4, 4)
insert @Test (EmpId, ProdId, Sold) values (2, 1, 5)
insert @Test (EmpId, ProdId, Sold) values (2, 2, 6)
insert @Test (EmpId, ProdId, Sold) values (2, 3, 7)
insert @Test (EmpId, ProdId, Sold) values (2, 4, 8)
select * from @Test

I create a Sql Server Reporting Services (SSRS) 2008 R2 report that contains a single Matrix configured like this:

|         | [ProdId]    |
| [EmpId] | [Sum(Sold)] |

Which, in preview mode, renders to the following (as expected):

|   | 1 | 2 | 3 | 4 |
| 1 | 1 | 2 | 3 | 4 |
| 2 | 5 | 6 | 7 | 8 |

But when I export it to CSV I get this:

| EmpId | ProdId | Sold |
| 1     | 1      | 1    |
| 1     | 2      | 2    |
| 1     | 3      | 3    |
| 1     | 4      | 4    |
| 2     | 1      | 5    |
| 2     | 2      | 6    |
| 2     | 3      | 7    |
| 2     | 4      | 8    |

In other words, when I export to CSV I lose the matrix layout and each row of data is rendered to a single row of CSV. Note that the report works as expected if I export to Excel, so this problem seems limited to CSV so far. I have tried restructuring the matrix as a table-within-a-table but that doesn't solve it either. Any suggestions?

kscott kscott · Accepted Answer · 2011-07-06T16:46:05

This is part of a big change to the report rendering between SSRS 2005 and SSRS 2008.

The only solutions I've found are:

Export to Excel, then save the Excel document as CSV - This flattens the Excel format, and requires the groupings to be set up so that each row repeats the values of the parent groups. However, there are issues with exporting to Excel to begin with, such as is if consecutive rows contain the same data, the renderer sometimes omits the data, which can be stopped by setting the 'DataElementOutput' to True for the affected columns instead of 'Auto' which allows the renderer to guess what fields you may think are important.
Build your report as a flat table - This pretty much defeats the point of making a matrix to begin with, and is a pain, but you can define the columns in advance, and can do so dynamically either in the query or using a lot of expressions in the textbox value and setting dynamic visibility on the columns. But to pull this off could potentially involve creating dozens or hundreds of columns to handle the potential appearance of a particular value.
Don't upgrade to 2008 - If matrix reporting, and the formatting of the export, are business critical, there is really no good way to recreate the functionality in 2008, sticking with 2005 SSRS is the only sure-fire way to get the old rendering.

Resources:

How do I export a SSRS matrix to CSV without losing the structure?

3 Answers