Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2 Expand / Collapse
Author
Message
Posted Friday, March 30, 2012 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:45 PM
Points: 8, Visits: 35
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
Posted Friday, March 30, 2012 10:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 6, 2014 6:18 PM
Points: 304, Visits: 521
habk24 (3/30/2012)
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.


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
Posted Friday, March 30, 2012 11:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 6, 2014 6:18 PM
Points: 304, Visits: 521
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 »

Add to briefcase ««12

Permissions Expand / Collapse