← Creating and Running ReportsConfiguration Data for Reports →

Creating Advanced Reports

When you want to generate more complex reports, and the object filters are not enough, you can query the Object Definition. ANSI '92 SQL knowledge is required to be able to write queries.

Relationships between objects go through the UniqueId of the object. For example, a process definition can have a default queue, so it has a Queue column. The value in the Queue column will be the UniqueId of the queue, not its name. This allows you to easily rename objects without having to change all it relatives. You join tables when you want to query on a relative.

Matching

You use the where clause for matching. 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.

note

Strings are trimmed before they are written to the database. Empty strings are saved as ' ', or a single <space> character.

The following example illustrates how to query all process definition that have DefaultQueue set to System:

select jd.Name from JobDefinition jd where jd.DefaultQueue in (select q.UniqueId from Queue q where q.Name =  'System')

String Comparisons

Note that translated fields are saved in the database in a special way, so you cannot use translated fields, such as Description, Documentation fields of built-in process definitions (with the System_ or SAP_ prefix) in String comparisons.

select JobDefinition.Name,JobDefinition.Description from JobDefinition where JobDefinition.Name = 'System_Promote'

The above query generates the table below with the name and description of process definition *System_Promote*

JobDefinition.DescriptionJobDefinition.Name
This process takes care of car file promotion.System_Promote
 
select JobDefinition.Name,JobDefinition.Description from JobDefinition where JobDefinition.Description like '%promotion%'

The above query returns no rows because the Description field on the *System_Promote* process definition is not stored in the database.

Columns

Special attention needs to be paid to column names, reports do not support multiple columns with the same name. SQL'92 keywords cannot be used as column names.

The following query would generates two columns named JobId, the report module does not support such queries and puts everything into one column, sorting on a column becomes ambiguous.

select originalJob.JobId, restartedJob.JobId
from Job originalJob, Job restartedJob
where restartedJob.RestartJobId = originalJob.JobId

The following query specifies column names:

select originalJob.JobId OriginalId, restartedJob.JobId RestartId
from Job originalJob, Job restartedJob
where restartedJob.RestartJobId = originalJob.JobId
order by OriginalId ASC

The query generates a report on restarted processes with two columns, _OriginalId_ and _RestartId_, sorted on OriginalId, in ascending order.

JobDefinition and JobChain Tables

When you query the JobDefinition and JobChain table you must take JobDefinition branches into account.

The following SQL does not take branches into account and will print multiple rows with the same definition name if there are any, one for each branch of the specific process definition.

select jd.Name Definition, jd.JobDefinitionType DefinitionType, jd.OwnerSubject DefinitionOwner from JobDefinition jd
where jd.DefaultQueue in (select q.UniqueId from Queue q where q.Name =  'System')

You use BranchedLLPVersion = -1 to select master process definitions, only. You can use the Behavior column to select custom definitions, only.

select jd.Name Definition, jd.JobDefinitionType DefinitionType, jd.OwnerSubject DefinitionOwner from JobDefinition jd
where jd.DefaultQueue in (select q.UniqueId from Queue q where q.Name =  'System')
and jd.BranchedLLPVersion = -1 and jd.Behavior = 0

Procedure

Create the report

  1. Navigate to "Definitions > Reports".
  2. Choose New Report from the context-menu.
  3. On the Report tab, fill a name into the Name field and choose a object type in the Object Definition Query field. Optionally use the lower field to enhance the query.
  4. On the Selections tab choose a column and a condition.
  5. On the Sorting tab, choose the columns you want to sort by and give each a unique sort order.

Example

Create a report of all processes that reached status Error on process server MSLN_UNIXS3

  1. Navigate to "Definitions > Reports".
  2. Choose New Report from the context-menu.
  3. On the Report tab, fill Rep_Job4UNIXS3 into the Name field and choose Job in the Object Definition Query field. Fill where Job.ProcessServer in (select ps.UniqueId from ProcessServer ps where ps.Name = 'MSLN_UNIXS3').
  4. On the Selections tab choose column Status and enter Error into Column/Value > Value field.
  5. On the Sorting tab, choose column JobId to sort by and give it a sort order of Ascending.
  6. On the Preview tab, inspect the report.
  7. Choose Save & Close.

Submit the report

  1. Navigate to "Definitions > Reports".
  2. Choose Submit Report from the context-menu of the report object.
  3. Make sure the correct report is in the Report field.
  4. Make sure the output format is correct.
  5. Choose Submit.

See Also

SQL Functions

report reporting

← Creating and Running ReportsConfiguration Data for Reports →