Resources For IT Professionals
Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 24 Aug 2013 11:05 AM
  • Last revision by (aMicrosof)
  • When: 5 Sep 2013 6:47 PM
  • Revisions: 4
  • Comments: 4
Options

Revision #2

Wiki  >  TechNet Articles  >  SSRS: How to set Column Visibility Property for Many Columns Based on Parameter's Value  >  Revision #2
SSRS: How to set Column Visibility Property for Many Columns Based on Parameter's Value
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 

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.




Revert to this revision