Running Reports with RedwoodScript

You can use RedwoodScript to generate reports with custom queries.

Required Imports

To create reports with RedwoodScript, you must import the Reporter and ReportDestination classes. For example:

import com.redwood.scheduler.api.model.report.Reporter;
import com.redwood.scheduler.api.model.report.ReportDestination;

Instantiating the Necessary Objects

To create the Reporter object, you must pass it an output stream, which must be a PrintWriter (usually jcsOut), like so:

Reporter reporter = jcsSession.createReporter(jcsOut);

You specify the output format when you create the ReportDestination object. For example:

ReportDestination destination = reporter.getHTMLReportDestination();

The available output formats are as follows.

• HTML: Use the getHTMLReportDestination() or getCSVReportDestination(<CSS_prefix>) method. The latter lets you specify a CSS prefix so you can use custom CSS.
• CSV: Use the getCSVReportDestination() method.
• XML: Use the getXMLReportDestination() method.

Retrieving Report Data

You cannot query the tables in the RunMyJobs database directly. The tables available for querying are listed in the API documentation. The Data model can be found under the Scheduler API and Language Reference section on the index page.

You can query the available tables with the executeQuery() method, which is defined in the SchedulerSession object, as follows:

executeQuery(query string, [[bind variables])

Use standard ANSI 92 SQL when constructing a query. When referring to a column, you must specify the table name (the type of the object you want to query for), followed by a period, like so:

jcsSession.executeQuery("select Job.JobId,Job.Description from Job", null, destination);

Note: Strings are trimmed before they are written to the database. Empty strings are saved as single space characters. If you need to test whether a string column entry is null, use IS STRINGNULL instead of IS NULL.

Example

The code below shows how to create a Reporter and a ReportDestination and how to create a query.

import com.redwood.scheduler.api.model.report.Reporter;
import com.redwood.scheduler.api.model.report.ReportDestination;
{
  String query = "select Job.JobId,Job.Description from Job where Job.Status = 'E'";
  Reporter reporter = jcsSession.createReporter(jcsOut);
  ReportDestination destination = reporter.getCSVReportDestination();
  jcsSession.executeQuery(query, null, destination);
}

This code shows the same example, but using bind variables and HTML output.

import com.redwood.scheduler.api.model.report.Reporter;
import com.redwood.scheduler.api.model.report.ReportDestination;
import com.redwood.scheduler.api.model.enumeration.JobStatus;
{
  String query = "select Job.JobId,Job.Description from Job where Job.Status = ?";
  Reporter reporter = jcsSession.createReporter(jcsOut);
  ReportDestination destination = reporter.getHTMLReportDestination();
  jcsSession.executeQuery(query, new Object[] { JobStatus.ErrorCode }, destination);
}

Bind variables can increase the performance and scalability of queries (especially simple queries like the ones above). Some supported databases, like Oracle, parse every query once and skip some queries they have already parsed. So if you are querying on process status, and that status changes frequently, the database will have to parse each and every query for each process status, if you use a bind variable, this step can be skipped.

Assume you do not know a status code and want to use it in a query. You could do so using the JobStatus class, like so:

import com.redwood.scheduler.api.model.enumeration.JobStatus;
import com.redwood.scheduler.api.model.report.Reporter;
import com.redwood.scheduler.api.model.report.ReportDestination;
{
  String query = "select Job.JobId,Job.Description from Job where Job.Status = ?";
  Reporter reporter = jcsSession.createReporter(jcsOut);
  ReportDestination destination = reporter.getCSVReportDestination();
  jcsSession.executeQuery(query, new Object[] { JobStatus.ScheduledCode }, destination);
}