SSRS 2008 R2 Bug? Still? Dynamically hiding column or tablix and exporting to CSV

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:

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.
If I use =IIF(Globals!RenderFormat.Name="CSV", True, False) - this doesn't work for CSV output
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

Could you add all columns to one matrix and set the Column's hidden property. — Ross Bush
I've tried that and I can the report to spit out the proper results based on the version - the problem I can't figure out is EXPORTING the report to "CSV". When I try to, it ignores the hidden columns and displays all of the columns which defeats the whole purpose. I've also tried with 2 different tablixes and hiding one of the tables. Same thing. It works on the report results, however when I go to export to "CSV", it shows both tablixes, even the one that is hidden in the results. Thanks! — Jay
Just a thought but would it work if you set the expression of the Express columns to something like --> =IIF(Globals!RenderFormat.Name="CSV", "",qryMain!Fields.MyField.Value) — Ross Bush
Hi lrb - I tried what you had suggested (ie =IIF(Globals!RenderFormat.Name="CSV", "",Fields!FIELD_TO_HIDE.Value) but I got error "The Hidden expression used in tablix returned a data type that is not valid". In this case FIELD_TO_HIDE was just a regular varchar field. Not sure if I did what you were suggesting it correctly. Thank you for the response! — Jay

Shayne Ephraim Shayne Ephraim · Accepted Answer · 2015-07-14T20:01:26

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.

SSRS 2008 R2 Bug? Still? Dynamically hiding column or tablix and exporting to CSV

1 Answers