[reporting-services] How to get named excel sheets while exporting from SSRS

Whenever a single page report is exported to excel, sheet in excel is named by the report name. If a report has multiple pages, the sheets are named as sheet1, sheet2,.... Is there any way to specify sheet names in SSRS 2005 ?

solution: Found this after some googleing: Changing the Sheet names in SQL Server RS Excel: QnD XSLT

Will try out and post an update if it works.

Put the tab name on the page header or group TableRow1 in your report so that it will appear in the "A1" position on each Excel sheet. Then run this macro in your Excel workbook.

Sub SelectSheet()
        For i = 1 To ThisWorkbook.Sheets.Count
        mysheet = "Sheet" & i
        On Error GoTo 10
        Sheets(mysheet).Select
        Set Target = Range("A1")
        If Target = "" Then Exit Sub
        On Error GoTo Badname
        ActiveSheet.Name = Left(Target, 31)
        GoTo 10
Badname:
        MsgBox "Please revise the entry in A1." & Chr(13) _
        & "It appears to contain one or more " & Chr(13) _
        & "illegal characters." & Chr(13)
        Range("A1").Activate
10
        Next i
End Sub

There is no direct way. You either export XML and then right an XSLT to format it properly (this is the hard way). An easier way is to write multiple reports with no explicit page breaks so each exports into one sheet only in excel and then write a script that would merge for you. Either way it requires a postprocessing step.

In SSRS 2008 R2 use PageName property of page group: http://bidn.com/blogs/bretupdegraff/bidn-blog/234/new-features-of-ssrs-2008-r2-part-1-naming-excel-sheets-when-exporting-reports

To add tab names while exporting to excel, I used the following method:

• On the report design window, select the tablix object.
• Open properties window of the tablix object.
• Add the required tab name to the PageName property.
• Run the report
• Export the report to Excel.
• Now the worksheet name is the same as the PageName property of the tablix object.

I was able to get this done following more complex instructions suggested by Valentino Vranken and rao , but here is a more simple approach , for a more simple report . This will put each table on a separate sheet and name them in Excel . It doesn't seem to have an effect on other exports like PDF and Word .

First in the Tablix Properties of of your tables under General , check either Add a page break before or after , this separates the report into sheets .

Then in each table , click the table , then in the Grouping view , on the Row Groups side , select the parent group or the default row group and then in the Properties view under Group -> PageBreak set BreakLocation to None and PageName to the sheet's name .

The Rectangle method

The simplest and most reliable way I've found of achieving worksheets/page-breaks is with use of the rectangle tool.

Group your page within rectangles or a single rectangle that fills the page in a sub-report, as follows:

• The quickest way I've found of placing the rectangle is to draw it around the objects you wish to place in the rectangle.

• Right click and in the layout menu, send the rectangle to back.

• Select all your objects and drag them slightly, but be sure they land in the same place they were. They will all now be in the rectangle.

In the rectangle properties you can set the page-break to occur at the start or end of the rectangle and name of the page can be based on an expression.

The worksheets will be named the same as the name of the page.

Duplicate names will have a number in brackets suffix.

Note: Ensure that the names are valid worksheet names.

You could use -sed- and -grep- to replace or write to the xml header of each file specifying your desired sheet name, e.g., sheetname1, between any occurrence of the tags:

<Sheetnames>?sheetname1?</Sheetnames>

While this usage of the PageName property on an object does in fact allow you to customize the exported sheet names in Excel, be warned that it can also update your report's namespace definitions, which could affect the ability to redeploy the report to your server.

I had a report that I applied this to within BIDS and it updated my namespace from 2008 to 2010. When I tried to publish the report to a 2008R2 report server, I got an error that the namespace was not valid and had to revert everything back. I am sure that my circumstance may be unique and perhaps this won't always happen, but I thought it worthy to post about. Once I found the problem, this page helped to revert the namespace back (There are tags that must also be removed in addition to resetting the namespace):

http://beatheadagainstwall.blogspot.com/2011/03/invalid-target-namespace-when-deploying.html?showComment=1440647962263#c5741523651495876761

To export to different sheets and use custom names, as of SQL Server 2008 R2 this can be done using a combination of grouping, page breaks and the PageName property of the group.

Alternatively, if it's just the single sheet that you'd like to give a specific name, try the InitialPageName property on the report.

For a more detailed explanation, have a look here: http://blog.hoegaerden.be/2011/03/23/where-the-sheets-have-a-name-ssrs-excel-export/