Conditionally Setting Column Visibility in SSRS

Conditionally Setting Column Visibility in SSRS

In this article I will show you real example of the usage of Column Visibility property in SSRS. Column visibility property is used to show or hide the column in the report. Now in this article, we can use this property to allow the users to select columns what they want to see in a report instead of all columns means they can filter the columns in SSRS report.

In this article, We used Person Address Details Report which we created earlier.You can find this report in following article :

http://bhushan.extreme-advice.com/create-sql-server-report-with-parameters/

1. Open Visual Studio and Open SSRS Project. Then open Person Address Details Report or Create Report as described in above article.

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

2-Conditionally Setting Column Visibility in SSRS

3. In Report Parameters Window, Enter name of parameter and check the check-box of Allow multiple values.

3-Conditionally Setting Column Visibility in SSRS

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

4-Conditionally Setting Column Visibility in SSRS

5. Now right click on First Tablix Column i.e. Name and select Column Visibility.

5-Conditionally Setting Column Visibility in SSRS

6. A Column Visibility window opens. In that select Show or hide based on an expression and then click on fx button as shown in below screenshot.

6-Conditionally Setting Column Visibility in SSRS

7. Enter following Expression in Expression Window.

=IIF(InStr(Join(Parameters!SelectColumn.Value,”,”),”Name“)=0,True,False)

7-Conditionally Setting Column Visibility in SSRS

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

8. Now Repeat 5.6 & 7 steps for all the columns in report. In that you need to change value in above expression which is in Bold letter. After completing this action run your report. Now you can see the filter of columns in your report.

8-Conditionally Setting Column Visibility in SSRS

9. Now if you want to see column of Name, City & State then select appropriate values in SelectColumn Parameter and click on View Report button.

9-Conditionally Setting Column Visibility in SSRS

Congratulations! We successfully completed lesson of Conditionally Setting Column Visibility in SSRS.

If any query, let me know.

For contact information, go to following link :

http://bhushan.extreme-advice.com/about-bhushan-shah/