Chapter 13. Scheduling of Reports

ReportServer supports the timed execution of reports. In this section we will discuss how to view existing jobs with the administration interface as well as how to configure conditional schedule jobs. For a description of how to configure the e-mail server as well as the notifications about completed (or failed) executions refer to the ReportServer configuration guide.

The Scheduling module enables the user to browse their own jobs, to change or archive them. In addition, the module offers a variety of information on each job such as given times for next executions, or information on possible errors in previous executions. A detailed description on how to work with the module is given in the ReportServer user manual in section Scheduling.

The Administrator can call up an extended form of the Scheduling module via the Administration module. Here you see the jobs of all users, and, if required, may change or remove them. Please consider that after having changed a scheduler job, your user account will be entered in Scheduled by, and the user who originally created the job cannot edit it afterwards any more if the user is not an owner.

During Scheduling three different Scheduling Actors are important:

The executor	The executor is the user as which the report will be executed. Thus, this determines the permissions and the data available during report execution. Allowing to change the executor is useful e.g. if the original executor leaves the organization: the scheduler administrator is able to simply change the executor by a couple of clicks. The executor may only be changed by the Scheduler Administrator.
The owners	The owners are users that are able to change a scheduled job. The job owners may change the complete job definition, but they can not change the job executor if they are not Scheduler Administrators. Allowing a job to have more than one owner is useful if the original job owner is out of office for some weeks, e.g. in vacation, and the job has to be changed by someone else during this time. Defining multiple owners allows all owners to change a job without having to give them Scheduler Administrator permissions.
The recipients	Recipients are the users that get the scheduled report e.g. by e-mail.

In the following we want to give a few technical details on scheduler job (or entries). A scheduler entry consists of a so-called job and one or more actions each. Here the job describes how to execute the report (further jobs could be the time controlled execution of scripts; refer to section "Scheduling of reports"), whereas actions describe what to do with the result (the completed report). At present, there are two options: Sending by e-mail or storing the result in a TeamSpace for later retrieval. Often instead of speaking of a scheduler entry (i.e., job plus actions) we simply speak of a scheduler job.

Errors may occur here on all levels. Running a job just as well as running individual actions might fail. If a report is not executable (for example, because the underlying data model has changed), the job cannot be executed. An example for a possible failure on the action level is that the e-mail server cannot be reached which would interrupt the sending of report results.

To display detailed information on successful or failed scheduler entries, from the list (on the left) select the respective entry and then double click in the details pane on a specific execution. In the pop-up window that opens, you will be given detailed information on the execution split up in job and actions. If there is a failure here you will be given a stack trace of the execution to draw conclusions as to what might be the failure cause.

If, for instance, the connection to the datasource is not available at the moment of execution, ReportServer will produce an error message similar to the following one:

net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: The
report could not be executed: Could not open connection to: jdbc:mysql://demo.db.raas.
datenwerke.net:3306/ClassicModels with user: demo. java.sql.SQLException: Timed out 
waiting for a free available connection.

From the filters in the tool bar you can search for a specific job by its failure status. ReportServer jobs are always in one of 4 statuses.

Inactive	The job is currently inactive If another execution at a later time is pending, the job will be selected at that time by the disposition module and prepared for execution.
Waiting to be executed	If a job has been selected by the scheduler for execution, it will change to the status "Waiting to be executed". It will remain in this status until a free worker thread will start the execution.
Executing	The job is being executed.
Critical failure	This status will be set if an unforeseeable error occurred that requires manual action. The job will be exempt from further execution until the status is manually restored. To reset the status to "Inactive", click on the respective job and in Details double click on the field Execution status.

After a scheduler job was executed ReportServer sends a notification by e-mail. If the report was scheduled into a TeamSpace, you can add a link to the completed report. Configure notification texts in the configuration file etc/scheduler/scheduler.cf. For further information on the configuration refer to the ReportServer configuration guide.

The terminal allows you to control the scheduler, e.g. to manually terminate or restart it. For further information refer to Section 19.58. (command scheduler).

Beside regular scheduling of reports, ReportServer also supports conditional scheduling of reports. This allows users to specify requirements that will be checked before execution and only if they hold the job will be executed (refer to the ReportServer user manual section Scheduling). Possible ReportServer conditions will be configured with dynamic lists that have a single result line. Here is such a dynamic list that could be used as a scheduler condition:

A	B	C	D
5	17	23	42

