Chapter 43. Defining spreadsheet decision tables

Spreadsheet decision tables (XLS or XLSX) require two key areas that define rule data: a RuleSet area and a RuleTable area. The RuleSet area of the spreadsheet defines elements that you want to apply globally to all rules in the same package (not only the spreadsheet), such as a rule set name or universal rule attributes. The RuleTable area defines the actual rules (rows) and the conditions, actions, and other rule attributes (columns) that constitute that rule table within the specified rule set. A spreadsheet of decision tables can contain multiple RuleTable areas, but only one RuleSet area.

Important

You should typically upload only one spreadsheet of decision tables, containing all necessary RuleTable definitions, per rule package in Business Central. You can upload separate decision table spreadsheets for separate packages, but uploading multiple spreadsheets in the same package can cause compilation errors from conflicting RuleSet or RuleTable attributes and is therefore not recommended.

Refer to the following sample spreadsheet as you define your decision table:

Figure 43.1. Sample spreadsheet decision table for shipping charges

Decision table example

Procedure

  1. In a new XLS or XLSX spreadsheet, go to the second or third column and label a cell RuleSet (row 1 in example). Reserve the column or columns to the left for descriptive metadata (optional).
  2. In the next cell to the right, enter a name for the RuleSet. This named rule set will contain all RuleTable rules defined in the rule package.
  3. Under the RuleSet cell, define any rule attributes (one per cell) that you want to apply globally to all rule tables in the package. Specify attribute values in the cells to the right. For example, you can enter an Import label and in the cell to the right, specify relevant data objects from other packages that you want to import into the package for the decision table (in the format package.name.object.name). For supported cell labels and values, see Section 43.1, “RuleSet definitions”.
  4. Below the RuleSet area and in the same column as the RuleSet cell, skip a row and label a new cell RuleTable (row 7 in example) and enter a table name in the same cell. The name is used as the initial part of the name for all rules derived from this rule table, with the row number appended for distinction. You can override this automatic naming by inserting a NAME attribute column.
  5. Use the next four rows to define the following elements as needed (rows 8-11 in example):

    • Rule attributes: Conditions, actions, or other attributes. For supported cell labels and values, see Section 43.2, “RuleTable definitions”.
    • Object types: The data objects to which the rule attributes apply. If the same object type applies to multiple columns, merge the object cells into one cell across multiple columns (as shown in the sample decision table), instead of repeating the object type in multiple cells. When an object type is merged, all columns below the merged range will be combined into one set of constraints within a single pattern for matching a single fact at a time. When an object is repeated in separate columns, the separate columns can create different patterns, potentially matching different or identical facts.
    • Constraints: Constraints on the object types.
    • Column label: (Optional) Any descriptive label for the column, as a visual aid. Leave blank if unused.

      Note

      As an alternative to populating both the object type and constraint cells, you can leave the object type cell or cells empty and enter the full expression in the corresponding constraint cell or cells. For example, instead of Order as the object type and itemsCount > $1 as a constraint (separate cells), you can leave the object type cell empty and enter Order( itemsCount > $1 ) in the constraint cell, and then do the same for other constraint cells.

  6. After you have defined all necessary rule attributes (columns), enter values for each column as needed, row by row, to generate rules (rows 12-17 in example). Cells with no data are ignored (such as when a condition or action does not apply).

    If you need to add more rule tables to this decision table spreadsheet, skip a row after the last rule in the previous table, label another RuleTable cell in the same column as the previous RuleTable and RuleSet cells, and create the new table following the same steps in this section (rows 19-29 in example).

  7. Save your XLS or XLSX spreadsheet to finish.
Note

By default, only the first worksheet in a spreadsheet workbook is processed as a decision table when you upload the spreadsheet in Business Central. Each RuleSet name combined with the RuleTable name must be unique across all decision table files in the same package.

If you want to process multiple worksheet decision tables, then create a .properties file with the same name as the spreadsheet workbook. The .properties file must contain a property sheet with comma-separated values (CSV) for the names of the worksheets, for example:

sheets=Sheet1,Sheet2

After you upload the decision table in Business Central, the rules are rendered as DRL rules like the following example, from the sample spreadsheet:

