Export to Excel Sheet Names issue
Our client is running SQL Server 2008 (not R2) and has an SSRS Report that they require to be exported to Excel. The report is very simple - having no title, only a column header row and a detail row. No grouping, no nothing fancy. The only problem is that once exported to Excel, the sheet name must be "Sheet1". By default, the Export to Excel feature uses the Report Name as the Sheet Name. I know in R2 using Report Builder 3.0 there is a way to easily name sheets when exported, however they do not and will not be upgrading to SQL 2008 R2. I found a bit of a hack, where you could modify the Properties for a Table, and have it break to a new page "AFTER". This would then be interpreted by the export to Excel process as creating new Sheets. This works great if there is only 1 record being returned to the report, however if there are 2 or more detail lines, it goes back to using the Report name for the Sheet name. Is there any logical reason why this is happening? Or a way to resolve it without having to build a secondary step into the process?
October 7th, 2010 12:57am

We were never able to find a solution at our company. If you have a List, with page breaks, it would create separate tabs in Excel. However, we ended up using SSIS with a post-processing .Net script that used the Microsoft Office Interop Excel Application object to open the workbook, then rename the tabs to the expected values. -- Scott
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2010 1:10am

Hi, Refer to this thread where I have given link that provides a workaround for this issue using XSLT. Hope this helps. Regards, Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
October 7th, 2010 6:17am

This feature was added in Report Builder 3.0. http://technet.microsoft.com/en-us/library/dd255234.aspx#WorksheetTabNames http://technet.microsoft.com/en-us/library/dd255278.aspx "To provide the sheet name, you set the PageName property of a table, matrix, list, group, or rectangle." "Use the InitialPage property to specify a default name for the worksheet tab name when you export the report, and use page breaks and the PageName property to provide different names for each worksheet. Each new report page, defined by a page break, is exported to a different worksheet named by the value of the PageName property."
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2010 8:01pm

True, but Report Builder 3.0 can only be used with SQL 2008 R2. The non-R2 version is what's being used, so it's incompatible unfortunately.
November 11th, 2010 6:39pm

True, but Report Builder 3.0 can only be used with SQL 2008 R2. The non-R2 version is what's being used, so it's incompatible unfortunately.
Free Windows Admin Tool Kit Click here and download it now
November 11th, 2010 6:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics