SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
HABK24
HABK24
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 36
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.
Lisa Slater Nicholls
Lisa Slater Nicholls
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 624
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<
Lisa Slater Nicholls
Lisa Slater Nicholls
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 624
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<
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search