Report and SSRS - Get Report Execution Detail

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 201

Description

Retrieves a list of executions for one or many reports.  Includes parameters and execution performance data.

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

Code

Stored Procedure:


DROP PROC usp_GetReportExecutionDetail
GO

CREATE PROC usp_GetReportExecutionDetail @ReportName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retrieve a list of executions for one or
           many reports.  Includes parameters and execution performance data.
          
           Uses the Catalog, and ExecutionLogStorage tables
           in the report server database (assumed to be called by its default
           name of 'ReportServer')
          
           NOTE:  There is a vew ExecutionLog which has different versions across the more recent
           SQL Versions (ie ExecutionLog, ExecutionLog2 and ExecutionLog3) that add more friendly
           values to some of the data field content.
           Rather than code different SPs for these, this SP uses the unterlying table directly
          
Author:        Sorna Kumar Muthuraj
History:   9 Jul 2011 - Initial Issue
           27 Jul 2012 - ChillyDBA - converted to SP and extended to include a wider range of returned data
           with conversions of some lookup IDs to their text label equivalents
          
*****************************************************/

SELECT
  
c.ItemID                                    AS ReportID,
  
c.Name                                      AS ReportName,
  
c.Path                                      AS ReportPath,      
  
CASE e.RequestType
      
WHEN 1 THEN 'Subscription'
      
WHEN 0 THEN 'Report Launch'
      
ELSE ''
  
END                                         AS ExecutionType,
  
e.InstanceName                              AS SQLInstanceName,      
  
e.UserName                                  AS UserName,    
  
e.Format                                    AS ReportFormat,
          
    
CASE(ReportAction)    
        
WHEN 1 THEN 'Render'
        
WHEN 2 THEN 'BookmarkNavigation'
        
WHEN 3 THEN 'DocumentMapNavigation'
        
WHEN 4 THEN 'DrillThrough'
        
WHEN 5 THEN 'FindString'
        
WHEN 6 THEN 'GetDocumentMap'
        
WHEN 7 THEN 'Toggle'
        
WHEN 8 THEN 'Sort'
        
WHEN 9 THEN 'Execute'
        
ELSE 'Unknown'
        
END                                    AS ReportAction,
        
  
e.Parameters                                AS ReportParameters,    
  
CONVERT(VARCHAR(20), e.TimeStart, 113)      AS ExecutionTimeStart,      
  
CONVERT(VARCHAR(20), e.TimeEnd, 113)        AS ExecutionTimeEnd,    
  
e.TimeDataRetrieval                         AS ExecutionDataRetrievalTime,      
  
e.TimeProcessing                            AS ExecutionProcessingTime,      
  
e.TimeRendering                             AS ExecutionRenderingTime,
          
    
CASE e.Source
        
WHEN 1 THEN 'Live'
        
WHEN 2 THEN 'Cache'
        
WHEN 3 THEN 'Snapshot'
        
WHEN 4 THEN 'History'
        
WHEN 5 THEN 'AdHoc'
        
WHEN 6 THEN 'Session'
        
WHEN 7 THEN 'Rdce'
        
ELSE 'Unknown'
    
END                                        AS ExecutionSource,
    
  
e.Status                                    AS ExecutionStatus,      
  
e.ByteCount                                 AS ReportByteCount,      
  
e.[RowCount]                                AS ReportRowCount,
  
e.AdditionalInfo                            AS AdditionalInfo -- context-dependant XML content

FROM ReportServer.dbo.ExecutionLogStorage e (NOLOCK)
INNER JOIN ReportServer.dbo.Catalog c (NOLOCK)
  
ON e.reportid = c.itemid
WHERE c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'
ORDER BY
  
c.Name,
  
e.TimeStart

ċ
usp_GetReportExecutionDetail.sql
(3k)
Andy Hughes,
Jul 28, 2012, 10:21 AM
Comments