May 13, 2009 at 8:55 am
Can I use temp table in SSRS?
Is it a valid SQL to use for a DataSet?
SELECT
IDENTITY(int, 1, 1) AS RowNumber,
ExecutionLog.UserName,count(ExecutionLog.UserName) as HowManyReportsRan
INTO
#t1
FROM
Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
ExecutionLog.UserName NOT IN ('NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
AND(NOT (Catalog.Path LIKE N'/Users Folder/%'))
AND
(
(Catalog.Path LIKE N'/Market Risk%')
OR
(Catalog.Path LIKE N'/Business Management%')
OR
(Catalog.Path LIKE N'/Liquidity Risk%')
)
AND
ExecutionLog.UserName NOT IN ('NA\Zaw','NA\Leun','NA\Ope')
group by ExecutionLog.UserName
--having
order by HowManyReportsRan desc
select RowNumber,UserName,HowManyReportsRan from #t1
I can execute this in Data tab
but Deploy fails with this error:
Error1[rsFieldReference] The Value expression for the textbox ‘textbox40’ refers to the field ‘RowNumber’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.h:\visual studio 2005\projects\report_statistics\report_statistics\DocumentMapSample.rdl00
May 13, 2009 at 8:58 am
You need to start with actual table and pass the data to a temp table so SSRS have a base table to call, and it is better if you make your call to the temp table short or use global temp table ##.
Kind regards,
Gift Peddie
May 13, 2009 at 9:04 am
I have a physical table:
ReportServer.Catalaog
ReportServer.ExecutionLog
But I'd like to display the IDENTITY RowNumber.
You can do it only using "SELECT INTO".
So I don't have a choice. I have to INSERT it into some #t1
and then I would like to "SELECT * FROM #t1".
Will Global temporary table solve my problem?
May 13, 2009 at 9:12 am
use the row_number function instead
SELECT
row_number() over (order by ExecutionLog.UserName) AS RowNumber,
ExecutionLog.UserName,count(ExecutionLog.UserName) as HowManyReportsRan
FROM
Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
ExecutionLog.UserName NOT IN ('NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
AND(NOT (Catalog.Path LIKE N'/Users Folder/%'))
AND
(
(Catalog.Path LIKE N'/Market Risk%')
OR
(Catalog.Path LIKE N'/Business Management%')
OR
(Catalog.Path LIKE N'/Liquidity Risk%')
)
AND
ExecutionLog.UserName NOT IN ('NA\Zaw','NA\Leun','NA\Ope')
group by ExecutionLog.UserName
--having
order by HowManyReportsRan desc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply