How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stan Kulp » How to Name Sheets in Exported Excel...

13 posts, Page 2 of 2

««1 2

How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

Rate Topic

Display Mode

Topic Options

Author

Message

HABK24

Posted Friday, March 30, 2012 10:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:53 AM
Points: 4, Visits: 18

The issue in not how do I rename the tab in Excel after it has been created. When setting up subscriptions in SSRS, it would extremely difficult to have the email recipient perform any post-production processes when THEY have absolutely no clue on how to do that. The real objective is for the task to be performed either before or during the subscription export process. For that, XML / XSLT is not a viable option as our primary objective is a "set it and forget it" setup.

Post #1275902

Lisa Slater Nicholls

Posted Friday, March 30, 2012 10:43 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 301, Visits: 472

habk24 (3/30/2012)

Then I think you simply don't understand how this option works. It does exactly what you want. There is no post-production processing.

>L<

Post #1275917

Lisa Slater Nicholls

Posted Friday, March 30, 2012 11:00 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 301, Visits: 472

You're welcome Stan.

While I'm at it, anything (whether the R2 feature or any other technique) that has to deal with Excel tab names dynamically also should deal with stripping the disallowed characters -- which were not documented until very recently and now, for the life of me, I can't make that dialog appear again so maybe un-documented again -- and the length limit for tab names (31!) -- which to my knowledge is still not documented.

see http://spacefold.com/lisa/post/2008/04/18/YAPS-(Yet-another-postscript)-on-Custom-Excel-for-RS.aspx for a discussion of both items, and don't miss the followup comment by Tom Xie.

The point here is: whether you do it in XSLT or an expression in the R2 property, you probably should create a custom function that receives your intended sheet name, and (if necessary):

* -- strips disallowed chars from your nominated value
* -- truncates with ellipses or whatever, as appropriate for your content
* -- having done the above, tests for uniqueness and adds a tie-breaker if necessary!

The expression you pass into the function is specific to the report, but the code I'm describing is generic, so IMHO it's a good candidate for doing the work on the SQL side and just passing the pre-massaged tab name as a separate column, along with the actual group values, in the dataset.

HTH,

>L<

Post #1275926

« Prev Topic | Next Topic »

13 posts, Page 2 of 2

««1 2

Permissions