//row 12
rule "Basic_12"
salience 10
  when
    $order : Order( itemsCount > 0, itemsCount <= 3, deliverInDays == 1 )
  then
    insert( new Charge( 35 ) );
end
Enabling white space used in cell values

By default, any white space before or after values in decision table cells is removed before the decision table is processed by the decision engine. To retain white space that you use intentionally before or after values in cells, set the drools.trimCellsInDTable system property to false in your Red Hat Process Automation Manager distribution.

For example, if you use Red Hat Process Automation Manager with Red Hat JBoss EAP, add the following system property to your $EAP_HOME/standalone/configuration/standalone-full.xml file:

<property name="drools.trimCellsInDTable" value="false"/>

If you use the decision engine embedded in your Java application, add the system property with the following command:

java -jar yourApplication.jar -Ddrools.trimCellsInDTable=false

43.1. RuleSet definitions

Entries in the RuleSet area of a decision table define DRL constructs and rule attributes that you want to apply to all rules in a package (not only in the spreadsheet). Entries must be in a vertically stacked sequence of cell pairs, where the first cell contains a label and the cell to the right contains the value. A decision table spreadsheet can have only one RuleSet area.

The following table lists the supported labels and values for RuleSet definitions:

Table 43.1. Supported RuleSet definitions

LabelValueUsage

RuleSet

The package name for the generated DRL file. Optional, the default is rule_table.

Must be the first entry.

Sequential

true or false. If true, then salience is used to ensure that rules fire from the top down.

Optional, at most once. If omitted, no firing order is imposed.

SequentialMaxPriority

Integer numeric value

Optional, at most once. In sequential mode, this option is used to set the start value of the salience. If omitted, the default value is 65535.

SequentialMinPriority

Integer numeric value

Optional, at most once. In sequential mode, this option is used to check if this minimum salience value is not violated. If omitted, the default value is 0.

EscapeQuotes

true or false. If true, then quotation marks are escaped so that they appear literally in the DRL.

Optional, at most once. If omitted, quotation marks are escaped.

Import

A comma-separated list of Java classes to import from another package.

Optional, may be used repeatedly.

Variables

Declarations of DRL globals (a type followed by a variable name). Multiple global definitions must be separated by commas.

Optional, may be used repeatedly.

Functions

One or more function definitions, according to DRL syntax.

Optional, may be used repeatedly.

Queries

One or more query definitions, according to DRL syntax.

Optional, may be used repeatedly.

Declare

One or more declarative types, according to DRL syntax.

Optional, may be used repeatedly.

Unit

The rule units that the rules generated from this decision table belong to.

Optional, at most once. If omitted, the rules do not belong to any unit.

Dialect

java or mvel. The dialect used in the actions of the decision table.

Optional, at most once. If omitted, java is imposed.

Warning

In some cases, Microsoft Office, LibreOffice, and OpenOffice might encode a double quotation mark differently, causing a compilation error. For example, “A” will fail, but "A" will pass.

43.2. RuleTable definitions

Entries in the RuleTable area of a decision table define conditions, actions, and other rule attributes for the rules in that rule table. A spreadsheet of decision tables can contain multiple RuleTable areas.

The following table lists the supported labels (column headers) and values for RuleTable definitions. For column headers, you can use either the given labels or any custom labels that begin with the letters listed in the table.

Table 43.2. Supported RuleTable definitions

LabelOr custom label that begins withValueUsage

NAME

N

Provides the name for the rule generated from that row. The default is constructed from the text following the RuleTable tag and the row number.

At most one column.

DESCRIPTION

I

Results in a comment within the generated rule.

At most one column.

CONDITION

C

Code snippet and interpolated values for constructing a constraint within a pattern in a condition.

At least one per rule table.

ACTION

A

Code snippet and interpolated values for constructing an action for the consequence of the rule.

At least one per rule table.

METADATA

@

Code snippet and interpolated values for constructing a metadata entry for the rule.

Optional, any number of columns.

The following sections provide more details about how condition, action, and metadata columns use cell data:

Conditions

