Revision #1

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 forum 


Suppose I have a table with five columns: (in the 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 these 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 this:

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 and replace that with XML segment formed using the query above - > save the rdl file and then check the report.

Revert to this revision