SSRS Report Usage Statistics

Declare @FromDate as date,@ToDate as date

Set @FromDate=CONVERT(date,getdate())

Set @ToDate=CONVERT(date,getdate()+1)

 

SELECT

Cat.[Name] AS ReportName,

Cat.[Path] AS ReportPath,

Ex.[Format] AS ReportFormat,

Ex.[UserName] AS RunBy,

Ex.[TimeStart] AS RunDateTime,

CONVERT(nvarchar(10), Ex.[TimeStart], 101) AS RunDate, 

CASE(Ex.[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 [DataSource],

CASE(Ex.[RequestType])

WHEN 0 THEN 'Interactive'

WHEN 1 THEN 'Subscription'

WHEN 2 THEN 'Refresh Cache'

ELSE 'Unknown'

END AS RequestType,

1 AS [NumRuns],

Ex.[TimeDataRetrieval],

Ex.[TimeProcessing],

Ex.[TimeRendering],

Ex.[ByteCount],

Ex.[RowCount],

CASE cat.Type

WHEN 1 THEN 'Floder'

WHEN 2 THEN 'Report'

WHEN 3 THEN 'Resource'

WHEN 4 THEN 'Linked Report'

WHEN 5 THEN 'Data Source'

ELSE 'Unknown'

END AS [Catalog Type]

FROM ExecutionLog AS Ex INNER JOIN [Catalog] AS Cat ON (Ex.ReportID = Cat.ItemID)

WHERE Cat.Type = 2

AND Cat.[Path] NOT LIKE '%Report Components%'

AND Ex.[TimeStart] BETWEEN @FromDate AND @ToDate

ORDER BY Ex.TimeStart DESC