For columns headed CONDITION, the cells in consecutive lines result in a conditional element:

  • First cell: Text in the first cell below CONDITION develops into a pattern for the rule condition, and uses the snippet in the next line as a constraint. If the cell is merged with one or more neighboring cells, a single pattern with multiple constraints is formed. All constraints are combined into a parenthesized list and appended to the text in this cell.

    If this cell is empty, the code snippet in the cell below it must result in a valid conditional element on its own. For example, instead of Order as the object type and itemsCount > $1 as a constraint (separate cells), you can leave the object type cell empty and enter Order( itemsCount > $1 ) in the constraint cell, and then do the same for any other constraint cells.

    To include a pattern without constraints, you can write the pattern in front of the text of another pattern, with or without an empty pair of parentheses. You can also append a from clause to the pattern.

    If the pattern ends with eval, code snippets produce boolean expressions for inclusion into a pair of parentheses after eval.

    You can terminate the pattern with @watch annotation, which is used to customize the properties that the pattern is reactive on.

  • Second cell: Text in the second cell below CONDITION is processed as a constraint on the object reference in the first cell. The code snippet in this cell is modified by interpolating values from cells farther down in the column. If you want to create a constraint consisting of a comparison using == with the value from the cells below, then the field selector alone is sufficient. If you use the field selector alone, but you want to use the condition as it is without appending any == comparison, you must terminate the condition with the symbol ?. Any other comparison operator must be specified as the last item within the snippet, and the value from the cells below is appended. For all other constraint forms, you must mark the position for including the contents of a cell with the symbol $param. Multiple insertions are possible if you use the symbols $1, $2, and so on, and a comma-separated list of values in the cells below. However, do not separate $1, $2, and so on, by commas, or the table will fail to process.

    To expand a text according to the pattern forall($delimiter){$snippet}, repeat the $snippet once for each of the values of the comma-separated list in each of the cells below, insert the value in place of the symbol $, and join these expansions by the given $delimiter. Note that the forall construct may be surrounded by other text.

    If the first cell contains an object, the completed code snippet is added to the conditional element from that cell. A pair of parentheses is provided automatically, as well as a separating comma if multiple constraints are added to a pattern in a merged cell. If the first cell is empty, the code snippet in this cell must result in a valid conditional element on its own. For example, instead of Order as the object type and itemsCount > $1 as a constraint (separate cells), you can leave the object type cell empty and enter Order( itemsCount > $1 ) in the constraint cell, and then do the same for any other constraint cells.

  • Third cell: Text in the third cell below CONDITION is a descriptive label that you define for the column, as a visual aid.
  • Fourth cell: From the fourth row on, non-blank entries provide data for interpolation. A blank cell omits the condition or constraint for this rule.
Actions

For columns headed ACTION, the cells in consecutive lines result in an action statement:

  • First cell: Text in the first cell below ACTION is optional. If present, the text is interpreted as an object reference.
  • Second cell: Text in the second cell below ACTION is a code snippet that is modified by interpolating values from cells farther down in the column. For a singular insertion, mark the position for including the contents of a cell with the symbol $param. Multiple insertions are possible if you use the symbols $1, $2, and so on, and a comma-separated list of values in the cells below. However, do not separate $1, $2, and so on, by commas, or the table will fail to process.

    A text without any marker symbols can execute a method call without interpolation. In this case, use any non-blank entry in a row below the cell to include the statement. The forall construct is supported.

    If the first cell contains an object, then the cell text (followed by a period), the text in the second cell, and a terminating semicolon are strung together, resulting in a method call that is added as an action statement for the consequence. If the first cell is empty, the code snippet in this cell must result in a valid action element on its own.

  • Third cell: Text in the third cell below ACTION is a descriptive label that you define for the column, as a visual aid.
  • Fourth cell: From the fourth row on, non-blank entries provide data for interpolation. A blank cell omits the condition or constraint for this rule.
Metadata

