How-to: Name exported excel sheets

Question

Post reply

How-to: Name exported excel sheets

JanJan-200247

Old Hand

Points: 321
More actions
August 11, 2005 at 1:31 pm

#168271

A question for everyone: is there a way in reporting services to name the excel sheets that were exported as the same name as the group names in the reporting services side.
Meaning that I have "grouped by" my output and when i export it to excel i get it on separate sheets because the page break at end option is checked, but i was just wondering if there is a way to name the excel sheets the same names appearing on the group titles instead of sheet1, sheet2, sheet3....etc
Thanx everybody
August 15, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

checkai Hall of Fame Points: 3041 More actions · Answer 1

Have you found an answer to this? I have the same question.

Sandip Shinde SSCrazy Points: 2068 More actions · Answer 2

I also tried for this finicality. But it seems that Export is finicality which is completely coded by Microsoft & while Executing/Exporting report we have any control on this.

Let me know if any one got crack on this.

Thank you

Sandip Shinde

geoff.daly SSC Enthusiast Points: 183 More actions · Answer 3

Hi all.

This is a question that's been asked in every reporting services forum there is, and the answer is no way in 2005 to do this. It was discussed as a possibility for 2008 but I don't know if this has been put into place.

To help my users I add a document map by assigning a label in the 'misc' section of the properties, for the group or object you want to output to each seperate tab.

Geoff.

Sandip Shinde SSCrazy Points: 2068 More actions · Answer 4

As I'm working on SQL Server 2008 Reporting Services. Export to Excel file with tab names are not yet implemented. :hehe:

geoff.daly SSC Enthusiast Points: 183 More actions · Answer 5

geoff.daly

SSC Enthusiast

Points: 183

July 30, 2008 at 6:52 am

#850437

I thought as much!

sol SSCrazy Points: 2294 More actions · Answer 6

hello!

I haven't tried this myself, as the customer said "don't worry about it...no huge deal right now"...but looks like someone got it to work.

http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2511056&SiteID=1

duppi8521 Valued Member Points: 67 More actions · Answer 7

That link at the MSDN forum appears to be broken. I did find a related link here: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/b510755a-fec4-45fb-afca-df72f9706b68/ that also points to a more detailed explanation of how to get it done using XSLT here: http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx

This feature is going to be available in SSRS 2008 R2. They have included a PageName property and others to go along with the PageBreak features that are present today. For more info look here: http://technet.microsoft.com/en-us/library/dd255278(SQL.105).aspx

R2 is not free and your shop may not want to upgrade JUST for that feature. I saw another suggestion. "One option is to schedule your report, and then run an SSIS job that opens, renames and saves the Excel worksheets on a shared drive." However, the author did not go into any detail.

I think at this point it is worth looking into some custom code (.NET, VBA, SSIS or a combination thereof) to read the names of the groups or tablix names, etc. from the RDL file and some code to manipulate the excel file to get the names into the sheets. Another solution might be to create a very simple XML for each report that needs this and apply the names from the XML to the spreadsheet via code. If I get this to work and it is simple to implement/maintain, etc I will post it. Good Luck!

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 8

Hi,

This does not work in reporting services 2005. The export only renames the sheet when the report is a single page.

We have basically hidden (changed the font colour to white) a group header field in the report which always shows in cell a3 and then created a macro in excel for the user. The suer then runs the macro when they receive the exported the report.

I know its not a very good solution 🙁 but works for the time being.

I have read somewhere the reporting services 2008 r2 has can rename the sheets.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

JanJan-200247

Now I do not profess to know how this can be incorporated into your Reporting Services work flow or T-SQL for the data selection, but go to this Simple Talk article and its extensive T-SQL code listing for doing about anything with Excel. It will provide you the T-SQL code to add sheets to a work book, name each sheet, format a range of cells, format a single cell and on and on and on. That said look it over it may give you a idea of how to accomplish what you want to do.

//www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]