Operators specify what happens to the values on either side of the operator. Operators are similar to functions, in that they manipulate data items and return a result.
(
Inserts an open parenthesis in your expression.
Syntax
(exp)
)
Inserts a close parenthesis in your expression.
Syntax
(exp)
*
Multiplies two numeric values.
Syntax
value1 * value2
,
Separates expression components.
Syntax
exp(param1, param2)
/
Divides two numeric values.
Syntax
value1 / value2
||
Concatenates strings.
Syntax
string1 || string2
+
Adds two values.
Syntax
value1 + value2
-
Subtracts two numeric values or negates a numeric value.
Syntax
value1 - value2
or
- value
<
Compares values against a defined value and retrieves the values that are less than the defined value.
Syntax
value1 < value2
<=
Compares values against a defined value and retrieves the values that are less than or equal to the defined value.
Syntax
value1 <= value2
<>
Compares values against a defined value and retrieves the values that are not equal to the defined value.
Syntax
value1 <> value2
=
Compares values against a defined value and retrieves the values that are equal to the defined value.
Syntax
value1 = value2
>
Compares values against a defined value and retrieves the values that are greater than the defined value.
Syntax
value1 > value2
->
Separates the components in a literal member expression.
Syntax
[namespace].[dimension].[hierarchy].[level]->[L1]
>=
Compares values against a defined value and retrieves the values that are greater than or equal to the defined value.
Syntax
value1 >= value2
and
Returns TRUE if the conditions on both sides of the expression are true.
Syntax
arg1 AND arg2
auto
Works with summary expressions to define the scope to be adjusted based on the grouping columns in the query. The scope is context dependent.
Syntax
aggregate_function ( expression AUTO )
between
Determines if a value falls in a given range.
Syntax
name BETWEEN value1 and value2
case
Works with When, Then, Else, and End.
Syntax
CASE expression { WHEN expression THEN expression } [
ELSE expression ] END contains
Determines if a string contains another string.
Syntax
string1 CONTAINS string2
currentMeasure
Keyword that can be used as the first argument of member summary functions.
Syntax
aggregate_function( currentMeasure within set expression
)
default
Works with the LOOKUP construct.
Syntax
lookup(....) in (....) DEFAULT(....)
distinct
A keyword used in an aggregate expression, to include only distinct occurrences of values. See also the function unique.
Syntax
DISTINCT dataItem
Example
count ( DISTINCT [OrderDetailQuantity] )
Result: 1704
else
Works with the If or Case constructs.
Syntax
IF (condition) THEN .... ELSE (expression) , or CASE
.... ELSE expression END
end
Works with the Case or When constructs.
Syntax
CASE .... END
ends with
Determines if a string ends with a given string.
Syntax
string1 ENDS WITH string2
for
Works with summary expressions to define the scope of the aggregation in the query.
Syntax
aggregate_function ( expression FOR expression { , expression
} ) for all
Works with summary expressions to define the scope to be all the specified grouping columns in the query. See also FOR clause.
Syntax
aggregate_function ( expression FOR ALL expression {
, expression } ) for any
Works with summary expressions to define the scope to be adjusted based on a subset of the grouping columns in the query. Equivalent to FOR clause.
Syntax
aggregate_function ( expression FOR ANY expression {
, expression } ) for report
Works with summary expressions to set the scope to be the whole query. See also FOR clause.
Syntax
aggregate_function ( expression FOR REPORT )
if
Works with the Then and Else constructs.
Syntax
IF (condition is true) THEN (action) ELSE (alternate
action)
in
Determines if a value exists in a given list of values.
Syntax
exp1 IN (exp_list)
in_range
Determines if an item exists in a given list of constant values or ranges.
Syntax
expression IN_RANGE { constant : constant [ , constant
: constant ] } Example 1
[code] IN_RANGE { 5 } Result: This is equivalent to [code] = 5
Example 2
[code] IN_RANGE { 5: } Result: This is equivalent to [code] >= 5
Example 3
[code] IN_RANGE { :5 } Result: This is equivalent to [code] <= 5
Example 4
[code] IN_RANGE { 5:10 } Result: This is equivalent to ( [code] >= 5 and [code] <= 10 )
Example 5
[code] IN_RANGE { :5,10,20: } Result: This is equivalent to ( [code] <= 5 or [code] = 10 or [code] >= 20 )
is missing
Determines if a value is undefined in the data.
Syntax
value IS MISSING
is null
Determines if a value is undefined in the data.
Syntax
value IS NULL
is not missing
Determines if a value is defined in the data.
Syntax
value IS NOT MISSING
is not null
Determines if a value is defined in the data.
Syntax
value IS NOT NULL
like
Determines if a string matches the pattern of another string.
Syntax
string1 LIKE string2
lookup
Finds and replaces data with a value you specify. It is preferable to use the CASE construct.
Syntax
LOOKUP (name) in (value1 --> value2) default (expression)
Example
lookup([Country]) in ('Canada'-->([List Price] * 0.60),
'Australia'-->([List Price] * 0.80)) default([List Price]) not
Returns TRUE if the condition is false, otherwise returns FALSE.
Syntax
NOT arg
or
Returns TRUE if either of the two conditions on both sides of the expression are true.
Syntax
arg1 OR arg2
prefilter
Performs a summary calculation before applying the summary filter.
Syntax
summary ([expression] PREFILTER)
rows
Counts the number of rows output by the query. Use with Count().
Syntax
count(ROWS)
starts with
Determines if a string starts with a given string.
Syntax
string1 STARTS WITH string2
then
Works with the If or Case constructs.
Syntax
IF (condition) THEN ...., or CASE expression WHEN expression
THEN .... END
when
Works with the Case construct.
Syntax
CASE [expression] WHEN .... END
