Creating and Running Reports →

Reporting

Reports are process definitions that allow you to gather meta-data about your repository in one of the following formats:

  • Comma separated values (CSV) to import into your favorite spreadsheet application
  • HTML to view in your browser
  • XML to import into third party systems
  • RTX to view in your browser and process further
  • JSON to view in your browser and process further

Report definitions are located in Definitions > Reports.

You can define multiple reports, and schedule a report definition to generate the output. It is also possible to run an existing report from within RedwoodScript. You can generate a report based on an object filter or a query on the Report tab, choose the columns of the query you want to end up in the report in the Columns tab, and throttle the amount of rows in the Selections tab based on the values of two columns or the value in a column.

For report automation, see Generating Reports.

The report object allows you to define the query with the following tabs

  • Report Definition - create the initial query, based on query filters or custom SQL
  • Documentation - write a description of the report
  • Parameters - pass parameters from within a chain definition and use REL expressions for use with the Selections
  • Columns - select which columns appear in the report and their formats
  • Selections - limit the number of rows of the report
  • Sorts - sort the rows in a specific order
note

Reports are limited to 500 000 rows.

Report

You have the choice between using a Query Filter and Object Definition Query. Redwood recommends using the query filters whenever possible and using a Selections or a combination of Selections, Parameters, and Sorts. The Object Definition Query can be used for more complex queries, ANSI '92 SQL knowledge is required.

Parameters

Parameters are available to use Redwood Expression Language in Selections. These parameters will also be available when you create report definitions. Parameter values are to be compared to column values of the report, so you first choose the data type of the column in the report you would like to compare to, both have to match. Array parameters are support for IN clauses, limited to 256 elements, every element must be unique in the array.

Columns

By default a number columns will be displayed in the report; you can hide or delete unwanted columns on this tab and format the displayed value of a column.

The following formats are available for columns of HTML reports, they are not used for CVS or XML formats:

  • Raw - displays the data as it is stored in the database, this will be the UniqueId for a Queue field or a UTC epoch timestamp for dates, for example
  • Formatted - formats the output to be more user-friendly
  • Business Key - displays the <partition>.<name> of the object, instead of its name
  • Permalink - displays the full permalink instead of the value
  • Permalink Business Key - displays a Business Key which links (via permalink) to the actual object

You use REL functions to further format the values in the Values column. For example, the Formatted value of Job.RunTime is in milliseconds, you use the Time.formatDuration(<duration_column>) function to format a duration into hours, minutes, seconds, and milliseconds; for example fill =Time.formatDuration(Job.RunTime) into the Value field of the Job.RunTime row. On DateTimeZone columns, you use the Time.expression(<dtz_column>, '<expression>') function to alter a value, for example =Time.expression(Job.RunStart, 'add 1 hour') would add an hour to the value. Leaving the <expression> empty displays the date/time in its time zone.

If you want to select a date/time and the time zone is not GMT, ensure the time zone column is on the columns tab or the time will be displayed in the GMT time zone. You select it in your custom SQL for this to be the case, when you use filters it will automatically be added. If you do not wish to display the time zone column in the output, you simply uncheck it.

Example

A report has been created on the filter processes With Status "Completed" (object Job), you would like to format the RunTime column in the report to use the 0:00:00:000 format. On the Columns tab you fill =Time.formatDurationEx(Job.RunTime, '|||||#|:#|:#|:#') into the Value field of Job.RunTime. You preview the report and notice that a job that ran for 6000 milliseconds has a value of 0:00:06:000 in the report preview.

You would also like to have a column containing the remote run time in minutes and seconds, such as 5min 48sec; you add a column and calculate the value by subtracting RemoteRunStart from RemoteRunEnd as follows:

=Time.formatDurationEx(Job.RemoteRunEnd - Job.RemoteRunStart, '||||||#min| #sec|')

Note that Job.RemoteRunEnd and Job.RemoteRunStart are only set for remote connector processes.

You would also like to format a date as yyyy.MM.dd hh:mm:ss:

=Time.format(Job.RunEnd, 'yyyy.MM.dd hh:mm:ss')

You would like to change the TimeZone of a date to America/Los_Angeles; this is a little more complex as there are no REL functions to easily change a time zone of a DateTimeZone.

You create a library named Custom_MyLib in the same partition as your report with the following source:

package com.redwood.scheduler.custom;

import com.redwood.scheduler.api.date.DateTimeZone;
import java.util.TimeZone;

public class TimeFunctions
{
  public DateTimeZone millisToDTZ(String millis, String z)
  {
     long millisl = Long.valueOf(millis);
     return new DateTimeZone(TimeZone.getTimeZone(z), millisl);
  }
}

You create the following REL Entry Point:

NameFQ Class NameMethod Signature
millisToDTZcom.redwood.scheduler.custom.TimeFunctionsmillisToDTZ(String, String)

Note that you will have to close the report and re-open it for any changes to libraries to be detected.

You then specify the following in the value field or your report column:

=$.Custom_MyLib.millisToDTZ(Time.getUTCMilliseconds(Job.RunStart), 'America/Los_Angeles')

Note that if you specify an invalid time zone, GMT will be used.

For more information on libraries, see the Libraries section of the documentation.

Selections

On the Selections tab you can compare values of different columns with each other, with a hard-coded value or report parameter. The following operations can be used to compare values:

  • Equal To - values must be equal or in other words be the same.
  • No Equal To - values must differ
  • Matches Regex - used with Value or Report Parameter only, the value of the Left Report Column must match the provided regular expression.
  • Not Matches Regex - used with Value or Report Parameter only, the value of the Left Report Column must match not the provided regular expression.
  • In Range - used with Value or Report Parameter only, the value of the Left Report Column must match the provided range expression.
  • Not In Range - used with Value or Report Parameter only, the value of the Left Report Column must match not the provided range expression.
  • Greater Than - value of the Left Report Column must be greater than the provided value of the Column, Value, or Report Parameter.
  • Less Than - value of the Left Report Column must be smaller than the provided value of the Column, Value, or Report Parameter.
  • Greater Than Or Equal - value of the Left Report Column must be greater than or equal to the provided value of the Column, Value, or Report Parameter.
  • Less Than Or Equal - value of the Left Report Column must be smaller than or equal to the provided value of the Column, Value, or Report Parameter.

Finding Reports

You can search for reports using filters and the Search Reports box on the Reports tab. This box is known as the IntelliSearch box and located under your username on the top right-hand side of the user interface. Filters allow you to specify a list of objects with static criteria. IntelliSearch allows you to specify complex queries in a simple way using prefixes. Prefixes are used to specify which property you are searching in and have short as well as long syntaxes. For example, if you want to display all reports with the term import in the comment, you would use the search criteria as follows:

c:import

You can search more than one property, as follows:

c:import n:Bi
note

No spaces should be entered before or after the colon (:).

See the Advanced Object Search for more information.

The following table illustrates the available prefixes for reports:

PrefixesDescription
n, namesearches the name property
c, comm, commentsearches the documentation property
d, desc, descriptionsearches the description property
a, applicationsearches the application property
cb, changedbefore(internal) search for reports that changed before a certain ISO-8601 period

Security

PrivilegeDescription
Report.CreateCreate reports
Report.DeleteDelete reports
Report.EditEdit reports
Report.ViewAccess reports

You can grant privileges on two levels, Access and Admin; a privilege granted on Admin level allows the grantee to grant the privilege to other users. These privileges can be granted per partition or system-wide.

The Security tab allows you to specify which users can access, edit, and delete the report.

See Also

report reporting

Creating and Running Reports →