Using Expression Builder

You can use the Expression Builder dialogs in the Oracle BI Administration Tool to create constraints, aggregations, and other definitions within a repository.

Expression Builder provides automatic color highlighting and other formatting enhancements to make expressions easier to build and to read.

The expressions you create with Expression Builder are similar to expressions created with SQL. Except where noted, you can use all expressions constructed with Expression Builder in SQL queries against the Oracle BI Server.

This section contains the following topics:

About the Expression Builder Dialogs

The Expression Builder contains a number of dialogs.

When creating expressions in Expression Builder, you select a category from the Category pane and values are displayed in the lower panes depending on the value selected in the Category pane. When you type a value into a Find field, it filters out the non-matching strings and displays matching strings only. After typing search criteria in a Find field, you can move up and down the list using the scroll bar, and use the tab key to move between the Find fields. To return to the full list of results, delete the string from the Find field.

Note:

You can only enter text in the Find field that matches the text of one of the available strings. For example, if the available string options begin with A11, A12, and A13, the text you enter in the Find field must begin with A.

When you first open Expression Builder, the items are not sorted. When selected, the Sort Panes option sorts all items in the panes. As soon as you select this option, the panes are automatically redrawn without changing the contents of the panes or your filtering criteria.

About the Expression Builder Toolbar

Describes operators on the Expression Builder toolbar.

The toolbar is located at the bottom of Expression Builder.

The table describes each button and its function in an expression.

Operator Description

+

Plus sign for addition.

-

Minus sign for subtraction.

*

Multiply sign for multiplication.

/

Divide by sign for division.

||

Character string concatenation.

(

Open parenthesis.

)

Close parenthesis.

>

Greater than sign, indicating values higher than the comparison.

<

Less than sign, indicating values lower than the comparison.

=

Equal sign, indicating the same value.

<=

Less than or equal to sign, indicating values the same or lower than the comparison.

>=

Greater than or equal to sign, indicating values the same or higher than the comparison.

<>

Not equal to, indicating values higher or lower, but different.

AND

AND connective, indicating intersection with one or more conditions to form a compound condition.

OR

OR connective, indicating the union with one or more conditions to form a compound condition.

NOT

NOT connective, indicating a condition is not met.

,

Comma, used to separate elements in a list.

About the Categories in the Category Pane

The categories that appear in the Category pane vary, depending on the dialog from which you accessed Expression Builder.

The table describes the categories that may appear.

Category Name Description

Aggregate Content

Contains the available aggregate functions. Aggregate sources must use one of the functions listed here to specify the level of their content.

Time Dimensions

Contains the time dimensions configured in the business model. If no time dimensions exist in a business model, or if time dimensions are not pertinent to a particular Expression Builder, the Time Dimensions category is not displayed.

When you select the Time Dimensions category, each configured time dimension appears in the middle pane, and each level for the selected dimension appears in the lower pane.

Logical Tables

Contains the logical tables configured in the business model. If logical tables are not pertinent to a particular Expression Builder, the Logical Tables category is not displayed.

When you select the Logical Tables category, each logical table in the business model appears in the middle pane, and each column for the selected logical table appears in the lower pane.

Value Based Dimensions

Contains the dimensions with parent-child hierarchies configured in the business model. If no dimensions with parent-child hierarchies exist in a business model, or if dimensions with parent-child hierarchies are not pertinent to a particular Expression Builder, the Value Based Dimensions category is not displayed.

When you select the Value Based Dimensions category, the configured dimensions with parent-child hierarchies appear in the middle pane. No lower pane exists for this category.

Logical Levels

Contains the related logical levels. If level-based dimensions are not pertinent to a particular Expression Builder, the Logical Levels category is not displayed.

When you select the Logical Levels category, you can then select the appropriate logical dimension (level-based) in the middle pane, and the level itself in the lower pane.

Physical Tables

Contains the related physical tables. If physical tables are not pertinent to a particular Expression Builder, the Physical Tables category is not displayed.

Operators

Contains the available SQL logical operators.

Expressions

Contains the available expressions.

Functions

Contains the available functions. The functions that appear depend on the object you selected.

Constants

Contains the available constants.

Types

Contains the available data types.

Repository Variables

Contains the available repository variables. If no repository variables are defined, this category does not appear.

Session Variables

Contains the available system session and non-system session variables. If no session variables are defined, this category does not appear.

Setting Up an Expression

You can view the Expression Builder dialog for a derived logical column.

To set up an expression, select Functions from the Category pane, select a function type from Functions pane, then select a function from the lower pane.

Description of GUID-85DC28EE-101C-4445-8B4C-26B2C1037FCD-default.gif follows
Description of the illustration GUID-85DC28EE-101C-4445-8B4C-26B2C1037FCD-default.gif

Double-click the function you want to use to paste it in the edit pane. Then, in the edit pane, click once between the parentheses of the function to select that area as the insertion point for adding the argument of the function.

To paste a logical column at the insertion point, select Logical Tables from the Category pane, select the table you want to use in the Logical Tables pane, and then double-click the logical column in the lower pane to paste the logical column at the insertion point as the argument of the function in the edit pane. The image shows where the expression appears in the edit pane.

Description of GUID-0EC46CA6-6009-4E75-AE50-145A8FA26124-default.gif follows
Description of the illustration GUID-0EC46CA6-6009-4E75-AE50-145A8FA26124-default.gif

Navigating Within Expression Builder

Use these steps to navigate within Expression Builder.

  1. In the Category pane, select the appropriate category for the expression you want to build.

    The available expression types for the selected category appear in the middle pane.

  2. Select the appropriate item for the expression you want to build.

    The available building blocks for the selected item appear in the lower pane.

  3. Double-click a building block to display it in the edit pane.
  4. To insert an operator into the expression, click an operator on the Expression Builder toolbar.

Building an Expression

Use these steps to build an expression in Expression Builder.

  1. Navigate to the individual building blocks you want in the expression.

    The Syntax bar at the bottom of the Expression Builder dialog shows the syntax for the expression.

    For example: BETWEEN <<Upper Bound>> AND <<Lower Bound>>

  2. Add the building blocks to the edit pane.
  3. Edit the building blocks to reflect the expression you want.
  4. Use the Expression Builder toolbar to insert operators into the expression.
  5. Repeat the preceding steps until the expression is complete, and then click OK.

    The Administration Tool displays a message for any syntax errors in the expression. When the expression is syntactically correct, the Administration Tool adds the expression to the dialog from which you accessed Expression Builder.

If the parameter PREVENT_DIVIDE_BY_ZERO is set to YES in NQSConfig.INI, the Oracle BI Server prevents errors in divide-by-zero situations, even for Answers column calculations. The Oracle BI Server creates a divide-by-zero prevention expression using nullif() or a similar function when it writes the physical SQL. Because of this, you do not have to use CASE statements to avoid divide-by-zero errors, as long as PREVENT_DIVIDE_BY_ZERO is set to YES (the default value).

About the INDEXCOL Conversion Function

The INDEXCOL function enables you to build a derived logical column.

Selecting INDEXCOL automatically generates the following function template:

IndexCol( <<integer literal>>, <<expr1>> [, <<expr2>>, ?-] )

Note:

You can also use a session variable, an arithmetic expression, or a CASE WHEN statement, when an evaluation is possible without reference to back-end data, as the argument integer literal.

See INDEXCOL in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition .