4

There are 7 built in options for exporting SSRS 2008 reports.

I was wondering if there is an easier way to code the following in SSRS when chosing the export option:

=IIF(Globals!RenderFormat.Name="WORD" OR Globals!RenderFormat.Name="XML" OR  
  Globals!RenderFormat.Name="CSV" OR Globals!RenderFormat.Name="TIFF" OR   
  Globals!RenderFormat.Name="PDF", Globals!RenderFormat.Name="MHTML" OR 
  Globals!RenderFormat.Name="EXCEL",true,false)

Is there a way to write the code above without having to list each export option listed? A way that includes all the export options? If so, how would you write that code?

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
user1816979
  • 511
  • 4
  • 13
  • 25
  • Depending on the ssrs version there are 8 formats possible - SSRS 2008 which you tagged the question with contains an ATOM feed format. What are you trying to accomplish by your IIF expression? It'll help me answer your question or provide a better method. – ShellNinja Nov 27 '13 at 15:03
  • I want to allow the user to hide header columns when exporting the SSRS report to whatever place they want to store the data. I am trying to only have the user click on the 'built-in' export button instead of making the user select a 'hide/show' option, then view the report and then hit the built in export button. I want to save the number of steps the user needs to follow to export the data they want to use. – user1816979 Nov 27 '13 at 16:48

3 Answers3

11

The suggestion from ShellNinja won't work as a visibility expression because of the order in which expressions and other report items are processed and rendered.

The article Built-in Globals and Users References on TechNet hints at this (allbeit a very vague hint) under the RenderFormat subheading where it says that:

Globals!RenderFormat.Name is available during specific parts of the report processing/rendering cycle.

Globals!RenderFormat.Name is not populated prior to expressions being evaluated, it's populated on completion of the current render request which is why it can't be used in a visibility expression but will display the name in a textbox.

Globals!RenderFormat.IsInteractive is populated prior to expression evaluation and is the only way of hiding/showing a report item prior to a report being rendered. RPL and HTML are considered fully interactive formats, all other formats are not or only support some interactive features. More information on this can be found in the article Comparing Interactive Functionality for Different Report Rendering Extensions on TechNet.

Jonathon Ogden
  • 1,562
  • 13
  • 19
4

Use "RPL" for a simpler IIF expression so that any other format is "EXCEL", "CSV", "WORD", etc. When the report is displayed in the report server viewer or a ReportViewer control the RenderFormat is "RPL".

    =IIF(Globals!RenderFormat.Name = "RPL", true, false)

The above code when set as a visibility expression will show the field when rendered in SSRS and hide it on export.

Tip: When you have a long IIF expression use a switch expression Reporting Services Expression Examples they are by far cleaner and easier to manage.

ShellNinja
  • 629
  • 8
  • 25
  • The general idea of using the "RPL" as the parameter worked very well. Thank! However I changed the statement to =IIF(Globals!RenderFormat.Name = "RPL", false, true) this instead and the code worked fine. – user1816979 Nov 27 '13 at 19:29
  • Thought you would have wanted to hide on export based on your comment. The explanation was included for clarification in-case you needed the opposite. Regardless, glad I gave you the answer! – ShellNinja Nov 27 '13 at 19:43
  • I am mistaken. What you suggested works inconsistently. I tried =IIF(Globals!RenderFormat.Name = "RPL", true, false) in visual studi0 2008 r2 and on the test report server. Thus can you suggest what I can use so the code works all the time? – user1816979 Nov 28 '13 at 20:45
  • You can debug it by placing a textbox and setting the expression to =Globals!RenderFormat.Name Can you give an example of when it works and when it does not? – ShellNinja Nov 29 '13 at 13:21
  • Just published a report to the server here with the field, it renders as RPL in VS and Report Server. – ShellNinja Nov 29 '13 at 14:34
  • I am going to try your suggestion of " flag You can debug it by placing a textbox and setting the expression to =Globals!RenderFormat.Name" to see what is wrong – user1816979 Nov 30 '13 at 04:44
  • I did debug ssrs by placing a textbox and setting the expression to =Globals!RenderFormat.Name and the ssrs did render as "RPL". When I wanted to hide headers the following code did not work: =IIF(Globals!RenderFormat.Name = "RPL", true, false). The headers did appear. The code did work for column headers. Thus can you tell me what you would recommend so I can hide the page headers? (there are 4 tablixes that are placed prior to the detail tablix. There is nothing placed in the header part of the ssrs report – user1816979 Dec 01 '13 at 04:47
  • Change the visibility headers by adding the expression to the visibility property to whatever (page, etc.) you want to hide – ShellNinja Dec 02 '13 at 13:07
  • `Globals!RenderFormat.Name is not populated prior to expressions being evaluated`. have a look at Jonathon's reply http://stackoverflow.com/a/22175275/1629357 – par Sep 27 '16 at 14:19
2

The expression below, placed in the Column Visibility dialog box for a selected column, displays the column only when the report is exported to Excel; otherwise, the column is hidden.

=IIF(Globals!RenderFormat.Name = "EXCELOPENXML" OR Globals!RenderFormat.Name = "EXCEL", false, true)

This is mentioned in the MSDN itself. Hence it does work!

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
Ganesh Kodiganti
  • 107
  • 8