locked
In SSRS, After Export into excel how to give Sheet name. RRS feed

  • Question

  • Hi All,

     

    when we export a Report in excel multiple sheets will open. How we can give the sheet name according of that report.

    let me know about the same.

    Thanks.

    Wednesday, February 13, 2008 2:10 PM

Answers

  • Hi  Moups

     

    Yes it is possible to export a data into multiple sheets. If you put more than one report in a single layout page so there is a option is there in layout to export into multiple sheets.

     

    Right click the table and check in table properties down side, Insert a page break after table. So accordingly u can break the multiple tables into different pages. When you export it will come as a different sheets.

     

    Hope i gave your Answer of your question. 

     

    Regards,

    Rahul

    Tuesday, February 19, 2008 4:13 AM
  •  

    Hi Rahul,

     

    Please see my previous answer to this same question.  It is not possible to give your worksheets a custom name.  If your report has only one worksheet, it will be the name of the report.  If it is multiple sheets, it will be named like Sheet1, Sheet2... SheetN.  There is no way to change this, but we certainly hope to add this feature in a future release.

     

    -Chris

     

    Wednesday, April 2, 2008 1:52 AM
  • Hi Oliver,

     

    Select the table body of the report and right click the table and check in table properties down side, Insert a page break after table. So accordingly u can break the multiple tables into different pages. When you export it will come as a different sheets.

     

    Regards,

    Rahul

    Thursday, April 17, 2008 9:42 AM
  • Hi chris,

     

    It is possible, Can any one know that so let me know.

     

    Thanks.

    Thursday, February 14, 2008 1:54 PM
  • Hi there,

     

    To be clear, it is not possible to do this with SSRS out of the box. 

     

    One suggestion is that you could develop an external app to post-process your XLS files and rename the worksheet tabs.  You could do this with a tool such as SoftArtisans OfficeWriter.  The app would need to programmatically render the SSRS report to Excel, then open the file with OfficeWriter and manipulate the XLS via an object model.

     

    Thanks again,

    Chris B.

    Thursday, February 14, 2008 5:27 PM

