1.Add flag to see if a report is hidden or visible
SELECT
C.ItemID AS ReportID,
C.Name AS ReportName,
C.Path AS ReportPath,
CASE
WHEN C.Hidden = 1 THEN 'Hidden'
ELSE 'Visible'
END AS ReportVisibilityFlag
FROM Catalog C
WHERE C.Type = 2 -- Filter to select only reports (Type = 2)
2.Averege running time the report took in seconds incomp
SELECT
C.Name AS ReportName,
AVG(DATEDIFF(SECOND, EL.TimeStart, EL.TimeEnd)) AS AvgExecutionTimeInSeconds
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
WHERE EL.Status = 'rsSuccess' -- Filter for successful executions
GROUP BY C.Name
3.Add requestor column parsed out from the description column
SELECT
EL.TimeStart,
c.Path,
c.Description,
-- Extract the Requestor information from the Description column
SUBSTRING(c.Description, CHARINDEX('Requestor:', c.Description) + LEN('Requestor:'), LEN(c.Description)) AS Requestor
FROM ExecutionLog EL
full JOIN Catalog C ON EL.ReportID = C.ItemID
4.number of times report is cancel if it is high we can look into it
SELECT
C.Name AS ReportName,
COUNT(*) AS CancelledReportCount
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
WHERE EL.Status = 'rsExecutionCancelled'
GROUP BY C.Name
