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

  • Comments posted to this topic are about the item How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

  • 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

  • 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...

  • 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

  • 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.

  • 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

  • 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

  • You are correct ... for my current report, this is not a viable solution .

    Nonetheless, thank you.

    Bob

  • 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<

  • Thanks. A good one to know.

  • 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.

  • 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<

  • 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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply