USE [ReportServer]
GO
/*
Query 1:
Specify report name looking for and list of parameters for report with timings in milliseconds
ordered by the total number of times parameter selection made in report (occurrences)
*/
DECLARE @ReportName nvarchar(200) = '%REPORTNAME%' --Set Report name
DECLARE @OccurancesOver int = 5 --Set occurance level
DECLARE @PeriodRange datetime = dateadd(month,-1,getdate()) --Last month
SELECT
[ItemPath]
,[Parameters]=replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')
,[Status]
,TotalOccurances = count(*)
,TimeDataRetrieval=sum([TimeDataRetrieval])
,TimeProcessing=sum([TimeProcessing])
,TimeRendering=sum([TimeRendering])
,TotalTime=sum([TimeDataRetrieval])+sum([TimeProcessing])+sum([TimeRendering])
FROM [dbo].[ExecutionLog3]
WHERE ItemPath like @ReportName
AND [Source] = 'Live' --Live queries only not already caching
AND [Status] = 'rsSuccess' -- ONLY successful
AND [TimeStart] >= @PeriodRange
GROUP BY
[ItemPath]
,replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')
,[Status]
HAVING count(*) >= @OccurancesOver
ORDER BY count(*) desc
/*
Query 2:
List all subscriptions and status
*/
SELECT
[Description]
,[EventType]
,[LastStatus]
,[LastRunTime]
FROM [dbo].[Subscriptions]
WHERE [Description] <> ''
order by [LastRunTime] desc
