Solved

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

Posted on 2013-01-23
6
2,919 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

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 …
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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