Reports and SSRS - Get Report Snapshot History

Applicability:

                 SQL Server 2000:        Not Tested        
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Sorna Kumar Muthuraj
Date:        9 Jul 2011

Description

Retrieves a list of snapshot history for one or many reports.


NOTE:  It is assumed that the Report Server Database has its default name of 'ReportServer'

Code

Stored Procedure:


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

ċ
usp_GetReportSnapshotHistory.sql
(2k)
Andy Hughes,
Jul 28, 2012, 10:13 AM
Comments