SSRS Usage Part2

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 incompSELECT C.Name AS ReportName, AVG(DATEDIFF(SECOND, EL.TimeStart, EL.TimeEnd)) AS AvgExecutionTimeInSecondsFROM ExecutionLog ELINNER JOIN Catalog C ON EL.ReportID = C.ItemIDWHERE EL.Status = 'rsSuccess' -- Filter for successful executionsGROUP BY C.Name

3.Add requestor column parsed out from the description columnSELECT 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 RequestorFROM ExecutionLog ELfull 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 CancelledReportCountFROM ExecutionLog ELINNER JOIN Catalog C ON EL.ReportID = C.ItemIDWHERE EL.Status = 'rsExecutionCancelled'GROUP BY C.Name