Home » BizTalk ServerRSS

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

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.

 

34 Answers Found

 

Answer 1

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.

 

Answer 2

Hi chris,

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

Thanks.

 

Answer 3

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.

 

Answer 4

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.

 

Answer 5

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

 

Answer 6

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

 

Answer 7

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?
 

Answer 8

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

 

Answer 9

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

 

Answer 10

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

 

Answer 11

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

 

Answer 12

Hi Chris,

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

Thanks,
Shital

 

Answer 13

When exporting a report  as excel  , can we pass the name of the file exported as a paramemter.. ?
 

Answer 14

How do you export  grouped data in a single table across multiple sheets?
 

Answer 15

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
 

Answer 16

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
 

Answer 17

Thanks Chris, looks like using OfficeWriter is the best solution at this point.

 

Answer 18

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

 

Answer 19

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

 

Answer 20

Hi Chris,

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

Thanks,

Ali


 

Answer 21

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

 

Answer 22

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
 

Answer 23

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<
 

Answer 24

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

 

Answer 25

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

 

Answer 26

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
 

Answer 27

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
 

Answer 28

Hi All,

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

Thank you,

Lalitha

 

Answer 29

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.

 

 

Answer 30

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

 

Answer 31

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,

 

 

 

Answer 32

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!

 

Answer 33

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

 

Answer 34

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
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter