Hide/Show Items Dependant On Export Format (SSRS)

Now and then I encounter forum questions in the style of the following:

I have a report with a title.  When rendered through the Report Manager and when exported to PDF, I want it to render as normal.  However, when exported to Excel I do not want to get the title.  How can I hide it?

Because I don’t like re-inventing the wheel each time I decided to write a blog post about it.

As of SQL Server 2008 R2, we’ve got a built-in global field that can help us out.  This field is called Globals!RenderFormat.  It has two properties: Name and IsInteractive.  Name represents the unique name that indicates the chosen renderer, and IsInteractive indicates whether or not the chosen report format is, well, interactive.

Depending on the renderer, the values of the properties differ.  To be able to use the variable in an expression, we need to know its values for each rendering format.  Here’s the list of different possibilities:

Renderer RenderFormat.Name RenderFormat.IsInteractive
Preview in BIDS or rendered through Report Manager RPL True
XML file with report data XML False
CSV (comma delimited) CSV False
TIFF file or Print button IMAGE False
PDF PDF False
MHTML (web archive) MHTML True
Excel EXCEL False
Word WORD False

If these names for RenderFormat look familiar to you, you’re probably right.  Have a look at the rsreportserver.config file in the C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer folder.  Note that you may need to adapt the folder to your specific settings.  In my case my instance is called “SQL2008R2”.  Near the bottom of that configuration file you can find the <Render> node, located under <Extensions>.  The names that you see there are those used by the RenderFormat.Name property.

Now that we know what values to test on, let’s get started.

If we get back to the example of hiding a title, or textbox, when exporting to Excel, here’s what needs to happen.  Locate the Hidden property of the textbox that you want to hide, and give it the following expression:

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

What we’re saying here is: if the RenderFormat is EXCEL, then the Hidden property should be set to True.  Which results in a hidden textbox whenever the report is exported to Excel!

As Erik pointed out in the comments, in this particular case you don’t need the IIF() statement.  The result of the expression results in True when the expected value should be True, and False when False is expected.

As a quick note: when building your expression through the expression builder, you’ll notice that the Intellisense doesn’t know the new RenderFormat field yet.  Do not worry about that, just continue typing and ignore any errors being indicated.  If you use the syntax as I highlighted above, it will work!  Well, unless you’re running an earlier version than SQL Server 2008 R2 of course.  In that case it won’t work.

Intellisense doesn't know RenderFormat yet

In contradiction to the Intellisense, the bottom part of the expression builder screen has been updated to show the new properties.  So if you don’t remember the syntax, you can just locate the field in the Built-in Fields category and give it a good double-click.

RenderFormat is located in the Built-in Fields category

Of course, the Excel example in this post is just one of many possibilities that this new field offers.  Is your company environment-friendly and does it want to prevent wasting paper?  Now it’s possible, just hide that 50-pages long table when the report is being rendered for print!

Have fun!

Valentino.

References

Globals!RenderFormat aka Renderer Dependent Report Layout by Robert Bruckner

Share

