none
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

    Saturday, August 17, 2013 4:59 AM

Answers

All replies

  • 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

    Saturday, August 17, 2013 6:24 AM
  • 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

    Saturday, August 17, 2013 6:35 AM
  • 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  &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 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.


    Sunday, August 18, 2013 8:03 AM
    Moderator
  • 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

    Monday, August 19, 2013 4:27 AM
  • 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

    Wednesday, August 21, 2013 1:47 AM
    Moderator
  • 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.

    Wednesday, August 21, 2013 5:06 PM
    Moderator
  • 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

    Friday, September 13, 2013 6:28 AM