In versions of SQL Server Reporting Services (SSRS) previous to SSRS 2008 R2, multi-sheet Excel workbooks exported from a report looked like this, with sheets named "Sheet1," "Sheet2," "Sheet3," etc.

Starting with SSRS 2008 R2, the sheets can have custom names designated in the PageName attribute of report items that contain a PageBreak attribute.

Only SSRS 2008 R2 or later supports named sheets for exported Excel files through the PageName attribute. Any report item that contains a PageBreak attribute contains a PageName attribute. If you can't find the PageName attribute, you probably aren't using SSRS 2008 R2 or later. Report items that contain the PageBreak and PageName attributes include the Table, Matrix, Rectangle, List, Chart, Gauge, Map, Data Bar, Sparkline and Indicator items.

The attached SSRS 2008 R2 report named "Paint Sales.rdl" demonstrates how to name Excel sheets. To run this report:

1. Open SQL Server Management Studio and create a database named "Test."
2. Open the attached T-SQL script named CreateData.sql in SQL Server Management Studio and execute it.
3. Open Business Intelligence Development Studio and load the attached "Paint Sales.rdl" report file.
   • If you successfully created a table named "Test.dbo.PaintSales" in step 1, the report will work.
   • If you created the PaintSales table in some other database, you will have to edit the report data source to point to the correct database.
   • If you are not running SSRS 2008 R1, BIDS will display the following warning and error:

If the report ran properly, export it to an Excel file:

The exported Excel file should be similar to the attached "Paint Sales.xls" file.

The report contains five tables:

Open the properties dialog of one of the tables:

The table properties dialog includes checkboxes to add a page break before or after the table

In order to get to the PageName attribute, click on Row Groups/table1_Details_Group and expand the Group attribute in the Tablix Member Properties dialog box. The PageName attribute is on the lower righthand side. Enter the desired name of the sheet in the PageName attribute field.

Conclusion

Any report item that has a PageBreak attribute will contain a PageName attribute. The hard part is finding it, but this article has demonstrated how to do so.

The lack of the ability to name sheets in exported Excel files was a major annoyance in previous versions. It's inclusion in SSRS 2008 R2 is a compelling reason to upgrade.