Get Report Execution Detail

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