For columns headed METADATA, the cells in consecutive lines result in a metadata annotation for the generated rules:

  • First cell: Text in the first cell below METADATA is ignored.
  • Second cell: Text in the second cell below METADATA is subject to interpolation, using values from the cells in the rule rows. The metadata marker character @ is prefixed automatically, so you do not need to include that character in the text for this cell.
  • Third cell: Text in the third cell below METADATA is a descriptive label that you define for the column, as a visual aid.
  • Fourth cell: From the fourth row on, non-blank entries provide data for interpolation. A blank cell results in the omission of the metadata annotation for this rule.

43.3. Additional rule attributes for RuleSet or RuleTable definitions

The RuleSet and RuleTable areas also support labels and values for other rule attributes, such as PRIORITY or NO-LOOP. Rule attributes specified in a RuleSet area will affect all rule assets in the same package (not only in the spreadsheet). Rule attributes specified in a RuleTable area will affect only the rules in that rule table. You can use each rule attribute only once in a RuleSet area and once in a RuleTable area. If the same attribute is used in both RuleSet and RuleTable areas within the spreadsheet, then RuleTable takes priority and the attribute in the RuleSet area is overridden.

The following table lists the supported labels (column headers) and values for additional RuleSet or RuleTable definitions. For column headers, you can use either the given labels or any custom labels that begin with the letters listed in the table.

Table 43.3. Additional rule attributes for RuleSet or RuleTable definitions

LabelOr custom label that begins withValue

PRIORITY

P

An integer defining the salience value of the rule. Rules with a higher salience value are given higher priority when ordered in the activation queue. Overridden by the Sequential flag.

Example: PRIORITY 10

DATE-EFFECTIVE

V

A string containing a date and time definition. The rule can be activated only if the current date and time is after a DATE-EFFECTIVE attribute.

Example: DATE-EFFECTIVE "4-Sep-2018"

DATE-EXPIRES

Z

A string containing a date and time definition. The rule cannot be activated if the current date and time is after the DATE-EXPIRES attribute.

Example: DATE-EXPIRES "4-Oct-2018"

NO-LOOP

U

A Boolean value. When this option is set to true, the rule cannot be reactivated (looped) if a consequence of the rule re-triggers a previously met condition.

Example: NO-LOOP true

AGENDA-GROUP

G

A string identifying an agenda group to which you want to assign the rule. Agenda groups allow you to partition the agenda to provide more execution control over groups of rules. Only rules in an agenda group that has acquired a focus are able to be activated.

Example: AGENDA-GROUP "GroupName"

ACTIVATION-GROUP

X

A string identifying an activation (or XOR) group to which you want to assign the rule. In activation groups, only one rule can be activated. The first rule to fire will cancel all pending activations of all rules in the activation group.

Example: ACTIVATION-GROUP "GroupName"

DURATION

D

A long integer value defining the duration of time in milliseconds after which the rule can be activated, if the rule conditions are still met.

Example: DURATION 10000

TIMER

T

A string identifying either int (interval) or cron timer definitions for scheduling the rule.

Example: TIMER "*/5 * * * *" (every 5 minutes)

CALENDAR

E

A Quartz calendar definition for scheduling the rule.

Example: CALENDAR "* * 0-7,18-23 ? * *" (exclude non-business hours)

AUTO-FOCUS

F

A Boolean value, applicable only to rules within agenda groups. When this option is set to true, the next time the rule is activated, a focus is automatically given to the agenda group to which the rule is assigned.

Example: AUTO-FOCUS true

LOCK-ON-ACTIVE

L

A Boolean value, applicable only to rules within rule flow groups or agenda groups. When this option is set to true, the next time the ruleflow group for the rule becomes active or the agenda group for the rule receives a focus, the rule cannot be activated again until the ruleflow group is no longer active or the agenda group loses the focus. This is a stronger version of the no-loop attribute, because the activation of a matching rule is discarded regardless of the origin of the update (not only by the rule itself). This attribute is ideal for calculation rules where you have a number of rules that modify a fact and you do not want any rule re-matching and firing again.

Example: LOCK-ON-ACTIVE true

RULEFLOW-GROUP

R

A string identifying a rule flow group. In rule flow groups, rules can fire only when the group is activated by the associated rule flow.

Example: RULEFLOW-GROUP "GroupName"

Figure 43.2. Sample decision table spreadsheet with attribute columns

Example decision table with definitions used