I was asked today for a report that I had never been asked for before - a list of all reports that are scheduled in the system. This would provide a means of monitoring what reports are scheduled.
After some playing around I came up with the attached report. Here is the SQL as well:
Code:
SELECT
GUID as _ResourceGuid,
Name as 'Report Name',
Description,
SUBSTRING(state,(PATINDEX('%Description="%',state) + 13),(PATINDEX('%"><BeginDate>%',state) - (PATINDEX('%Description="%',state) + 13))) as Schedule
FROM item
WHERE state like '%<enabled>True</enabled><schedule%'
AND classguid = '67033A4E-A848-4A0B-B037-AB02854A315A'
AND Name != 'All Scheduled Reports'
UNION ALL
SELECT
it.GUID as _ResourceGuid,
rep.Name as 'Report Name',
rep.Description,
SUBSTRING(it.state,(PATINDEX('%Description="%',it.state) + 13),(PATINDEX('%"><BeginDate>%',it.state) - (PATINDEX('%Description="%',it.state) + 13))) as Schedule
FROM itemschedule sch
JOIN item rep on rep.guid = sch.ItemGuid
JOIN item it on it.guid = sch.scheduleguid
WHERE rep.classguid = '67033A4E-A848-4A0B-B037-AB02854A315A'
EDIT: After sleeping on it I looked the report over and thought it would be good to be able to go directly to a report in the results to modify the schedule. I've added GUID to the SELECT statement so this is allowed.
Also, I should mention that I named this report 'All Scheduled Reports'. If you name it something else, update the line for "AND NAME != 'All Scheduled Reports'". This was a quick and dirty way from excluding this report from showing in the results.
Linear Mode
