Solved

How to list columns of a Dataset in SSRS which uses dynamic query in stored procedure?

Posted on 2013-01-23
6
2,846 Views
Last Modified: 2013-02-09
I am using SQL Server Reporting Services 2008.

I have a stored procedure which uses dynamic query to pivot dates as columns. These can be n number of columns.

I have a DataSet in SSRS which is bound to the above stored procedure. Since the stored procedure query has no fixed columns, this DataSet is not listing fields.

How to list all the fields from this stored procedure?

My dynamic query looks somewhat like this:

IF OBJECT_ID('tempdb..#Results',N'U') IS NOT NULL
DROP TABLE #Results

SELECT EMP.EMP_ID, EMP.EMP_NAME, T.tempStoredDate, AD.AttendanceDate, AD.AttendanceStatus
INTO #Results
FROM T
LEFT OUTER JOIN Employee EMP
ON T.UserID = EMP.Emp_ID
LEFT OUTER JOIN Attendance AD
ON EMP.EMP_ID = AD.EMP_ID
GROUP BY T.tempStoredDate, EMP.EMP_ID, EMP.EMP_NAME, AD.AttendanceDate, AD.AttendanceStatus

declare @SQL nvarchar(max), @Cols nvarchar(max)

select @Cols = stuff((select ', ' + quotename(dt)
from (select top (15) DISTINCT convert(varchar(8), AttendanceDate,112)
as dt from #Results ORDER BY convert(varchar(8), AttendanceDate,112)) X
ORDER BY dt FOR XML PATH('')),1,2,'')

set @SQL = 'SELECT * FROM #Results
PIVOT (max(AttendanceStatus) FOR AttendanceDate IN (' + @Cols
+')) pvt'

execute (@SQL)

Open in new window

0
Comment
Question by:rpkhare
6 Comments
 
LVL 6

Expert Comment

by:liija
ID: 38811298
I don't think this is possible. SSRS requires fixed columns and fixed names for them.
How does the final repprt look like? There could be some other way to get what you need here. Are you using Matrix in SSRS?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38811310
0
 
LVL 8

Author Comment

by:rpkhare
ID: 38813159
@ralmada:

I already tried that solution but it is not working.
0
 
LVL 6

Expert Comment

by:liija
ID: 38813207
That solution would work if you had fixed columns in the SP. Since you have dynamic column quantity and names, it won't work.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 38813350
As you've discovered, SSRS and dynamic datasets don't combine nicely.  If you don't know what the columns will be called while writing the report, such as in your case where they will be dates, I don't think it will be possible to get it working in this way.

However, do you really need to pivot the data in the stored procedure?  Wouldn't it be an option to return it unpivoted and then use the Matrix with AttendanceDate in the Columns?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question