Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-23
6
Medium Priority
?
3,206 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 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 1500 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

916 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