Reporting Services export to Excel with Multiple Worksheets

Solution 1:

To late for the original asker of the question, but with SQL Server 2008 R2 this is now possible:

Set the property "Pagebreak" on the tablix or table or other element to force a new tab, and then set the property "Pagename" on both the element before the pagebreak and the element after the pagebreak. These names will appear on the tabs when the report is exported to Excel.

Read about it here: http://technet.microsoft.com/en-us/library/dd255278.aspx

Solution 2:

Here are screenshots for SQL Server 2008 R2, using SSRS Report Designer in Visual Studio 2010.

I have done screenshots as some of the dialogs are not easy to find.

1: Add the group

2: Specify the field you want to group on

3: Now click on the group in the 'Row Groups' selector, directly below the report designer

4: F4 to select property pane; expand 'Group' and set Group > PageBreak > BreakLocation = 'Between', then enter the expression you want for Group > PageName

5: Here is an example expression

Here is the result of the report exported to Excel, with tabs named according to the PageName expression

Solution 3:

As @huttelihut pointed out, this is now possible as of SQL Server 2008 R2 - Read More Here

Prior to 2008 R2 it does't appear possible but MSDN Social has some suggested workarounds.