The list consists of four attributes A, B, C, D. If the list has been configured as a scheduler condition, users now can define conditions based on this list. ${} formula expressions define the conditions (here the user does not have to delimit the expression by "${" and "}". A feasible condition based on the list available would for instance be:

${A > 10 && D != 1}

Now, prior to execution, the expression would be compared to the current report values. Only if the condition evaluates to TRUE, the job will be executed.

In this section, we will give an example of how to create a conditional report, That is, the corresponding variant can be set to be executed in a conditional manner, i.e. only if the given condition is met during the scheduled execution time. Note that only variants may be scheduled.

First of all, a condition report must be created. The condition report is a variant of a dynamic list which we will use during conditional scheduling.

The demo data comes with the table T_AGG_PRODUCT, which contains information about products. As an example, we create a variant of the T_AGG_PRODUCT as a condition report. We select the PRO_PRODUCTNAME column and we will set the condition to be met if this column contains at least 10 entries.

For this purpose, open the T_AGG_PRODUCT report (Administration - Reports). Select the column PRO_PRODUCTNAME and set the aggregate function "Count". You can enter an alias for your column, e.g. NUMBER_OF_PRODUCTS. Save the report as a variant and call this "min10_Condition". Note that you need to ensure that your condition report only always returns a single row. In the above example, the report contains only the column PRO_PRODUCTNAME.

Now that you have defined a condition report, it is time to create the condition.

In this example, you will create a condition that uses the condition report "min10_Condition" you previously created. To do this, open the terminal (CTRL + ALT + T) and enter the following command:

rcondition create id:Report:152649 min10_Condition myCondition "Min 10 entries"

The easiest to provide the report is via id. In the example the id is 152649 so you can use the above command. You can find more information on the syntax of the rcondition command in Section 19.51.

Note: The id for the report can be found in the heading line of the variant. In our example, the id of the condition report "min10_Condition" is 152649.

ReportServer confirms the creation of the condition with a "Condition created" message. You can check the existing conditions via the rcondition list command, and remove conditions with rcondition remove.

Finally, we have a condition and can now use it when scheduling. For this, when you schedule a report, make sure to check the box "advanced options" on the first page of the scheduling wizard. Coose the dynamic list report T_AGG_PRODUCT for scheduling and select two columns: PRO_PRODUCTNAME and Y_AVG_PRICE. Save this report as a variant under the name "Productname_avgPrice". This is the variant being scheduled using the condition report "min10_Condition" previously created.

Open the variant "Productname_avgPrice" and click on "Schedule". Check "advanced options" and follow all steps until you reach the "Conditional Scheduling" dialog. Click on "Add condition" and select the condition "min10_Condition".

After clicking on the "Submit" button you can define the actual condition using a condition formula. In our example, you can use the formula PRO_PRODUCTNAME >= 10, which means that the condition holds if the number of products (PRO_PRODUCTNAME) is at least 10. You can test the condition for validity with the "test condition" button.

With this, the conditional scheduling is ready. Before the report is executed while scheduling, the condition is checked for validity. If the condition is true, the report is executed and sent.

Note: Setting the option "If the conditions do not hold" you can define the behavior if the condition is not true. Either skip the execution (which is probably the usual case) or retry the execution.

The ReportServer administrator is also able to create predefined conditions by scripting. The scheduling users can select the desired condition(s) from the set of conditions predefined. In such a way, the users do not have to type formulas, which improves user experience and avoids common errors. As a standard predefined scheduler condition, ReportServer has a "not empty" condition. It allows to prevent sending a report if it is empty.

It is important to emphasize that completely new conditions may be created by scripting. As an example, please take a look at the following simple script. It allows report execution during working days, while during the weekend, the report execution is disabled.

import net.datenwerke.rs.condition.service.condition.hooks.ConditionProviderHook
import net.datenwerke.rs.condition.client.condition.dto.SimpleCondition
import java.util.Calendar
 
def HOOK_NAME = "IS_WORKINGDAY_HOOK"
 
def callback = [
	provideConditionFor: { report ->
 		SimpleCondition cond = new SimpleCondition();
		cond.setKey(HOOK_NAME);
		cond.setName("Is working day");
		cond.setDescription("Actions are executed if today is a working day");
		return cond; 
	},
 	consumes: { key -> return HOOK_NAME.equals(key); },
 	execute: { key, expression, user, rjob ->
 		Calendar c1 = Calendar.getInstance();
 		c1.setTime(new Date());
 		return ( c1.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || 
          c1.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY ) ? false: true;
 	},
	isBeforeActions: { -> return true; },
	isBeforeJob: { -> return true; },
] as ConditionProviderHook
 
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, ConditionProviderHook.class, callback)

This script may be saved in the startup.d folder for execution at ReportServer startup. The predefined condition "Is working day" is then available for users during scheduling definition.

Alternatively, the conditions can also be defined via a small script. The basic outline of the script is:

import net.datenwerke.scheduler.service.scheduler.hooks.SchedulerExecutionHook
import net.datenwerke.scheduler.service.scheduler.hooks.adapter.SchedulerExecutionHookAdapter
import net.datenwerke.rs.scheduler.service.scheduler.jobs.report.ReportExecuteJob
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport
import net.datenwerke.rs.base.service.reportengines.table.output.object.CompiledTableReport
import net.datenwerke.scheduler.service.scheduler.helper.SkipJobExecution
def HOOK_NAME = "SkipEmptyListSchedulerHook";
def callback = [
  doesVetoExecution : { job, logEntry ->
    if(job should not be executed)
            return new SkipJobExecution("No data")
  }
  ] as SchedulerExecutionHookAdapter
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, SchedulerExecutionHook.class, callback)

So, basically, you implement the method doesVetoExecution as part of hook SchedulerExecutionHook. One example implementation, which checks whether or not the report contains any data (note that this only works with dynamic lists) would be:

import net.datenwerke.scheduler.service.scheduler.hooks.SchedulerExecutionHook
import net.datenwerke.scheduler.service.scheduler.hooks.adapter.SchedulerExecutionHookAdapter
import net.datenwerke.rs.scheduler.service.scheduler.jobs.report.ReportExecuteJob
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport
import net.datenwerke.rs.base.service.reportengines.table.output.object.CompiledTableReport
import net.datenwerke.scheduler.service.scheduler.helper.SkipJobExecution
def HOOK_NAME = "SkipEmptyListSchedulerHook";
def callback = [
  doesVetoExecution : { job, logEntry ->
    if(job instanceof ReportExecuteJob && job.getReport() instanceof TableReport){
       job.doExecute()
       if(job.getExecutedReport() instanceof CompiledTableReport && !job.getExecutedReport().hasData()){
          return new SkipJobExecution("No data")
       }
    }
  }
  ] as SchedulerExecutionHookAdapter
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, SchedulerExecutionHook.class, callback)

For a general introduction to scripting, refer to the Scripting Guide.