This article is about setting the Column Visibility property for many columns based on parameter value.

We can set the Column Visibility property for Tablix/Matrix columns by selecting the column and then right-clicking Column Visibility - >  Show or hide based on expression and then entering 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 a tiring & time-consuming job.

This article idea came after seeing the related question about setting Column Visibility property for 100 plus columns on MSDN: SQL Server Reporting Services, Powerview forum 

Example:

Suppose a table has 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 the table dataset query:
SELECT * FROM Test_dynamicColumns



Below query is 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 this:
DECLARE @i INT  = 1,@Cnt INT,@string NVARCHAR(2000),@xmlstring XML,@Cname VARCHAR(200)
DECLARE @Tmp TABLE (id INT IDENTITY(1,1),ColumnName VARCHAR(200))
INSERT @Tmp 
SELECT name FROM sys.columns
WHERE Object_name(Object_id) = 'Test_dynamicColumns'
SELECT @Cnt = COUNT(Id) FROM @Tmp
DECLARE @MasterXML XML = '<TablixColumnHierarchy>
<TablixMembers>
</TablixMembers>
</TablixColumnHierarchy>'
WHILE @i <= @Cnt
BEGIN
SELECT @Cname  = ColumnName FROM @Tmp WHERE id = @i
SET @string = '<TablixMember>
<Visibility>
<Hidden>=IIF(InStr(Join(Parameters!ColumnSelection.Value, ","),"'+@Cname+'")>0 ,False,True)</Hidden>
</Visibility>
</TablixMember>'
--PRINT @string
SET @xmlstring = CONVERT(XML,@string) 
SET @MasterXML.modify('           
insert sql:variable("@xmlstring")           
as last       
into (/TablixColumnHierarchy/TablixMembers)[1] ')   
SET @i = @i + 1
END
SELECT @MasterXML

Note: In the above XML result, 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 the XML segment formed using the query above - > Save the rdl file and then check the report.






 

See Also