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
Stan Kulp-439977
Stan Kulp-439977
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3852 Visits: 1159
Comments posted to this topic are about the item How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2
skanker
skanker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 1385
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
mannaggia
mannaggia
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 299
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...
skanker
skanker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 1385
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
MaricopaJoe
MaricopaJoe
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 438
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.
HABK24
HABK24
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 36
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
Stan Kulp-439977
Stan Kulp-439977
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3852 Visits: 1159
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
HABK24
HABK24
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 36
You are correct ... for my current report, this is not a viable solution .

Nonetheless, thank you.

Bob
Lisa Slater Nicholls
Lisa Slater Nicholls
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 631
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<
Stan Kulp-439977
Stan Kulp-439977
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3852 Visits: 1159
Thanks. A good one to know.
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