SSRS Parameter Value to Show or Hide Table Columns

This SSRS article shows how to use the report parameter value to show or hide the columns in a table report with an example. To do this, right-click on the Datasets folder to create a new DataSet. 

The screenshot below shows the data set we use for this SSRS Parameter Value to Show or Hide Table Columns example.

DataSet

The Sql query that we used above SSRS example is:

SELECT [FirstName] +  ' ' + [LastName] AS FullName
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]

We have designed a simple table report of employee sales and formatted the font and colors. Let me show the preview of the report.

Table Preview

SSRS Parameter Value to Show or Hide Table Columns

Right-click on the Parameters folder and choose Add Parameter option.

Choose Add Parameter option

It will open the following window. Add User as the name and Select the User as prompt text.

Assign Name

Under the Available Values tab, select Specify values option and click the add button. Next, enter the available parameter values as Manager and Sales. 

For example, if we select Manager, it has to display all the columns, and if Sales has picked, then Hide a few columns.

Enter the Available Values

Next, click the down arrow under the Column group section and select Advanced Mode.

Choose Advanced Mode

We want to hide the Employee yearly Income column. So, under the Column Group, click the Static representing the Income column and go to the Properties window.

Under the Visibility section, change the Hidden property from default False to Expression. 

Visibility Expression

We check whether the @User parameter value equals Sales in the expression window. If True, hide this Yearly Income Column. Otherwise, display it.

=Parameters!User.Value = "Sales"
Expression for SSRS Parameter Value to Show or Hide Table Columns

Similarly, we used the same expression to hide the Hire Date Column.

=Parameters!User.Value = "Sales"
IIF Expression

Now, if you go to the report preview, it displays all the columns for the Manager. 

SSRS Parameter Value to Show or Hide Table Columns Preview

If I select Sales as the parameter value, it does not show the Yearly Income and Hire Date Columns. However, the best part is it automatically adjusts the empty space between the columns.

Preview of the SSRS Parameter Value to Show or Hide Table Columns