Find all reports schedule with a certain custom calendar

[nscheaffer]

How do I find all reports scheduled with a specific custom calendar in my BI 4.0 SP5 platform?  I found one report that should have run on New Year's Eve (12/31/2012), but didn't due to that date not being specified as a run date in the custom calendar.  I am trying to determine what other scheduled reports also use that same custom calendar.

I realize I will probably have to resort to using Query Builder for this.  However, I have no idea what table(s) to use?  Any suggestions?  Is there a definitive guide to the tables accessible via Query Builder?

Thanks,

Noel

[former_member182521 · Accepted solution]

Noel,

Can we try this in Query builder.

SELECT SI_ID, SI_NAME,SI_KIND,SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,

SI_NEXTRUNTIME,SI_SCHEDULE_STATUS,SI_STATUSINFO

FROM CI_INFOOBJECTS

WHERE SI_SCHEDULE_TYPE in (8,9)  AND SI_SCHEDULE_STATUS = 9

FYI, I have added the below hints with reference to report schedule information.

------------------------------------------------------------------------------------------------

SI_SCHEDULE_TYPE

0 – Once: the report is run once only

1 – Hourly: the report is run hourly.

2 – Daily: the report is run daily.

3 – Weekly: the report is run weekly.

4 – Monthly: the report is run monthly.

5 – NthDay: the report is run on the nth day of the month.

6 – 1stMonday: the report will run on the first monday of every month.

7 – LastDay: the report will run on the last day of the month.

8 – Calendar: the report will run based on calendar templates.

9 – Run schedule based on calendar plugin object templates.

SI_SCHEDULE_STATUS

0 – Running

1 – Success

3 – Failure

8 – Paused

9 – Pending

----------------------------------------------------------------------------------------------------------------------

Give a try and let me know if this works for you.

Regards,

Mani

[nscheaffer · Accepted solution]

That is great.

I had to add the "SI_SCHEDULEINFO" qualifier to SI_SCHEDULE_TYPE (highlighted below).  I also plugged in the ID of the calendar I was interested in.  So here is my final query.

SELECT SI_ID, SI_NAME,SI_KIND,SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,

SI_NEXTRUNTIME,SI_SCHEDULE_STATUS,SI_STATUSINFO

FROM CI_INFOOBJECTS

WHERE SI_SCHEDULEINFO.SI_SCHEDULE_TYPE in (8,9)

  AND SI_SCHEDULE_STATUS = 9

  AND SI_SCHEDULEINFO.SI_CALENDAR_TEMPLATE_ID = 57952

Thank you!

[nscheaffer · Accepted solution]

Mani,

Since you obviously have experience with Query Builder can you take a look at another question of mine related to Query Builder?

http://scn.sap.com/thread/3292447

Thanks,

Noel