Dynamically Add/Remove columns ssrs at runtime according to my select query

Question

Hi All,

Today,i came with a problem which is common but i am unable to find any proper solution of this, so here i describe it,

I want to Add/remove columns  from ssrs report, according to user selection,

suppose my table structure is like,

A || B || C || D || E ||

=================================

1 || abc || xbg || ih || hfj ||

2 || kdj || kjhv || fh || jjh ||

now,i have  to provide column selection to the user, like

if user select, B,D then my report should be like,

B || D ||

============

abc || ih ||

kdj || fh ||

So, How can i do this, i know about the option of Hiding Column, but i do not want that, because i have 100 + columns,

Xml Updation is also an option but it is a very lengthy process and i am unable to find proper solution for this,

Please help me on this...it is very urgent for me...

Thanks in advance....

---

shahsank

Answer 1

hey ,

in your query ....

create a dummy column that returns your character convert with ASCII value 

like 

A  1 2 3 4 5 6

B  1 2 3 4 5 6

C --------------

D --------------

like this.... then pivot on this column so you will get 

A   B  C  D

1  1   -

2  2   -

3  3 

4  4

5

like this ...

hope you got the solution

Answer 2

Hi Shashank,

SSRS does not provide a way to dynamically define the columns, you need to define the metadata of column at the design time, (based on all possible columns that can be displayed).

Then runtime you set the visibility property of column to show the column which are selected by user.

Here is a really good post that show how it can be done -> http://sql-bi-dev.blogspot.in/2010/10/displaying-dynamic-columns-in-ssrs.html

---

Regards Harsh

Answer 3

Hi ,

Consider I have table with five 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'

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 below :

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

---

sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

Answer 4

Thanks for reply sathya,

You have mentioned that, i have to make column visibility show/ hide, but i have already used this method,

the problem is i have some 65+ columns in report ad if i hide 60 of them still report size(width) is as wide as 65 columns,

so i need a way by which we can add or remove columns at run time and user can select the columns from column list whatever he need to see.

---

shahsank

Answer 5

Hi Shahsnk,

As Harsh said in the above, SQL Server Reporting Servicecs (SSRS) does not provide a way to dynamically define the columns. Currently the best workaround is control the columns visibility base on the parameter's values. If you have any concerns about this feature, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product.

Thank you for your understanding.

Regards,

---

Charlie Liao
TechNet Community Support

Answer 6

Thanks for reply sathya,

You have mentioned that, i have to make column visibility show/ hide, but i have already used this method,

the problem is i have some 65+ columns in report ad if i hide 60 of them still report size(width) is as wide as 65 columns,

so i need a way by which we can add or remove columns at run time and user can select the columns from column list whatever he need to see.

---

shahsank

Hi ,

Try below links ,

http://blogs.msdn.com/b/robertbruckner/archive/2010/05/02/report-design-shrinking-hidden-static-columns.aspx?wa=wsignin1.0

http://beyondrelational.com/modules/2/blogs/115/posts/11153/consumecontainerwhitespace-property-to-remove-blank-space-in-ssrs-2008-report.aspx

---

sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

Answer 7

Hi Charlie,

Thanks for reply,

Fot this i can use Hide/Show option on runtime, but on many forum i found that SSRS renders Hidden columns too. so that is my concern can you please let me know that. Does SSRS Renders Hidden Columns?

Thanks. 

---

shahsank