All replies

  • Hi there,

     

    Unfortunately it is not possible to rename the worksheet tabs when exporting to Excel with SSRS.  We wanted to do this but just weren't able to fit it into the SQL Server 2008 timeframe.

     

    Best regards,

    Chris B.

     

    • Proposed as answer by mamatucci Tuesday, January 22, 2013 1:16 PM
    Wednesday, February 13, 2008 7:52 PM
  • Hi chris,

     

    It is possible, Can any one know that so let me know.

     

    Thanks.

    Thursday, February 14, 2008 1:54 PM
  • Hi there,

     

    To be clear, it is not possible to do this with SSRS out of the box. 

     

    One suggestion is that you could develop an external app to post-process your XLS files and rename the worksheet tabs.  You could do this with a tool such as SoftArtisans OfficeWriter.  The app would need to programmatically render the SSRS report to Excel, then open the file with OfficeWriter and manipulate the XLS via an object model.

     

    Thanks again,

    Chris B.

    Thursday, February 14, 2008 5:27 PM
  • HI

     

    Can you please tell me whether its possible to export data using SSRS to multiple sheets in excel.If yes how?After reading you post i felt you have some how ecported the data to multiple sheets but not able to rename the sheets.But for me i need to know how to generate multiple sheets from SQL Server 2005 reporting services.

     

    Monday, February 18, 2008 11:59 AM
  • Hi  Moups

     

    Yes it is possible to export a data into multiple sheets. If you put more than one report in a single layout page so there is a option is there in layout to export into multiple sheets.

     

    Right click the table and check in table properties down side, Insert a page break after table. So accordingly u can break the multiple tables into different pages. When you export it will come as a different sheets.

     

    Hope i gave your Answer of your question. 

     

    Regards,

    Rahul

    Tuesday, February 19, 2008 4:13 AM
  • Hi Chris,

     

    In SSRS when i m Subscriptions the mail so i m getting a error like...

     

    Failure sending mail: At least one of the From or Sender fields is required, and neither was found.

     

    Please let me know.

     

    Thanks again.

    Rahul

    Thursday, February 21, 2008 4:44 AM
  •  

    Isn't it possible to accomplish this by creating an XSLT style sheet with the name of each worksheet and attach it your report by putting its name in the Data Output tab of the Report Properties dialog?
    Tuesday, February 26, 2008 8:14 PM
  •  

    Hi All,

     

    Is Any one know that, how to give sheet name, after exporting the report into different Excel sheets.

    please share with me.. if any body knows.

     

    Thanks & Regards,

    Rahul

    Tuesday, April 1, 2008 6:33 AM
  •  

    Hi Rahul,

     

    Please see my previous answer to this same question.  It is not possible to give your worksheets a custom name.  If your report has only one worksheet, it will be the name of the report.  If it is multiple sheets, it will be named like Sheet1, Sheet2... SheetN.  There is no way to change this, but we certainly hope to add this feature in a future release.

     

    -Chris

     

    Wednesday, April 2, 2008 1:52 AM
  • Hi Rahul,

      Do you know how to export the data from to Multiple Excel sheets when you only have one report?  I have a report which will do a page break if the data of the header information has changed.  Is there a way to export new header page to an Excel sheet (one new header page per sheet!!!)? 

     

    Please let me know,

     

    Thanks,

    Oliver

    Monday, April 14, 2008 11:55 AM
  • Hi Oliver,

     

    Select the table body of the report and right click the table and check in table properties down side, Insert a page break after table. So accordingly u can break the multiple tables into different pages. When you export it will come as a different sheets.

     

    Regards,

    Rahul

    Thursday, April 17, 2008 9:42 AM
  • Hi Chris,

     

    Does Feb CTP of SQL Server 2008 supports this functionality (renaming the worksheet tabs when exporting to Excel with SSRS)?

     

    Thanks,
    Shital

    Thursday, April 24, 2008 9:00 PM
  •  

    When exporting a report as excel , can we pass the name of the file exported as a paramemter.. ?
    Friday, April 25, 2008 7:36 PM
  •  

    How do you export grouped data in a single table across multiple sheets?
    Wednesday, April 30, 2008 1:58 PM
  • The Excel renderer will create a new worksheet whenever you use explicit page breaks in your report.  For example, a table group with Page Break set to Between will put one group instance on a each worksheet.

    -Chris
    Saturday, May 3, 2008 5:09 AM
  • Hi Shital, SQL Server 2008 also does not support the ability to rename worksheet tabs.  This is a key feature that we hope to add in the near future.

    Thanks,
    Chris
    Saturday, May 3, 2008 5:11 AM
  • Thanks Chris, looks like using OfficeWriter is the best solution at this point.

    Monday, May 5, 2008 4:40 AM
  •  

    Please check my above Answers. You will get the solution.

     

     

    Monday, July 28, 2008 11:59 AM
  •  

    Please check my above answers. you will get the solution.

     

    Monday, July 28, 2008 12:01 PM
  • Hi Chris,

    Are you planning to add this feature to SQL Server 2005 Reporting Services in the near future as well?

    Thanks,

    Ali


    Wednesday, August 13, 2008 5:53 PM
  • Hello,

     

    No, we won't be retroactively adding this to SQL Server 2005.  This feature will come in a release subsequent to SQL Server 2008.

     

    -Chris

     

    Wednesday, August 13, 2008 6:08 PM
  • Chris,

    Is it possible to rename the sheets using OWC?

    If so, could you post some code with the basics.

    We are still generating excel reports using DTS/VBS
    and Excel installed in an Old Win 2K server.  We are in the
    process of eliminating this situation and implementing SSRS
    as a substitute and running into some road blocks. In
    particular with the exporting feature to excel from SSRS.

    Is there a place were the OWC object model is described?
    I tried using VB6 to expose all the methods using the Object
    explorer, but I can't find too much there either (not sure if I
    am looking in the right place).

    I have been trying to find OWC documentation. But, no dice.

    Any help would be greatly appreciated.

    AL
    Wednesday, March 11, 2009 9:18 PM
  • Chris,

    It's possible.

    You choose XML output, attach an XSLT, and create SSML instead of binary Excel.

    I've written a walkthrough about this and do it all the time.  I think I've gotten more thank-yous for this post than anything I've ever written <g>.

    http://spacefold.com/lisa/post/2007/10/03/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx

    ... and if you search for XSLT on the blog you'll see lots of followup advice as people asked specific questions about this technique. 

    HTH,

    >L<

    http://spacefold.com/lisa
    Wednesday, March 11, 2009 10:41 PM
  • Lisa,

    Hi and many thanks for providing a solution via SSML/XSLT.
    I have been trying to get this to work for my particular
    situation.

    I was wondering if you have the actual files for the solution
    for me to try and follow.

    My problem:
    I am trying to port a simple report that is being created via
    old DTS VBS/Excel package to SSRS. I am recreating the report
    as an RDL file and then export with some automation to an
    excel sheet. I have been able to create multiple sheets within
    a worksheet with no problem and now need to rename each
    individual sheet. I am having trouble following your explanation
    on how it is done. Probably because I am a little rusty with xml/
    xslt and would probably better follow witht the original files.

    Let me know if you can provide the sources for everything that
    is described in your solution. I pretty much follow the concept
    and am almost there.

    Thanks in advance,
    Al Longobardi

    Friday, March 13, 2009 2:04 PM
  • Hi Lisa

    Thanks for providing such a nice article....
    I am creating a rdl file in SSRS 2005. I have been able to create multiple sheets within a worksheet with no problem and now need to rename each individual sheet. I am facing some issue while following your steps/explanation. I am getting worksheet name and formatting in exported xml, however not getting the actual row's of data.  

    I am relatively new with xml/xslt and would probably feel better with the original files, if you could provide me.

    Thanks in advance.
    Ashish


    ashish Jain
    Friday, June 19, 2009 6:26 AM
  • Lisa,

    Hi and many thanks for providing a solution via SSML/XSLT.
    I have been trying to get this to work for my particular
    situation.

    I was wondering if you have the actual files for the solution
    for me to try and follow.

    My problem:
    I am trying to port a simple report that is being created via
    old DTS VBS/Excel package to SSRS. I am recreating the report
    as an RDL file and then export with some automation to an
    excel sheet. I have been able to create multiple sheets within
    a worksheet with no problem and now need to rename each
    individual sheet. I am having trouble following your explanation
    on how it is done. Probably because I am a little rusty with xml/
    xslt and would probably better follow witht the original files.

    Let me know if you can provide the sources for everything that
    is described in your solution. I pretty much follow the concept
    and am almost there.

    Thanks in advance,
    Al Longobardi


    It's been some time now since I last post this and after the post, I figured it out after many attempts.
    AL
    Saturday, June 20, 2009 12:16 AM
  • Hi Ashish,

    I have a same problem like you had, I am getting worksheet name and formatting in exported xml, however not getting the actual row's of data.  

    I wonder if you are able to solve your problem?

    Please let me know. If yes can I see ur XML, SSML and XSLT file really appreciate it.

    Thanx

    simam
    Sunday, December 6, 2009 2:59 AM
  • Hi All,

    It is possible to rename the sheet name when exported to excel in SSRS 2008 R2.

    Thank you,

    Lalitha


    Lalitha
    Friday, May 14, 2010 5:29 AM
  • Hi, Lalitha:

    Could you please tell  me how to rename the sheet name when exported to excel in SSRS 2008 R2? I have the same requirment wiht you now, thanks in advance for your help.

     

    Wednesday, July 7, 2010 9:37 AM
  • It's been sometime now, but if you go to the beginning of the post you will find the instructions on how to do it. But in short, you have to export the report in xml format. Once you have the xml, you'll need to construct an xslt file that you'll need to provide in the ssrs output properties (i think). In the xslt document, you will provide the sheet name as you want it. That part I forget and i'll have to dig out from my solution. I am on vacation now and was checking my email.

    Hope this helps.

    AL

    Wednesday, July 7, 2010 1:47 PM
  • The Book "SQL Server Reporting Services Recipes" by Paul Turley, Robert M. Bruckner has details how to do it.

    P420. "EXCEL WORKSHEET NAMING AND PAGENAMING"

    It's for Reporting Services 2008 R2 only.

    Thanks,

     

     

    Wednesday, July 7, 2010 7:19 PM
  • Lisa:

    I'm also trying to get this to work and am having zero luck. I'm using Excel 2007, and I am not seeing ANYWHERE where I can save/export an Excel Workbook as an SSML. I save it as an XML Spreadsheet, but when I open it up, it appears as just a normal spreadsheet.

    Do you have instructions if you're using Excel 2007?

    Thank You!


    A. M. Robinson
    Friday, August 13, 2010 9:22 PM
  • Ansonee,

    The real solution is to create an xslt document that you'll need to include in the output properties of the SSRS report.

    When the report is rendered, you'll have to export the report as xml and when you do, it'll interact with the xslt template and produce the excel document with the sheet names. The specifcs on how to do the xslt is not at hand for me at the moment. But it is explained somewhere at the beginning of this thread.

    The only other way to get individual sheets without names is by playing around with the Header's within the SSRS report.

    Al

    Friday, August 13, 2010 10:28 PM
  • I know....I could not find where to save spreadsheet as XML since I'm using 2007 and not 2003, but I found it.

    http://spacefold.com/lisa/post/2007/10/03/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx



    A. M. Robinson
    Saturday, August 14, 2010 12:13 AM
  • Yes it is possible, in SQL 2008 R2... (this is for those people who might cross this page on a search)

    Please see page name property mentioned at the below link...

    http://blogs.msdn.com/b/robertbruckner/archive/2010/04/25/report-design-reset-page-number-on-group.aspx

    Sunday, February 6, 2011 8:19 PM
  • Yes it is possible, in SQL 2008 R2... (this is for those people who might cross this page on a search)

    Please see page name property mentioned at the below link...

    http://blogs.msdn.com/b/robertbruckner/archive/2010/04/25/report-design-reset-page-number-on-group.aspx


    Thank you, this is perfect and exactly what I needed. Glad I read through to the bottom.

    Thursday, March 17, 2011 7:06 PM
  • Hi Lalitha

     

    You can set a property called Page Name in the properties of the Tab beside the Page break at end..

     

    Thanks

    Ramesh

    Thursday, July 28, 2011 11:54 AM
  • I know this thread is probably too old for the answer to be seen, but there is a partial solution that nobody has yet offered.

    While you are not able to give names to the individual sheets in your Excel export, you can use the document map feature to achieve something similar.

    Suppose that you have a large report that is grouped by region, and for each region you make a new page (which means a new tab in Excel).  If you give one cell of the group a Document Map Label that uses some field of the data to describe the region, what you get is an extra tab at front page of the excel spreadsheet.

    The front page tab is named "Document Map" and will simply contain links to the other pages (tabs) using the data-driven names you gave to the groups.  Click the link, go to the page.  Yes, the tabs at the bottom of the page are still going to say "tab 1" etc., but your users do have an easy and meaningful way to find the page they want.

    Best regards,

    Jeff

    • Proposed as answer by Jai_Brijpuria Wednesday, March 7, 2012 2:06 PM
    Tuesday, September 27, 2011 12:02 AM
  • Thanks so much IronMonk! It works.
    We were tired of using XSLT in R1 version. Now we save that time. 
    Tuesday, December 6, 2011 7:34 PM
  • How come I don't see the property Page name. Is this a version thing? I am doing this is VS 2008.
    Wednesday, February 8, 2012 9:48 PM
  • For those stumbling on this thread: as of SQL Server 2008 R2, it's possible to give Excel sheets a name. 

    More details: http://blog.hoegaerden.be/2011/03/23/where-the-sheets-have-a-name-ssrs-excel-export/


    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)


    Thursday, February 9, 2012 8:05 AM
  • Hi,

    I will be looking at the walkthough as this is a huge need right now and after being told its not possible in anyway, after reading this, i beg to differ.

    Let me see how i come on and will give feedback. Thanks for the link

    Wednesday, June 27, 2012 8:31 AM
  • It is possible .  Can some one help.

    Sanjay Kumar

    Thursday, November 8, 2012 11:46 PM
  • It is easily done in SSRS 2008 R2. Go to the table properties and find the table property Name. Hint: To do this click on the upper left hand corner of the table and then press the F4 key. Enter the name for your Tab in the Name field. That's it!

    JLFDTW

    Friday, August 30, 2013 6:13 PM
  • Hi Jeff,

    Yes, I agree with you that:

    The front page tab is named "Document Map" and will simply contain links to the other pages (tabs) using the data-driven names you gave to the groups.  Click the link, go to the page

    I have another Required based on this as below:

         I need to export parameter selection to excel sheet. That too i need to export it to to front page tab "Document Map".  

         so Any suggestion please to achieve this ?

    Note: i can do this on all other tabs/sheets of excel

    Thanks,

    Venkat

    Thursday, November 14, 2013 12:05 PM