1. Calculation and Query Reference for Oracle Essbase
  2. MDX
  3. MDX Operators

MDX Operators

The MDX operators (mathematical, conditional and logical, and Boolean) help you define expressions when you write MDX formulas and queries for Essbase.

This section describes operators that can be used in MDX queries as part of numeric value expressions or search conditions.

Mathematical Operators

Table 4-15 Mathematical Operators in MDX

Operator Definition
+ Adds. Also can be used as a unary operator.
- Subtracts. Also can be used as a unary operator; for example, -5, -(Profit).
* Multiplies.
/ Divides.
% Evaluates percentage. For example, Member1%Member2 evaluates Member1 as a percentage of Member2. Note: Aggregate storage outline formulas cannot contain the % operator. In outline formulas, replace % with expression: (value1/value2)*100)

Conditional and Logical Operators

Conditional operators take two operands and check for relationships between them, returning TRUE or FALSE.

Table 4-16 Conditional and Logical Operators in MDX

Operator Definition
> Data value is greater than.
< Data value is less than.
= Data value is equal to.
<> Data value is not equal to.
>= Data value is greater than or equal to.
<= Data value is less than or equal to.
IN The syntax for the IN operator is as follows:
<property> IN <member>|<character_string_literal>

The first argument, <property> should be an attribute property; for example, Population in the following example.

The second argument, <member> or <character_string_literal>, should be an attribute member that is neither a level-0 member nor a generation-1 member; for example, Medium in the following example.

Example

The following filter evaluates the Population property (attribute) of the current member of Market dimension:

 Filter ([Market].Members, Market.CurrentMember.Population IN Medium)

If the population attribute of the current member is Medium, the expression returns TRUE.
IS The IS operator syntax is as follows: member1 IS member2. The IS operator is equivalent to the IS function. For details and examples, see the IS function.

Boolean Operators

Boolean operators can be used in the following functions to perform conditional tests: Filter, Case, IIF, Generate. Boolean operators operate on boolean operands (TRUE/FALSE values).

See also MDX Functions that Return a Boolean.

Table 4-17 Boolean Operators in MDX

Operator Definition
AND Logical AND linking operator for multiple value tests. Result is TRUE if both conditions are TRUE. Otherwise the result is FALSE. For an example using AND, see IsValid.
OR Logical OR linking operator for multiple value tests. Result is TRUE if either condition is TRUE. Otherwise the result is FALSE.
NOT Logical NOT operator. Result is TRUE if condition is FALSE. Result is FALSE if condition is TRUE. For an example using NOT, see IsEmpty.
XOR Logical XOR linking operator for multiple value tests. Result is TRUE if only one condition is TRUE. Otherwise the result is FALSE.