5

In an existing 2008R2 report, I have a concern about reports being exported to Excel. I have this concern since the result is related to the visibility of tablixes, columns, and rows based upon the export option chosen by the user.

When I am exporting the SSRS 2008R2 from my visual studio 2008R2 IDE in an Excel format, the value for Globals!RenderFormat.Name = EXCEL. However when I export the SSRS 2008R2 report from the test report server in an Excel format, the value for Globals!RenderFormat.Name = EXCELOPENXML.

Thus I have the following questions which includes the following:

  1. Can you tell me why the values between my visual studio IDE and the test report server are different? If so, is there anything I can do to either environmnet to make the values the same? If so, what can I do to make the values the same?
  2. I am concerned about deploying the RDL to the production report server. How would I know if the value from Globals!RenderFormat.Name would be either EXCEL, EXCELOPENXML, or something you tell me to expect? Is there some option(setting) in SSRS I can set and/or on the report server? If so, what would the option be?
  3. If there is no way to tell what would occur on the production report server, should I just deploy the report to production and see what I get? Would I just place a "temporary" textbox on the report server and place the expression =Globals!RenderFormat.Name in that textbox? What do you recommend?
Dale K
  • 25,246
  • 15
  • 42
  • 71
user1816979
  • 511
  • 4
  • 13
  • 25

1 Answers1

7

It seems like your test server is SSRS 2012 - that's the only way would get that rendering format. The EXCELOPENXML rendering extension was introduced in that version:

The Reporting Services Excel rendering extension, new in SQL Server 2012, renders a report as an Excel document that is compatible with Microsoft Excel 2007-2010 as well as Microsoft Excel 2003 with the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint installed. The format is Office Open XML and the file extension is XLSX.

See What's New (Report Services 2012).

I would definitely confirm the test and production server SQL Server versions.

If you want to make sure the test and production servers are using the same export format as your IDE, you can re-enable the old legacy EXCEL formatter in the Reporting Services configuration file. In SSRS 2012 the relevant config file section will look something like:

<Render>
    ...
    <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
    <Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
    ...
</Render>

You can set the Visible element as required.

See RSReportServer Configuration File for more details.

But really, I don't think you should be changing the default Report Server settings - really your best bet is to upgrade your IDE to match the servers, i.e. develop using BIDS 2012.

Edit after comment

If you're just looking for a visibility expression that will work for both SSRS 2008R2 and SSRS 2012, something like:

=IIf(Globals!RenderFormat.Name Like "EXCEL*", True, False)

Should work for both versions.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • Don't you think changing the visibility value to the following check would work in both ides: =IIF(Mid(Globals!RenderFormat.Name,1,5) = "EXCEL" – user1816979 Dec 04 '13 at 19:32
  • Sure, something like that would be OK (without testing it specifically), i.e. it should catch both cases. I thought this was more a general question about why the environments could be different. Your expression to work in both versions is likely totally fine - I've added my own suggestion. – Ian Preston Dec 04 '13 at 20:00