I created a query where it contains ID, Email, Class and a dynamic column which changes or grow depending on the parameter I select.
This is my query:
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + Session + ']',
'[' + Session + ']')
WHERE Session = @Session
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT ID, Email, '+
@cols + ',class
Session IN (''' + @Session +''')
FOR Session IN
@cols +' )
) AS pvt'
The query run fine when I run it in management studio where the column header changes based on the session parameter I select. For example, If i pass thru H for session then the column returned would be ID, Email, H and class or if i pass thru A then the column heading H will change to A and the associated data.
I put this query into reporting services and created a table and drag the available field in and I lose the dynamic column field feature, can anyone assist me to fix this please.