Conditioning the visibility of SSRS columns

In this article, I'm going to show you a real-life example of using the visibility property (Visibility) of the column in SSRS.

Visibility property of the column is used to show or hide the column in the report. Now, in this article, we can use this property to allow users to select the columns they want to display in a report instead of all the columns.

For this example, we'll use the AdventureWorks database to create a report:

1. Open Visual Studio and create a new SSRS report.

Use the following query as a Dataset to create your report:

SELECT [FirstName], [LastName], [Title], [EmailAddress], [Phone], [Gender], [DepartmentName] FROM [dbo]. [DimEmployee]

2. Now, in the Report Data right click on Parameter and select Add Parameter.

2. In Report Parameters Window, Enter the parameter name and select the Allow multiple values ​​check box.

4. Now click on Available Values from the left pane. Then select Specify values and Add Label & Value as shown below. Then click on the OK button.

5. Now, right-click on the first column of the Tablix (First Name) and select Column visibility:

6. A Column visibility window opens. Select “Show or Hide based on an expression”, then click on the fx button as shown below:

7. Enter the expression below in the window expression:

= IIF (InStr (Join (Parameters! SelectColumn.Value, ","), "FirstName") = 0, True, False)

Then click on the OK button. Again click on the OK button.

1. Now repeat steps 5, 6 and 7 for all the columns in the report without forgetting to change the name of the column in the expression.

After completing this action, run your report. You can have the column filter in the report.

 

9.Now, if you want to see the FirstName, EmailAddress, and Department columns, select the appropriate values ​​in the SelectColumn parameter and click the View Report button:

Congratulations! We have successfully completed the lesson on conditional column visibility tuning in SSRS.