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
|
 Updating... usp_GetReportExecutionDetail.sql (3k) Andy Hughes, Jul 28, 2012, 10:21 AM
|
