August 22, 2011 at 12:01 am
Comments posted to this topic are about the item How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2
August 22, 2011 at 2:05 am
Thanks for this article. It will come in useful as this was a report feature that we were asked for when we developed a particular report using 2005. We have just upgraded to 2008 r2 so I can revisit the report and win a few brownie points. 😉
I have been struggling to find things in the 2008 - although there is more to change in properties I am finding that it takes a little longer to find where everything is set.
Does anyone know if there is a way of automaticlaly naming a report when a user exports to pdf or excel?
Tim
August 22, 2011 at 7:40 am
tim.kay (8/22/2011)
Does anyone know if there is a way of automaticlaly naming a report when a user exports to pdf or excel?
No - that's what we really would like.
I logged a suggestion at connect.microsoft.com, but they closed it with a "By Design" status.
We run into a problem because if a user exports a report to Excel, leaves Excel open, then changes a parameter in the report and exports again, it exports the report with the same name each time (Report.xls).
It reports an error: "A document with the name 'Report(1).xls' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open, or rename one of the documents."
With Excel 2010, this often locks up Excel and it has to be killed with Task Manager. It has an OK button in the error message, but you can't click it to dismiss it.
I guess Microsoft is saying they designed it to lock up like that...
August 22, 2011 at 7:52 am
Thanks for the reply mannaggia.
I had a feeling that this might be the answer but thought best to ask in case anything has changed.
I was looking for a easier way for some of our report users to save a bit of time as they need to save a particular report to be sent to different organisations. The reports are sent securely using a set referencing format for each organisation. We are not allowed (at this point in time) to allow the external organisation to access the reports direct, though this will hopefully change in future once powers that be allow it to happen. The joys of bureaucracy......
Tim
August 22, 2011 at 11:11 am
I've been using this, but found that SSRS limits the sheet name to very small name. Reality is that most users want a date and the measure name or some sort of combination, but the most SSRS will support is 'Sheet Name 1".
Odd that excel supports a longer name, even programmatically via vba.
C'mon Microsoft..finish it. Just like the rest of the Dundas product, we will have to wait for SQL 19 to see it fully implemented.
November 30, 2011 at 10:28 am
I'm using SQL 2008 (not R2) ... is there any known method of naming each w/s in an exported Excel file in similar fashion with what R2 permits?
Bob
November 30, 2011 at 10:54 am
No. R2 is when Microsoft added the ability to name sheets.
Here is a macro you can use to do it after the Excel file has been generated, but it is hardly a good solution.
http://stackoverflow.com/questions/736918/how-to-get-named-excel-sheets-while-exporting-from-ssrs
Put the tab name on the page header or group TableRow1 in your report so that it will appear in the "A1" position on each Excel sheet. Then run this macro in your Excel workbook.
Sub SelectSheet()
For i = 1 To ThisWorkbook.Sheets.Count
mysheet = "Sheet" & i
On Error GoTo 10
Sheets(mysheet).Select
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
GoTo 10
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
10
Next i
End Sub
November 30, 2011 at 11:39 am
You are correct ... for my current report, this is not a viable solution .
Nonetheless, thank you.
Bob
March 30, 2012 at 9:44 am
Actually there is such a method -- and I've shown it here -- http://spacefold.com/lisa/post/2007/10/03/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx -- and there are lots of followup posts on my blog because people seem very interested in this technique.
But you may not like it.
Basically the idea is to use XML versus Excel as your output format and use XSLT to transform the XML -> SSML (Excel's 2003 XML dialect). It's quite easy, and you can save the XSLT information with your report definition so it is applied whenever XML is exported, it's not a separate option the user has to pick. The whole configuration and dev process is spelled out as a walkthrough in that post.
Of course, in later posts, I've let people know that in R2 this is no longer necessary *IF* the only thing you care about changing in Excel is the sheet names -- but it's still a great technique to know, because there's *lots* more you might want to alter in the default Excel presentation that SSRS provides.
>L<
March 30, 2012 at 9:57 am
Thanks. A good one to know.
March 30, 2012 at 10:23 am
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.
March 30, 2012 at 10:43 am
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<
March 30, 2012 at 11:00 am
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<
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply