SSRS report parameter to show or hide columns

Sometimes reports are used mostly for data analysis, rather than presentation. In such cases users request many filtering options and flexibility in choosing the data included in a report. So frequently they ask to select which report columns to show.

When resulting data set is large it may be better to filter the data in the query or stored procedure and return only the columns user requests. However, there is a simple way to let a user show or hide many columns. All the column selections can be included as one additional report parameter.

Suppose a tabular report lists a number of projects with several details. There are about 20 columns, such as project name, employee contact information, project address, etc. Let's say users want ability to hide most of the columns.

To give users such ability add a multi-valued parameter to the report that lists all the columns that users can hide. The parameter values are "non-queried" and correspond to the column names. By default the report shows all columns, so all possible parameter values are also included in the default list.
In report design select a column that users can hide and find and expand Visibility property of that column. If the Hidden attribute of Visibility property is true, the column is not shown in the resulting report. Set the value of the Hidden attribute to the following expression:

=NOT Join(Parameters!p_columns.Value).Contains("COLUMNVALUE")

The COLUMNVALUE must match the value of the parameter that identifies the selected column, such as "projectname", "address", etc. The Join function combines all possible parameter values into a single string. Then Contains function tests whether the user has checked that particular value in the list. The negation of expression tells Reporting Services to hide the column if the user has not checked its value in the parameter list. Set the Hidden property to such expression for every column you want to be able to hide.

One note of caution about using this approach with SSRS 2005. Due to the way SSRS 2005 text renderer works, the exported file does not include any column that can be hidden. That affects CSV and XML export. If you export directly to Excel, all selected columns show and have values. SSRS 2008 R2 does not have such a problem: text formats include all columns regardless of visibility settings, and other formats include all columns selected by the parameter values.