Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

By Stan Kulp,

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.

Resources:

CreateData.sql | Paint Sales.rdl | Paint Sales.xls
Total article views: 5539 | Views in the last 30 days: 15
 
Related Articles
FORUM

financial report ( P&L, Balance Sheets)

financial report ( P&L, Balance Sheets)

FORUM

Changing the report manager color (SKIN) by style sheet

Changing the style sheet of report manager

ARTICLE

SQL Server Crib Sheet Compendium

As a developer, DBA or manager, you may not really want to know all about XML, replication or Report...

FORUM

Export data into the xls Sheet Directly without click on the View Report.

Export data into the xls Sheet Directly without click on the View Report.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones