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?

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

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.