Preserving Tablixes on Export from SSRS 2008 to Excel

In Reporting Services 2008, it is possible to export to Excel using the Visual Studio interface and the Reporting Services browser. However, what about the case where you have a complex report with numerous tablixes, matrices and so on, and want to preserve these in the target Excel Workbook? Here is a nifty trick for doing this:

1. Take the example where you have a report with two tablix or tables; for illustrations sake, we will call them Table1 and Table2./
2. To do this, go to ‘Properties’ of Table1 in question. Look for the property called ‘PageBreak’. Set this to ‘end’.
3. Save your changes
4. Run the report, and export it to excel.
Open the report in Excel. You will see that Excel has two worksheets, one for Table1 and the other for Table2.

If you want to name your Excel worksheets to something other than ‘Sheet 1’ etc., then there isn’t an easy way to do this. One option is to schedule your report, and then run an SSIS job that opens, renames and saves the Excel worksheets on a shared drive.
This issue affects both the execution of the report in Visual Studio and in the Reporting Services browser. If you have one sheet, and export the SSRS report to Excel using the Reporting Services browser, then you’re in luck: the sheet will be called the name of the report. If you then add in another table to the same report, and try to create a multi-tabbed Excel workbook, then you are not in luck: the workbook sheets will be called ‘Sheet 1’ etc. It seems as if the tab name matches the report name, unless you try to export the data to different tabs. In this case, it loses it… and sticks with the default name, ‘Sheet 1’.

Note that other reporting software also has this issue; exporting to Excel from Tableau, for example, will also keep to the default ‘Sheet 1’ worksheet name.
I hope that the Tablix names will be preserved in Excel in later versions: we will have to wait and see.

Add to Technorati Favorites

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s