Scheduling Reports

There are many reasons why you would want to use the Crystal Enterprise scheduler. The most obvious case is when you want to run a report at a reoccurring time and make it available for a large number of users. However, the value of the scheduler is more than just time-based report processing. Often, the scheduler can be used for efficiency reasons. Being able to schedule a report once while incurring a single hit to the database, but allowing a large number of users to view the report instance, is a powerful thing. It decreases the number of queries to the database, which could affect the number of database licenses required. At the same time, because a cached copy of the data is stored with the report instance, the performance of viewing the report instance is much better than viewing the report in an on-demand mode. When you understand this basic scheduling principle and the Crystal Enterprise scheduling API, you can create a highly efficient and effective reporting system. This section provides that scheduling API knowledge.

Because the Crystal Enterprise SDK is a unified object model, its not surprising to learn that the scheduling API is built into the core object model. Scheduling a report is pretty straightforward; you simply call the schedule method of the IInfoStore interface, passing in a collection of reports to be scheduled. Youll remember from the previous chapter that reports are stored as objects inside Crystal Enterprise. They are accessed via the IInfoObject interface. Multiple objects are stored in the IInfoObjects collection. Therefore the schedule method takes an IInfoObjects collection as a single argument. For most cases, this collection only contains a single object; however, its possible to schedule a batch of reports at the same time.

The process for scheduling a report is generally a three-step process. First, the reports corresponding IInfoObject interface needs to be retrieved, next the scheduling information needs to be filled into the object, and finally the schedule method needs to be called. Chapter 34 described how to retrieve an objects IInfoStore interface, but as a review, the following code snippet illustrates querying the InfoStore for a report object:

IInfoStore iStore = (IInfoStore) ceSession.getService("InfoStore"); IInfoObjects results = iStore.query("SELECT * FROM CI_INFOOBJECTS WHERE " + "SI_NAME=World Sales Report AND SI_INSTANCE=0"); IInfoObject report = (IInfoObject) results.get(0);

This code uses SI_INSTANCE=0 in the WHERE clause of the query to ensure that the report object and not the report instance is brought back. This is important when you start scheduling reports because the report instance has the same report name as the report object, so querying by name does not necessarily give you the object you are looking for.

After an IInfoObject is obtained, its getSchedulingInfo method should be called to obtain an ISchedulingInfo interface. Use this interface to define the settings for the scheduled job. At a high level, these settings are as follows:

• The date and time for the scheduled job
• If and how the job should reoccur
• Any events that job should wait for or fire
• Which format the job should output to
• Notification settings
• A server group preference

The following sections cover these settings in detail.

Working with Schedule Date and Times

The most obvious attribute of a scheduled job is when that job should execute. The job can be either set to run immediately or at a predetermined date and time. Setting a job to run immediately is as simple as calling the setRightNow method of the ISchedulingInfo interface. The following code snippet shows a report scheduled to run immediately:

IInfoObjects results = iStore.query("SELECT SI_ID FROM CI_INFOOBJECTS WHERE " + "SI_NAME=World Sales Report AND SI_INSTANCE=0"); IInfoObject report = (IInfoObject) results.get(0); ISchedulingInfo sched = report.getSchedulingInfo(); sched.setRightNow(true); iStore.schedule(results);

Because there is no immediate feedback that the report has been scheduled, open the reports properties in the Crystal Management Console and click the History tab. Here you can see the reports scheduling history, specifically the exact time the report was scheduled and its completion status. This provides the verification that the code actually scheduled the report properly. This is shown in Figure 35.1.

Scheduling a report to run immediately might seem like a useless task. Some people might wonder why the report would not just be viewed on-demand. There are sometimes limitations around the size and complexity of reports that you want to have viewed on-demand; often the browser or application server will timeout the HTTP request because the page server took longer than required to bring back the results of the report. In this case, a common feature for developers to implement is something colloquially referred to as "fake on-demand." In a fake on-demand scenario, when the user chooses to view a report, the script actually schedules the report for the user and either provides an auto refreshing page that polls for the completion of the report, or provides the capability to e-mail the report to the user when completed.

For the traditional schedule scenario, you want to specify when the job should run. This is done via a standard Java Date object. Keep in mind that the date and time are relative to whichever time zone the Crystal Enterprise servers are running on, which could be different from an end users time zone in a geographically distributed Crystal Enterprise implementation. To set the schedule date and time, pass a Date object to the setBeginDate method of the ISchedulingInfo interface as shown in the following code snippet:

ISchedulingInfo sched = report.getSchedulingInfo(); SimpleDateFormat dateFormat = new SimpleDateFormat(); Date beginDate = dateFormat.parse("10/01/2004 07:00 AM"); sched.setBeginDate(beginDate); iStore.schedule(results);

Although this example might seem fairly simple, a developer could use the scheduling API to create a complex scheduling interface like the one found in the Crystal Management Console. This is shown in Figure 35.2.

Creating Reoccurring Schedules

Sometimes a report needs to be scheduled as a one-time event, but more commonly, a report needs to be scheduled at a set date and time, and then reoccur at some interval thereafter. The Crystal Enterprise scheduler supports the basic reoccurrences daily, hourly, weekly, and monthly. In addition it provides some advanced occurrences such as Nth day, first Monday of the month, or last day of the month. Finally, it enables administrators to define a business calendar that is a custom daily schedule based on some business rule. There are several methods used to define the schedule reoccurrence. The first method that must be called is the ISchedulingInfo interfaces setType method. This accepts a CeScheduleType enumeration value. The default value is CeScheduleType.ONCE. To set a reoccurring schedule, pass in one of the following values:

