SSRS: How to set Column Visibility Property for Many Columns Based on Parameter's Value

SSRS: How to set Column Visibility Property for Many Columns Based on Parameter's Value

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 

Example:

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)
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, 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.




Leave a Comment
  • Please add 2 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Maheshkumar S Tiwari edited Revision 1. Comment: minor edit

  • Naomi  N edited Original. Comment: Minor edit

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Original. Comment: Minor edit

  • Maheshkumar S Tiwari edited Revision 1. Comment: minor edit

  • thanx.. for this great stuff

    but error is comming...

    Deserialization failed: The element 'TablixMembers' in namespace 'schemas.microsoft.com/.../reportdefinition& has incomplete content. List of possible elements expected: 'TablixMember' in namespace 'schemas.microsoft.com/.../reportdefinition&. Line 333, position 8.

  • Hi Dilip Patil ,

    Thanks for posting , make sure you place the generated scipt exactly , such that start tag and end tag of RDL XML nodes should match . If you are trying with visual studio 2008 ,default location for RDL file - C:\Users\PCname\Documents\Visual Studio 2008\Projects . If not resolved please elaborate more on what your trying exactly and post it on MSDN SSRS forumn .

Page 1 of 1 (4 items)