none
ssrs 2008 r2 export to csv and excel only

    Question

  • In an SSRS 2008 r2, I am going to have some selected colunmns set as invisibile while the ssrs 2008 r2 report is running. However when the report is exported only to excel and csv files, I want those invisible columns to be included in the export. Thus can you tell me and or show me in code how to add the selected invisible columns to only the CSV and excel exports?
    Friday, December 13, 2013 11:19 PM

Answers

  • Hi Wendy,

    Use the expression below to set the columns's visibility property.
    =IIF(Globals!RenderFormat.Name="EXCEL",false,true)
    And then set this column's DataElementOutput property to "Output".

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, December 16, 2013 3:24 AM
    Moderator

All replies

  • Hi Wendy,

    According to your description, you want to hide some columns when run the report on Business Intelligence Development Studio (BIDS) or on the report server, and show the columns on the exported file when exporting to CSV or Excel, right? Generally, we can use the expression
    =IIF(Globals!RenderFormat.Name="EXCEL" OR Globals!RenderFormat.Name="CSV",false,true) to achieve your requirement. However the expression Globals!RenderFormat.Name="CSV" is not working. And it seems that it's a known issue which you can see
    SSRS CSV export issue for hiding certain columns using expressions

    There's another property that can be used, called DataElementOutput.  By default it is set to "Auto" which means SSRS decides if the field should be exported or not. So the workaround for CSV formant is that hidden the column and set DataElementOutput to "Output". You'll notice that the field is not rendered in HTML but it is actually available when exported to CSV.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Sunday, December 15, 2013 3:29 AM
    Moderator
  • So you are saying that if I want to show hidden columns on an export for csv or excel I need to say basically if the output format is excel or the dataoutput element= 'auto"? Can you show me in code how to setup this logic?
    Monday, December 16, 2013 3:08 AM
  • Hi Wendy,

    Use the expression below to set the columns's visibility property.
    =IIF(Globals!RenderFormat.Name="EXCEL",false,true)
    And then set this column's DataElementOutput property to "Output".

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, December 16, 2013 3:24 AM
    Moderator
  • Thanks your solution works great!


    Monday, December 16, 2013 6:20 PM
  • Charlie, this Globals!RenderFormat.Name is only available on 2008 R2 and above? will it work on 2008 standard?
    Monday, December 16, 2013 9:56 PM