How do I use the Logical Expression feature in reports?

Environment:

Analytics, Logical Expressions in Report Filters
Oracle B2C Service

Resolution:

The logical expression feature allows significant flexibility when defining reports. By default, when multiple filters are included in a report, all filters are joined with an AND (&) Boolean connector.  However, you can customize the logical expression to use an OR ( | ) join between filters or groups of filters as well.

Note: Logical expressions are also used when configuring rulesets.  For more information on using logical expressions with rules, refer to Answer ID 84: Using 'OR' logic with business rules.

When you add multiple filters to the report, the logical expression automatically joins the filters with an AND connector.

Therefore, to customize the logical expression used for your report, the first step is to add an OR node. Then, drag and drop the filters that are joined with an OR join into the OR node. 

Within the OR node, you can also add an AND sub-node for more complex expressions.

When adding a node to an expression, you are adding a parenthetical component where the elements within the node are joined by either OR or AND.  That is, adding an OR node is like adding ( __ | __ ) or ( __ OR __ ) to the logical expression.  For this reason, you must have at least two components listed under a node. 

You can drag and drop filters into the node or you can add sub-nodes and then drag filters to the sub-nodes. Although AND and OR nodes alternate you can add one then the other and move the desired node to the root and delete the undesired node.

You can also negate both filters or nodes.  Simply select the filter or node and click Negate Expression.

The Edit Text Definition link allows you to review how your filters are grouped and the operators between them.  This allows you to verify the configuration of the expression.

From the Edit Logical Expression window, you can add, edit or delete filters from your report. In addition, you can click on a node and delete that node.

Caution: If you delete a node, it also deletes all filters and sub-nodes. Therefore, to delete a node without deleting the filters, you must first drag the filters to a higher level (such as the top AND node) and then delete the node.

To modify the logical expression used with your report: use the steps below:

1. Open the report for editing. 
    
2. From the Home tab, click Filters from the Data Set section.
    
   In the Logical Expression window, the filters are listed under an AND node, indicating they are all joined using an AND join.  
    
3. To add an OR node, click Add OR Node. 
    
4. Drag and drop filters to the OR node. 
    
5. Click View Text Definition to review the logical expression. 
    
6. When finished, click OK to close the Edit Logical Expression window and save your report.

Note: If you want all of your filters to be joined with an OR connector, add the OR node and then drag and drop all of your filters to the OR node. 

Use of Parentheses

Within the logical expression string, the use of parentheses is very important. If no parentheses are included, the criteria are evaluated solely by the order listed in the Logical Expression box. For example, there is a significant difference between expressions A and B below:

• Expression A: 1 & (2 | 3)
• Expression B: 1 & 2 | 3

Without parentheses, comparisons are made strictly by the order the criteria are listed. As a result, the expression 1 & 2 | 3 corresponds to (1 & 2) | 3.

• With expression A, an item matches if criteria 1 is met AND either criteria 2 or 3 is met.  No matter what, criteria 1 must be met in order for the record to match. 
   
• With expression B, an item matches if criteria 1 and 2 are met OR if criteria 3 is met. If criteria 3 is met, it does not matter if 1 or 2 are met.  If criteria 1 and 2 are met, it does not matter if criteria 3 is met.