← Configuration Data for Reports

Creating and Running Reports in RedwoodScript

RedwoodScript can be used to generate reports with custom queries, which can be used in RedwoodScript process definitions. You have to import two classes, since they are not imported by default in the different scripting contexts process definitions:

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

Classes

The two classes are required for creating reports and are not imported in standard scripting contexts, you will have to import them with an import statement, see Example section below.

The Reporter requires the output stream, which has to be a PrintWriter (usually jcsOut ), and the ReportDestination, the output format.

The output format can be one of the following

  • HTML - use the getHTMLReportDestination() or getCSVReportDestination(<css_prefix>) method, the latter allows you to specify a css prefix, which in turn allows you to use custom CSS.
  • CSV - use the getCSVReportDestination() method
  • XML - use the getXMLReportDestination() method

Retrieving Report-Data

You cannot query the tables in the database directly, only an upper-layer table-implementation; the available tables in Redwood Server are listed in the API documentation. The Data model can be found under the Scheduler API and Language Reference section on the index page.

The database can be queried with the following function, which is defined in the SchedulerSession object:

executeQuery(query string, [[bind variables])

Standard ANSI'92 SQL can be used in queries; when referring to a column, you need to prepend the table name, which name is the type of the object you want to query for, followed by a period ( __. ), as shown below;

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 ' ', or a single <space> character.

When you test if a column entry is null, you must keep in mind that empty strings are stored as ' ', or a single <space> character. If you want to check if a column of type string is empty, use IS STRINGNULL instead of IS NULL.

Example

The code below illustrates how to create a Reporter, a ReportDestination; and how to use a query for the report.

Generate a CSV report of all jobs in error:

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);
}

The same example as above 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 one above. Some supported databases, like Oracle, parse every query once and skip some queries they have already parsed. So if the Job.Status above changes frequently, the database will have to parse each and every query for each Job.Status, if we use a bind variable, this step can be skipped.

The following example is for illustration purposes, when you do not know the status code and want to use it in a query (note that looking up the code is easier). You have to import the JobStatus class, the fully qualified class name can be found in the API documentation:

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);
}

See Also

report reporting

← Configuration Data for Reports