May 10, 2012 at 3:55 am
Hi,
I have an ssrs report in that i need the report name,current execution time of report and the report has executed previously means last execution time
has to display in the report can any one help .
🙂
May 10, 2012 at 4:11 am
USE Reportserver
go
MS ******/
SELECT TOP 1000 [HistoryID]
,[ReportID]
,[SnapshotDataID]
,[SnapshotDate]
FROM [ReportServer].[dbo].[History]
SELECT TOP 1000 [LogEntryId]
,[InstanceName]
,[ReportID]
,[UserName]
,[ExecutionId]
,[RequestType]
,[Format]
,[Parameters]
,[ReportAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo]
FROM [ReportServer].[dbo].[ExecutionLogStorage]
May 10, 2012 at 4:13 am
also this may help as well http://qa.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/69257/
May 10, 2012 at 4:16 am
Hi,
what is the use of the history table, in my db the history table is blank, it is not populated with data.
How to get the report_name and last_execution of the particular report.
🙂
May 10, 2012 at 4:25 am
this will get you what you want and a bit extra
its the data set for the ReportStatistics rdl which is ued in that diagnostic SSRS package I provided the link to
Set transaction isolation level read uncommitted
select x.itemID,
x.name,
x.path,
x.lastRunDate,
x.avgTimeDataRetrieval,
x.avgTimeProcessing,
x.avgTimeRendering,
x.avgTimeDataRetrieval + x.avgTimeProcessing + x.avgTimeRendering as avgTimeTotal,
x.avgRowCount,
lastStat.status,
x.rsSuccessY,
x.rsSuccessN,
(x.rsSuccessY*1.0) / (x.totalEntries*1.0) as rsSuccessYpct,
x.lastNonSuccess,
x.totalEntries
from (
select c.itemID,
c.name,
c.path,
max(e.timeStart) as lastRunDate,
avg(e.timeDataRetrieval) as avgTimeDataRetrieval,
avg(e.timeProcessing) as avgTimeProcessing,
avg(e.timeRendering) as avgTimeRendering,
avg(e.[rowCount]) as avgRowCount,
sum(case when e.status = 'rsSuccess' then 1 else 0 end) as rsSuccessY,
sum(case when e.status = 'rsSuccess' then 0 else 1 end) as rsSuccessN,
(select max(timeStart) from executionLog where reportID = c.itemID and status != 'rsSuccess' and timeStart >= dateAdd(mm,-6,getDate())) as lastNonSuccess,
count(c.itemID) as totalEntries
from executionLog e
inner join catalog c on e.reportID = c.itemID
where e.timeStart >= dateAdd(mm,-6,getDate())
group by c.itemID,
c.name,
c.path
) x
left join (
select reportID,
max(timeStart) lastRunDate,
[status]
from executionLog
group by reportID, [status]
) lastStat on x.itemID = lastStat.reportID and x.lastRunDate = lastStat.lastRunDate
May 10, 2012 at 4:33 am
Hi,
select MAX([TimeEnd]),Name
FROM [ReportServer].[dbo].[ExecutionLog] ,[ReportServer].[dbo].[Catalog]
where [ReportID]=[ItemID]
group by Name
will get enough information for me
but the concern is the latest report which i executed has a record entry in "ExecutionLog" table
but it is not in "catalog" table so when i try to join report_id=item_id it is showing older records becoz the report which was exected last has no record details in catalog table.
May 10, 2012 at 4:50 am
Does this do what you want?
SELECT
MAX(EL.TimeEnd)
,C.Name
FROM
ReportServer.dbo.ExecutionLog EL
INNER JOIN
ReportServer.dbo.Catalog C
ON
EL.ReportID = C.ItemID
GROUP BY
C.Name
May 10, 2012 at 5:52 am
hi,
i tried the same method but i am not getting the the record at all from that select becoz the catalog table has no record for the last executed report but in execution log table it was there.
May 10, 2012 at 5:54 am
are you looking at the correct reportserver DB to where the reports are stored, need to check via the ssrs configuration manager, as the only way an entry would remove from catalog is if the report is deleted or redployed under a different guid
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply