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...
Andy Hughes, Jul 28, 2012, 10:21 AM
|