locked
Dynamic Columns Based on Parameters RRS feed

  • Question

  • I am trying to use SSRS to allow users to select any one or several columns from a set of cascading parameters which will then do a "data dump" of the contents of the selected columns for "export".

    I know how to do cascading parameters, but I am having problems coming up with a way of using the parameters to dynamically build a matrix which has as its columns the list selected in the parameters.

    NOTE:  I need the user to choose WHICH columns will be displayed.  There could be 1, 2, 3, up to 50 columns.

    Any suggestions?


    Friday, August 21, 2015 12:26 PM

Answers

  • This is tricky.

    Here's one way:

    Add a dataset to your report to drive the parameter. Use this query, replacing with your table name:

    SELECT c.name
      FROM sys.columns c
        INNER JOIN sys.tables t
          ON c.object_id = t.object_id
          AND t.name = 'myTableName'

    Now, create a parameter called @Columns. Set its available values to name from that dataset. Allow multiple values.

    Now, create another data set using this query, again replacing myTableName with yours.

    DECLARE @sColumns NVARCHAR(MAX)
    
    DECLARE @holder TABLE (colName SYSNAME, seq INT)
    INSERT INTO @holder
    SELECT value, ID
      FROM dbo.splitterMkII(@Columns,',')
    
    ;WITH rCTE AS (
    SELECT CAST('CAST('+colName+' AS NVARCHAR) AS '+colName AS NVARCHAR(MAX)) AS colName, seq
      FROM @holder
     WHERE seq = 1
    UNION ALL
    SELECT a.colName + ', CAST('+r.colName+' AS NVARCHAR) AS '+r.colName, r.seq
      FROM rCTE a
        INNER JOIN @holder r
    	  ON a.seq + 1 = r.seq
    )
    
    SELECT @sColumns = colName
      FROM rCTE
     WHERE seq = (SELECT MAX(seq) FROM rCTE)
    
    
    DECLARE @dSQL NVARCHAR(MAX)
    SET @dSQL = 'SELECT x,y, row FROM (SELECT '+@sColumns+', ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row FROM myTableName) c UNPIVOT ( x for y IN ('+@Columns+') ) p'
    
    EXEC sp_executeSQL @dSQL

    This will do the hard work for you. You're parameter is inserted into it, and processed to set up for a matrix.

    Create a matrix in the report. Put row in the left column, y in the header row, and x in the values.

    Run and enjoy.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Friday, August 21, 2015 3:45 PM

All replies

  • Hi GKFKey,

    Please check the below link and this is what you are looking for :)

    http://sql-bi-dev.blogspot.in/2010/10/displaying-dynamic-columns-in-ssrs.html


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Friday, August 21, 2015 12:40 PM
  • This is tricky.

    Here's one way:

    Add a dataset to your report to drive the parameter. Use this query, replacing with your table name:

    SELECT c.name
      FROM sys.columns c
        INNER JOIN sys.tables t
          ON c.object_id = t.object_id
          AND t.name = 'myTableName'

    Now, create a parameter called @Columns. Set its available values to name from that dataset. Allow multiple values.

    Now, create another data set using this query, again replacing myTableName with yours.

    DECLARE @sColumns NVARCHAR(MAX)
    
    DECLARE @holder TABLE (colName SYSNAME, seq INT)
    INSERT INTO @holder
    SELECT value, ID
      FROM dbo.splitterMkII(@Columns,',')
    
    ;WITH rCTE AS (
    SELECT CAST('CAST('+colName+' AS NVARCHAR) AS '+colName AS NVARCHAR(MAX)) AS colName, seq
      FROM @holder
     WHERE seq = 1
    UNION ALL
    SELECT a.colName + ', CAST('+r.colName+' AS NVARCHAR) AS '+r.colName, r.seq
      FROM rCTE a
        INNER JOIN @holder r
    	  ON a.seq + 1 = r.seq
    )
    
    SELECT @sColumns = colName
      FROM rCTE
     WHERE seq = (SELECT MAX(seq) FROM rCTE)
    
    
    DECLARE @dSQL NVARCHAR(MAX)
    SET @dSQL = 'SELECT x,y, row FROM (SELECT '+@sColumns+', ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row FROM myTableName) c UNPIVOT ( x for y IN ('+@Columns+') ) p'
    
    EXEC sp_executeSQL @dSQL

    This will do the hard work for you. You're parameter is inserted into it, and processed to set up for a matrix.

    Create a matrix in the report. Put row in the left column, y in the header row, and x in the values.

    Run and enjoy.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Friday, August 21, 2015 3:45 PM