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.