SSRS: How to set Column Visibility Property for Many Columns Based on Parameter's Value
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 > 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.
