Querying Repository - Finding Scheduled Reports

I think we can all agree that the administration tools provided by Business Objects lack alot of functionality. Every week a question arises that can't be answered by anything other than a query with Query Builder.

A query that I often use is to find which reports are scheduled reports. This is what I use:

SELECT *
FROM CI_INFOOBJECTS
WHERE SI_RUNNABLE_OBJECT = 1

The SI_RUNNABLE_OBJECT = 1 means all reports that are scheduled (true).

Another useful query is to find reports which are scheduled to run between a certain period of time.

SELECT *
FROM CI_INFOOBJECTS
WHERE SI_NEXTRUNTIME BETWEEN '2008.01.01.08.00' AND '2008.01.10.13.00'

The format of the date/time is 'yyyy.mm.dd.hh.mm.ss'. I have omitted the seconds part. Anything from right to left can be removed. The hour format must be 24 hour. Note that the returned date/time is in 12 hour format.

To filter the results in any of the queries above you can use SI_KIND with any of the following object types (I have removed not applicable object types):

CrystalReport
Excel
FullClient
Pdf
Rtf
Txt
Webi
Word

Remember that if you use the SI_KIND on the query above you filter on the resulting instance object type, not the report type that the schedule is based on. E.g.: If you create a report i Crystal Report and you schedule it to run every day with Adobe Acrobat format you must use Pdf as filter.