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 Monday, August 22, 2011 12:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:51 AM
Points: 128, Visits: 911
Comments posted to this topic are about the item How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2
Post #1163112
Posted Monday, August 22, 2011 2:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:02 AM
Points: 892, Visits: 1,240
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
Post #1163142
Posted Monday, August 22, 2011 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 7:11 AM
Points: 25, Visits: 248
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.

https://connect.microsoft.com/SQLServer/feedback/details/679029/ssrs-should-vary-the-export-filename-or-allow-it-to-be-set-at-runtime

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...
Post #1163285
Posted Monday, August 22, 2011 7:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:02 AM
Points: 892, Visits: 1,240
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
Post #1163292
Posted Monday, August 22, 2011 11:11 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:21 AM
Points: 55, Visits: 300
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.
Post #1163499
Posted Wednesday, November 30, 2011 10:28 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
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
Post #1214122
Posted Wednesday, November 30, 2011 10:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:51 AM
Points: 128, Visits: 911
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
Post #1214139
Posted Wednesday, November 30, 2011 11:39 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
You are correct ... for my current report, this is not a viable solution .

Nonetheless, thank you.

Bob
Post #1214165
Posted Friday, March 30, 2012 9:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:33 AM
Points: 304, Visits: 519
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<
Post #1275863
Posted Friday, March 30, 2012 9:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:51 AM
Points: 128, Visits: 911
Thanks. A good one to know.
Post #1275876
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse