The OpenRulesTM Tutorials          Home

User's Guide

Spreadsheet Management

Workbooks, Worksheets, and Tables
How OpenRules Tables Are Recognized
Table Example
Business and Technical Views

OpenRules uses Excel spreadsheets to represent and maintain business rules, web forms, and other information that can be organized using a tabular format. Excel is the best tool to handle different tables and is the most popular tool among business analysts.

Workbooks, Worksheets, and Tables  ►top

OpenRules utilizes Excel's well-established concepts of workbooks and worksheets. These can be represented and maintained in multiple Excel files. Each OpenRules workbook is comprised of one or more worksheets that can be used to separate information by categories. Each worksheet, in turn, is comprised of one or more tables. Decision tables are most typical OpenRules tables and are used to represent business rules. However, along with rule tables, OpenRules supports tables of other types such as: Form Layouts, Data and Datatypes, Methods, Environment tables. Workbooks can include tables of different types, each of which can support a different underlying logic.

How OpenRules Tables Are Recognized  ►top

OpenRules recognizes the tables inside Excel files using the following parsing algorithm.

1. The OpenRules parser splits spreadsheets into “parsed tables”. Logical tables should be separated by at least one empty row or column or start at the very first row/column. Table parsing is performed from left to right and from top to bottom. The first non-empty cell (i.e. cell with some text in it) that does not belong to a previously parsed table becomes the top-left corner of a new parsed table.

2. The parser determines the width/height of the table using non-empty cells as it’s clues. Merged cells are important and are considered as one cell. If the top-left cell of a table starts with a predefined keyword (see the table below), then such a table is parsed into an OpenRules Table.

3. All other "tables" are ignored and may contain any information.

The following table lists the predefined keywords and proper table types for OpenRules:

OpenRules Keyword	OpenRules Table Type
Rules	Rules Decision Table
Rules <rules-name> template <template-name>	Rules that extend Templates (since release 5.0)
Layout	Layout Table
Method	Methods with Java Snippets
Datatype	Datatype Table
Data	Data Table
Variable	Variable Table
Environment	OpenRules Environment

For example, the tables in the figure above are decision tables recognized by the keyword "Rules". OpenRules can be extended with more table types, each with their own keyword.

While not reflected in the table recognition algorithm, it is good practice to use a black background with a white foreground for the very first row. All cells in this row are usually merged, so the first row explicitly specifies the table width.  We call this row the "table signature". The text inside this row (consisting of one or several merged cells) is the table signature that starts with a keyword. The information after the keyword usually contains a unique table name and additional information that depends on the table type.

If you want to put a table title before the signature row, use an empty row between the title and the first row of the actual table.  Do not forget to put an empty row after the last table row. Here are examples of some typical tables recognized by OpenRules.

OpenRules table with 3 columns and 2 rows:

Keyword <some text>
Something	Something	Something
Something	Something	Something

OpenRules table with 3 columns and 2 rows:

Keyword	Something	Something
Something	Something	Something
Something	Something	Something

OpenRules table with 3 columns and 3 rows (empty initial cells are acceptable):

Keyword <some text>
Something	Something
	Something	Something
		Something

OpenRules table with 3 columns and 2 rows (the empty 3rd row ends the table):

Keyword <some text>
Something	Something	Something
Something	Something	Something
Something	Something	Something

OpenRules table with 2 columns and 2 rows (the empty cell in the 3rd column of the title row  results in the 4th columns being ignored. This also points out the importance of merging cells in the title row):

Keyword	Something		Something
Something	Something	Something	Something
Something	Something	Something	Something

OpenRules will not recognize this table (there is no empty row before the signature row):

Table Title
Keyword <some text>
Something	Something
	Something	Something
		Something

Fonts and coloring schema are a matter of the table designer's taste. The designer has at his/her disposal the entire presentation power of Excel (including comments) to make the OpenRules tables more self-explanatory.

Table Example  ►top

Here is an example of a worksheet with two rule tables:

This workbook is comprised of three worksheets:

1. Worksheet "Decision Tables" - includes rule tables

2. Worksheet "Launcher" - includes a method that defines an order and conditions under which rules will be executed

3. Worksheet "Environment" - defines a rule language and lists of standard terms used inside rule tables.

The worksheet "Decision Tables" is comprised of two rule tables "defineGreeting" and "defineSalutation".  Rule tables are a traditional way to represent business decision tables.  Rule decision tables usually describe combinations of conditions and actions that should be taken when all conditions have been satisfied. In the table "defineGreeting", the action "Set Greeting" will be executed when an "hour," passed to this table as a parameter, is between "Hour From" and "Hour To". In the table "defineSalutation", an action "Set Salutation" will be executed when a customer's Gender and Marital Status correspond to the proper row.

These tables start with signature rows that are determined by a keyword in the first table cell.  The table signature in general has the following format:

        Keyword return-type table-name(type1 parameter1, type2 parameter 2,..)

where: table-name - a one-word function name; return-type, type1, type 2 - types defined in the current OpenRules configuration, for example it can be any basic Java type like int, double, or String.

The rule tables above are recognized by the keyword "Rules". All of the columns have been merged into a single cell in the signature rows. Merging cells B3, C3, and D3 specifies that table "defineGreeting" has 3 columns. A table includes all those rows under its signature that contain non empty cells: in the example above, an empty row 12 indicates the end of the table "defineGreeting".

Limitation. Avoid merging rule rows in the very first column (or columns if the very first row) - it may lead to an invalid logic.

Business and Technical Views  ►top

Usually OpenRules worksheets have two views:

[1] Business View
[2] Technical View

These two views are implemented using Excel's outline buttons [1] and [2] at the top left corner of every worksheet - see the figure above. This figure represents a business view - no technical details about the implementation are provided. For example, from this view it is hard to tell for sure what greeting will be generated at 11 o'clock: "Good Morning" or "Good Afternoon"? If you push the Technical View button [2] (or the button "+" on the left), you will see the hidden rows with the technical details of this decision table:

The technical view opens hidden rows 4-6 that contain the implementation details. In particular, you can see that both "Hour From" and "Hour To" are included in the definition of the time intervals. Different types of tables have different technical views.

Using Rules Templates you may completely split business and technical information between Excel different tables.

 

►top