Tags: , , ,

  1. Erik Eckhardt’s avatar

    Why would you wrap a Boolean-returning expression in an Iif() function?

    Any time you catch yourself writing Iif(Expression, True, False) go back and remove the Iif:

    =Globals!RenderFormat = “EXCEL”

    That’s already True or False.

    Reply

    1. Erik Eckhardt’s avatar

      That is… `Globals!RenderFormat.Name`

      My apologies. Anyway, this was helpful. Thanks for the blog post.

      Reply

    2. Valentino Vranken’s avatar

      Good remark Erik, in that particular case you indeed do not need the IIF statement!
      Well found :-)

      Reply

    3. Saqib’s avatar

      Hi I am exporting my report into excel then its showing serial no. correct but when I am exporting it into PDF or TIFF then it shows the serial numbers wrong….Can you please help me in this regard

      Reply

  2. Mike Michalicek’s avatar

    Thanks much for this information.

    I do have 1 question.

    I need to add a row to an Audit Log table every time a user runs a report, prints a report or exports a report. I also need to make a distinction between printing and exporting a report. I noticed that the TIFF export and the Print button have the same Render Format name and IsInteractive values. Is there any way to distinguish between printing a report and exporting to a TIFF file?

    FYI, I am using a custom assembly to call a WCF service which is used to cut the audit record.

    Reply

    1. Tony Bater’s avatar

      Mike, You don’t need a custom assembly to audit report execution. Check out the view ExecutionLog3 in the ReportServer database (the latter may be ReportServer$InstanceName)n on the SSRS host server. This view has rows for each execution of the report and includes much usefult information including user, execution time, duration parameter values etc. One of the fields is ‘Format’. The values here are similar to the RenderFormat.Name values, except that printing the report logs a value of ‘IMAGE’. That shoud give you all you need for your audit.

      Reply

  3. ganesh’s avatar

    Some time , we don’t know exact capabilities of any function.
    Thanks for exploring that….

    Reply

  4. Lupe’s avatar

    PDF export doesn’t support toggling. I tried the above Is Interactive to show or hide the tablix and still no luck. The goal is to have everything hidden when it is interactive and shown when its not.
    Any suggestions on how to get around this issue ?

    Reply

  5. Tony Bater’s avatar

    Lupe, have you tried setting the hidden property of the tablix to =Globals!RenderFormat = “PDF”?
    With that settin, the tablix should be visible under all formates except PDF.

    Reply

  6. Ed’s avatar

    Have any alternatvie for SSRS 2005 ¿?

    Reply

    1. Valentino Vranken’s avatar

      Wow erm, didn’t think so… Same for 2008 btw, the RenderFormat field only exists since 2008 R2.

      Reply

  7. canon ink’s avatar

    There are some important alternative for SSRS 2005.

    Reply

  8. Darren’s avatar

    I’ve tried Hidden=(Globals!RenderFormat.Name = “RPL”) on SSRS 2008R2 and it renders on view (preview in BIDS and on server) and export.

    Is there a reason why this would not work? Config or setting?

    Reply

  9. Sunil’s avatar

    What are the alternatives for SSRS 2005 to achieve this feature.

    Reply

    1. Valentino Vranken’s avatar

      In 2005 (or even 2008 – not R2) it’s not possible to automatically detect the render format. The only possibility that I see to get something as close as possible to the RenderFormat property is to implement an additional parameter on the report. This parameter then indicates what “format” is expected and depending on the selection you can show/hide items. You don’t necessarily need to implement the parameter exactly the same as the RenderFormat of course. Depends a bit on the situation, but a parameter of type boolean called “Include details?” could already be useful for a report that should come in two versions: high-level or detailed.

      Reply

  10. satish’s avatar

    Hai To All,

    Please Help me Out.

    Iam using SSRS 2008.my question is iam writeing make,model,year in single text box,but my intention is it is splitting into 3 columns seperatly (In CSV version).

    URGENT.

    Reply

    1. Valentino Vranken’s avatar

      Try this: add an additional tablix to your report, with those three columns as needed when exporting to CSV. Set the tablix to Hidden when not rendering as CSV. Set the existing textbox to Hidden when exporting to CSV. That should accomplish your goal.

      Reply

  11. satish’s avatar

    Thanks for ur reply.

    can u give any example for this…my goal is single textbox i use 3 columns( fields) .but when i export into CSV the column names are splitted into seperately.for example we are using 3 fields in a text box but when export into csv version it should split into 3 columns .Is it possible ?

    ex:
    name district region
    state
    country

    assume it as expected output :name district state country region

    when iam exported into CSV version.

    Note:district,state,country as take as single textbox(must and should)

    Reply

    1. Valentino Vranken’s avatar

      Hi Satish,

      Would you mind posting the question on a forum and then post a link to it here? That gives me the possibility to add a sample attachment and get some points meanwhile :) I ensure you to come back with a working example.

      Have a look at my About page (Community Activity chapter) for a list of forums on which I’ve got a profile.

      Thanks,
      Valentino.

      Reply

  12. satish’s avatar

    Hi Valentino,

    where should i post my example? i see ur blog ..good.really much appreciated for ur response.please give me concern.i have a sample example also.

    Thanks,
    Satish

    Reply

    1. Valentino Vranken’s avatar

      Hi Satish,

      The Microsoft forum will probably be easiest: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/threads/

      Don’t forget to post the direct link to your question here, I’ll reply promptly.

      Valentino.

      Reply

  13. satish’s avatar

    Hi Valentino,

    Link:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8ffe3513-d38e-4abe-bc51-75ea38729a23

    I posted my question in MSDN.Please give me answer as early as possible.iam waiting for ur reply.

    Thanks,
    Satish

    Reply

  14. kumar’s avatar

    Hi Valentino,

    What is the main difference between SQLSERVER 2005 and 2008 ,2008R2.give me best 10 differences.

    Thanks,
    Kumar

    Reply

  15. Rob’s avatar

    Hi,

    I get this to work for PDF and EXCEL, but it does not work for CSV.

    works just fine:
    Globals!RenderFormat.Name = “EXCEL”
    Globals!RenderFormat.Name = “PDF”

    does not work:
    Globals!RenderFormat.Name = “CSV”

    Any ideas?

    Thanks,
    Rob

    Reply

    1. Shell’s avatar

      I’m finding the exact same problem, SSRS 2012 & Report Builder 3.0.

      I am trying to hide about 10 table columns of about 50 when exported to CSV & it is always exporting them regardless, even if I use a hidden expression based on something else other than Globals!RenderFormat.Name = “CSV”, (eg: Parameters!p_incl_ids_flag.Value = “N”)

      Tried a combination of 1 or both of these:
      * ‘column visibility’: select column, right click, column visibility
      * ‘visibility’ property: select column or column cell
      they both appear to be different

      Reply

  16. bhupesh’s avatar

    Hi ,

    Will MHTML show the hidden elements after rendering?

    pdf,excel,word showing hidden elements,but MHTML not showing hidden elements.

    Reply

  17. Keith Lindsey’s avatar

    2012 SSRS Excel name changed – it now is “EXCELOPENXML”

    Reply

    1. santosh’s avatar

      this solution worked, thank you.
      =IIF(Globals!RenderFormat.Name = “EXCELOPENXML”, false, true)

      Reply

  18. Joel’s avatar

    Thank you, Keith Lindsey! This has been driving me crazy. Couldn’t find this EXCELOPENXML nugget anywhere but here.

    Reply

  19. Patrick’s avatar

    Thanks for this – I have a customer who uses an SSRS report, but likes to do manipulation with the data after it has been exported, and the pretty pictures I included on the header were causing certain cells/columns to merge. I’d like to hide the header altogether, but for now simply hiding the images in the header solved the problem.

    Reply

  20. Andi’s avatar

    Is it possible to use the ‘RenderFormat.Name’ field for custom (added) export file types? We had the need to create a ‘PIPE’ export option (PIPE Delimited Text File) and i want to hide the header row for export to this file type only. Thanks!

    Reply

    1. Valentino Vranken’s avatar

      Hi Andi,

      I think it should be possible. When adding the custom renderer you needed to add additional config to rsreportserver.config. RenderFormat.Name contains the value that is specified through the Name attribute of the Extensions\Render\Extension node.

      Valentino.

      Reply

  21. KarenH’s avatar

    Globals!RenderFormat.Name = “CSV” does not work (as queried by Rob some time ago).

    On the properties sheet of the textbox/column/table you want to hide in CSV, set the property of item ‘Data Element Output’ to ‘NoOutput’ and it will not appear in the CSV file, but will appear in the main report or in an Excel export. Haven’t tried the effect of this on any other export types!

    Reply

    1. V’s avatar

      I tried the option of setting the ‘Data Element Output’ to ‘nooutput’. does not work. the tablix header still appears when exporting to CSV file. Any suggestions

      Reply

    2. Jann’s avatar

      This works for me. Thx!

      Reply

    3. Shell’s avatar

      Thanks this works as a workaround for what I’m after :)

      I am trying to hide 10 of 50 columns from view & from exports (eg: excel & csv) when a parameter is selected & to show them when it is not.

      Tried a combination of 1 or both of these:
      * ‘column visibility’: select column, right click, column visibility
      * ‘visibility’ property: select column or column cell
      they both appear to be different, however the columns always appear in the csv export regardless.

      I tried =Globals!RenderFormat.Name = “CSV” and =Parameters!p_incl_ids_flag.Value = “N” however the csv export always had the columns.

      By setting the ‘Data Element Output’ to ‘NoOutput’ it always stops the relevant column(s)/objects from exporting to csv.

      I’m using SSRS 2012 & Report Builder 3.0.

      Reply

  22. AnthonyC’s avatar

    If I wanted to dynamically re-format a table (because when it has more than 7 columns it word wraps when exporting or printing), could the RenderFormat variable be used in some way?

    Reply

    1. Valentino Vranken’s avatar

      Hmm, I doubt it, dynamically reformatting tables isn’t really supported (the dimensional properties don’t support expressions). The only option that I see is setting up an additional table for print layout and use the RenderFormat property in an expression in the Hidden property of the table.

      Reply

  23. Orjan’s avatar

    Hi. I use the expressions like Not(MID(LCase(Globals!RenderFormat.Name),1,4) = “html”) whitch displays
    false as expression in a textfield but if i use the same expression for visibility for a texfield, it obviously returns true. Does this have to do with the time the expression is evaluated – as the format is not html at the point of desiding visibility but it is at the point it should decide what to write?

    Reply

    1. Orjan’s avatar

      forgot to check notify via email :P

      Reply

    2. Valentino Vranken’s avatar

      Hi Orjan,

      if your expression result displays as “false” it will return the same value when used for visibility purposes. However, the property in which it is used is called “Hidden” and this is a bit misleading (illogical): it should be set to False when you need something to be actually visible and True to hide something.

      Also, note that there’s no renderer named “html”, perhaps you meant “rpl”?

      Valentino.

      Reply

      1. Orjan’s avatar

        Hi, and thanky you for quick reply!
        I figured it out. The Globals!RenderFormat.Name is not evaluated when the hidden propertiy is set but luckily the other global value RenderFormat.IsInteractive is ( so I put Not(Globals!RenderFormat.IsInteractive) in the hidden-property) and my element is now only visible in HTML and not in EXCEL and PDF, as was my final target :)

        Reply

        1. Chris’s avatar

          Thanks! That’s exactly what my problem was.

          I shouldn’t have ignored my hunch that perhaps when the Hidden property is set it RenderFormat might not be available. Easy check

          Reply

  24. Scooter’s avatar

    Wonderfully helpful! I used the following variation to hide when displayed online but auto-expand to show when printed/exported:

    =IIF(Globals!RenderFormat.IsInteractive = “True”, True, False)

    Thank you!

    Reply

    1. trubs’s avatar

      this is the same as

      =Globals!RenderFormat.IsInteractive

      Reply

  25. Fame’s avatar

    How to use this expression for check Print to Printer or Only Preview?

    I have some problem like these
    http://stackoverflow.com/questions/33561129/is-it-possible-to-show-border-on-preview-but-not-when-printing/33562245#33562245
    Can you help me, please?

    Reply

  26. Mr Foot’s avatar

    Thanks, you are my beer intelligence master. Simple and it works. Cheers

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved