Get Report Snapshot History

DROP PROC usp_GetReportSnapshotHistory

GO

CREATE PROC usp_GetReportSnapshotHistory @ReportName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retrieve a list of snapshot history for one or

many reports.

Uses the Catalog, Schedule, ReportSchedule and SnapshotData tables

in the report server database (assumed to be called by its default

name of 'ReportServer')

Author: Sorna Kumar Muthuraj

History: 9 Jul 2011 - Initial Issue

*****************************************************/

SELECT

c.itemid AS ReportID,

c.name AS ReportName,

c.path AS ReportPath,

CONVERT(VARCHAR(20), h.snapshotdate, 113) AS SnaphsotDate,

s.DESCRIPTION AS SnapshotDescription,

s.effectiveparams AS SnapshotEffectiveParams,

s.queryparams AS SnapshotQueryParams,

sc.name AS ScheduleName,

CONVERT(VARCHAR(20), sc.nextruntime, 113) AS ScheduleNextRunTime

FROM ReportServer.dbo.History h (NOLOCK)

INNER JOIN ReportServer.dbo.SnapshotData s (NOLOCK)

ON h.snapshotdataid = s.snapshotdataid

INNER JOIN ReportServer.dbo.Catalog c (NOLOCK)

ON c.itemid = h.reportid

INNER JOIN ReportServer.dbo.ReportSchedule rs (NOLOCK)

ON rs.reportid = h.reportid

INNER JOIN ReportServer.dbo.Schedule sc (NOLOCK)

ON sc.scheduleid = rs.scheduleid

WHERE rs.reportaction = 2 -- Create schedule

AND c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'

ORDER BY

c.name,

h.snapshotdate