• CeScheduleType.DAILY (daily reoccurrence)
• CeScheduleType.HOURLY (hourly reoccurrence)
• CeScheduleType.WEEKLY (weekly reoccurrence)
• CeScheduleType.MONTHLY (monthly reoccurrence)
• CeScheduleType.NTH_DAY (monthly reoccurrence on the Nth day of the month)
• CeScheduleType.FIRST_MONDAY (monthly reoccurrence on the first Monday of the month)
• CeScheduleType.LAST_DAY (monthly reoccurrence on the last day of the month)
• CeScheduleType.CALENDAR (a customized list of days)
• CeScheduleType.CALENDAR_TEMPLATE (a predefined list of days stored in a business calendar template)

After you set the type of reoccurrence, depending on which type you used, there are several methods you can use to define the intervals. The simplest scenario is a daily reoccurrence. To indicate which day to begin and what time of day to run the report, call the setBeginDate method. To set the date to end the daily schedule, call the setEndDate method. The following code snippet shows a report being scheduled each day at 8:00 a.m. from June 1st to June 15th:

ISchedulingInfo sched = report.getSchedulingInfo(); sched.setType(CeScheduleType.DAILY); SimpleDateFormat dateFormat = new SimpleDateFormat(); Date beginDate = dateFormat.parse("06/01/2004 08:00 AM"); Date endDate = dateFormat.parse("06/15/2004 08:00 AM"); sched.setBeginDate(beginDate); sched.setEndDate(endDate); iStore.schedule(results);

This is a simple example because only the start and end dates need to be specified. In the following example, the report is scheduled hourly:

ISchedulingInfo sched = report.getSchedulingInfo(); sched.setType(CeScheduleType.HOURLY); SimpleDateFormat dateFormat = new SimpleDateFormat(); Date beginDate = dateFormat.parse("06/01/2004 12:00 AM"); Date endDate = dateFormat.parse("06/15/2004 12:00 AM"); sched.setBeginDate(beginDate); sched.setEndDate(endDate); sched.setIntervalHours(1); iStore.schedule(results);

In this case, in addition to the start and end dates, you should set an interval that indicates how many hours should pass in between the scheduled jobs. In the previous example, the report is scheduled every six hours.

When working with weekly schedules, its assumed that the report runs the first day of the week at the specified time. This can be done by simply setting the schedule type to WEEKLY and providing a start and end date as follows:

ISchedulingInfo sched = report.getSchedulingInfo(); sched.setType(CeScheduleType.WEEKLY); SimpleDateFormat dateFormat = new SimpleDateFormat(); Date beginDate = dateFormat.parse("01/01/2005 08:00 AM"); Date endDate = dateFormat.parse("01/30/2005 08:00 AM"); sched.setBeginDate(beginDate); sched.setEndDate(endDate); iStore.schedule(results);

The other way to do a weekly schedule is to specify individual days of the week to run the report. In this case, even though its a weekly-type schedule, you need to define a custom schedule by setting the schedule type to CALENDAR. When using the calendar type, you need to call the getCalendarRunDays method of the ISchedulingInfo interface to obtain the ICalendarRunDays interface. Through this interface you can add any day combination you want. It has an add method that accepts eight arguments indicating day, week, month, and so on. In this case, only the day of week argument is used so set the rest to -1. The following example schedules the report every Monday, Wednesday, and Friday from January 1st to 30th:

ISchedulingInfo sched = report.getSchedulingInfo(); sched.setType(CeScheduleType.CALENDAR); ICalendarRunDays days = sched.getCalendarRunDays(); days.add(-1, -1, -1, -1, -1, -1, 2, -1); days.add(-1, -1, -1, -1, -1, -1, 4, -1); days.add(-1, -1, -1, -1, -1, -1, 6, -1); SimpleDateFormat dateFormat = new SimpleDateFormat(); Date beginDate = dateFormat.parse("01/01/2005 08:00 AM"); Date endDate = dateFormat.parse("01/30/2005 08:00 AM"); sched.setBeginDate(beginDate); sched.setEndDate(endDate); iStore.schedule(results);

Working with monthly schedules is fairly simple. First, set the schedule type to MONTHLY and provide start and end dates, and optionally call the setIntervalMonths method to indicate that the report job should be run every Nth month.

Finally, working with business calendars is quite simple. The real work is in creating the calendar, but luckily this is a task that can be performed visually through the Crystal Management Console shown in Figure 35.3. Business calendars are stored as InfoObjects just like everything else in the Crystal Enterprise repository. However, business calendars are stored in the CI_SYSTEMOBJECTS table instead of the CI_INFOOBJECTS table. To create a scheduled job based on a business calendar, first look up the ID of the business calendar object, and then call the setCalendarTemplate method of the ISchedulingInfo interface as shown in the following code:

IInfoObjects calResults = iStore.query("SELECT SI_ID FROM CI_SYSTEMOBJECTS " + "WHERE SI_NAME=Each Quarter End"); IInfoObject cal = (IInfoObject) calResults.get(0); ISchedulingInfo sched = report.getSchedulingInfo(); sched.setType(CeScheduleType.CALENDAR_TEMPLATE); sched.setCalendarTemplate(cal.getID()); iStore.schedule(results) ;