SSRS 2008 R2 Bug? Still? Dynamically hiding column

Question

I originally thought this would be an easy task, but after several hours of research I'm reading there may be a SSRS bug when exporting hidden fields to CSV which doesn't make this possible?

What I'm looking to do:

I have a report with several columns (let's say 50). I have a parameter drop down for REPORT_VERSION that allows the user to select "Standard" (all 50 columns) or "Express" (only 10 columns). I've been able to display the 2 versions correctly, but when I export (the express version) to CSV it shows all of the columns (and or tablixs) and not what the results look like.

I've read about and tried:

1. If I create 2 tablix and hide one based on the parameter value, the export to CSV still shows both the visible and hidden tablix.

2. If I use =IIF(Globals!RenderFormat.Name="CSV", True, False) - this doesn't work for CSV output

3. Changing DataElementOutput = NoOutput. This hides the columns or table from the CSV output, but this can't be dynamically changed based on a parameter value.

Could this be done in the "custom code" section via vbscript??

Many of the articles and threads I read through dated back to 2010-2012 so hopefully there is a solution now? I'm really at a less here.

Help would be greatly appreciated. Thank you

Answer 1

I was able to get this to work in SSRS 2008 and 2012 based on your attempt #2. I created a dummy report with a data source query of

SELECT 'value1' as col1, 'value2' as col2

I then added the following expression to Column Visibility to column2

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

Note that the true and false are reversed from your sample. When the report was generated, column 2 was hidden, but when exported to CSV, column 2 was present.

To incorporate your parameter into the visibility expression, you could do something like this

=Switch(
    Globals!RenderFormat.Name="CSV", False,
    Parameters!REPORT_VERSION.Value = "Standard", False, 
    True, True
)

This will set the hidden property of the column to false if rendered as a CSV, or if "REPORT_VERSION" parameter is set to "Standard", else hide the column. This expression would need to be added to the Column Visibility of every column you want to hide in the "Express" version of the report.

EDIT

OK, I understand the issue now and can replicate it (I was backwards on the concept). You can explicitly set the visibly of a column to hidden and it won't show up in a CSV export. However, when you attempt to control this via an expression, the CSV export ignores this setting.

Data based exports look to be controlled by the "DataElementOutput" property. Setting this to "NoOutput" will suppress this that element from the CSV output. Unfortunately, it doesn't appear it can be controlled by an expression. Microsoft does not appear to have any plans to change this(https://connect.microsoft.com/SQLServer/feedback/details/431073/ssrs-programatically-controlling-the-dataelementoutput-property)

The Microsoft connect request hints at creating two tablixes and filtering out all the results for each one based on the parameter supplied (i.e. "Express" or "Standard"), additionally hiding the other tablix based on the parameter value. I tried this and it worked halfway. Although the other tablix had no results, it would still export the column headers and one blank row.

Now I'd be curious to know what a good solution would be to this issue.