Solved

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

Posted on 2013-01-23
6
2,804 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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