Set Column Visibility in SSRS Report Based On Parameters Values.

In this article We  will see one real time scenario, the usage of Column Visibility property in SSRS.

STEP 1: Create Sample Data set in our report, You can use the Below Query.

SELECT 'Steven Paul' AS NAME ,'American' AS LOCATION ,'American entrepreneur' AS BUSINESS , 'NeXT' AS FOUNDER, 'Q1 2016' AS YEAR,'$10.00 billion' AS TURNOVER,'Steve Jobs' AS CEO 
UNION ALL
SELECT 'Bill Gates' AS NAME ,'American' AS LOCATION ,'American entrepreneur' AS BUSINESS , 'Microsoft' AS FOUNDER, 'Q1 2016' AS YEAR,'$22.6 billion' AS TURNOVER,'Satya Nadella' AS CEO
union all 
SELECT 'Larry Page' AS NAME ,'American' AS LOCATION ,'American entrepreneur' AS BUSINESS , 'Google' AS FOUNDER, 'Q1 2016' AS YEAR,'$20.3 billion' AS TURNOVER,'Sundar Pichai' AS CEO
union all 
SELECT 'Dhirubhai Ambani' AS NAME ,'India' AS LOCATION ,'Indian entrepreneur' AS BUSINESS , 'Reliance Industries' AS FOUNDER, 'Q1 2016' AS YEAR,'$17.00 billion' AS TURNOVER,'Mukesh Ambani' AS CEO

Your data Set will Look like this .
Visibility1

STEP 2: Now Insert a Table.
visibility2

STEP 3: Now Create One New Parameter in Report and Configure as shown in Below       Image.

visibility3

Then Set Available Values.
visibility4

STEP 4:

Now Right Click on Name Column and select Column Visibility.
visibility5

Then Set an Expression

visibility6

Click on Expression (Fx) and set to
visibility7

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

Similarly we can do for rest of the columns.
Now Whatever User select in Parameter only that column will display in Report.
visibility8.1

Now I will select All Parameter Values.

visibility91

Now You can Control your Column visibility in Your Report.

Thanks 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s