Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3276
  • Last Modified:

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

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
rpkhare
Asked:
rpkhare
1 Solution
 
liijaCommented:
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
 
rpkhareAuthor Commented:
@ralmada:

I already tried that solution but it is not working.
0
 
liijaCommented:
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
 
ValentinoVBI ConsultantCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now