You are currently reviewing an older revision of this page.
Go to current version
This article is about setting Column Visibility property for many columns based on Parameter value .
We can set  Column Visibility property for Tablix/Matrix columns by selecting the column and then right-click - > Column Visibility - >  Show or hide based on expression and then enter our custom expression as required .
But if we are in a situation to provide Column Visibility property for 50 or more columns in a report , setting it manually will be tiring & time-consuming job .
I got this idea , when I saw the related question about setting Column Visibility property for 100 plus columns on MSDN SQL Server Reporting services,Powerview forumn 

Example :

Consider I have table with five columns : (in real scenario say 50 or more columns)

CREATE TABLE Test_dynamicColumns  
(Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10))
INSERT Test_dynamicColumns SELECT 'col1','col2','col3','col4','col5'

Now below query is my table dataset query :

SELECT * FROM Test_dynamicColumns

Below query is my parameter dataset query :(columns list of table)

SELECT name FROM sys.columns
WHERE Object_name(Object_id) = 'Test_dynamicColumns'

Follow Parameter settings as shown in below images :


Below is the column visibility expression - when the parameter value matches the column name , show that column :

=IIF(InStr(Join(Parameters!ColumnSelection.Value, ","),"Col1")>0 ,False,True)

To form this expression for all columns try like below :

DECLARE @i INT  = 1,@Cnt INT,@string NVARCHAR(2000),@xmlstring XML,@Cname VARCHAR(200)
SELECT name FROM sys.columns
WHERE Object_name(Object_id) = 'Test_dynamicColumns'
DECLARE @MasterXML XML = '<TablixColumnHierarchy>
WHILE @i <= @Cnt
SELECT @Cname  = ColumnName FROM @Tmp WHERE id = @i
SET @string = '<TablixMember>
<Hidden>=IIF(InStr(Join(Parameters!ColumnSelection.Value, ","),"'+@Cname+'")>0 ,False,True)</Hidden>
--PRINT @string
SET @xmlstring = CONVERT(XML,@string) 
SET @MasterXML.modify('           
insert sql:variable("@xmlstring")           
as last       
into (/TablixColumnHierarchy/TablixMembers)[1] ')   
SET @i = @i + 1

note: in the above xml result , just replace  &gt; with >

Go to your report RDL file location : for example : C:\Users\Sathya\Documents\Visual Studio 2010\Projects\SSRS_Demo\SSRS_Demo\report.rdl

Open the rdl file in notepad - > search for <TablixColumnHierarchy> node replace that with XML segment formed using above query - > save the rdl file and then check the report .

Revert to this revision