Keywords in expressions

The AS keyword can be used to specify the name of an expression in the context of a custom expression.

Examples:

Sum([Sales]) as [Total Sales]

<[Region_Name] as [Region]>

The CROSS keyword can be used in categorical custom expressions to specify that a cross join should be performed between the category levels. This means that all possible combinations of categories are displayed, even those that currently hold no data.

Example:

<[A] CROSS [B]>

The DISTINCT keyword can be used within aggregated expressions to specify that the aggregation should be computed on the distinct values only.

Example:

Avg(distinct [Column])

ELSE is used in case statements to define what happens if a condition is not met.

The case statement has two different forms.

Simple:

case Arg1 when Arg2 then Arg3 else Arg4 end

The Arg1 expression is evaluated and when Arg1 is equal to Arg2 then Arg3 is returned.

Multiple when/then expressions can be entered and are evaluated in left to right order.

Searched:

case when Arg1 then Arg2 else Arg3 end

Returns Arg2 if Arg1=true, and Arg3 if Arg1=false.

Multiple when/then expressions can be entered and are evaluated in left to right order.

Examples:

Case when 1 < 2 then "a" when 1 < 3 then "b" else "c" end

Case [Column] when 3 then "a" when 2 then "b" else "c" end

END is used in case statements to signal the end of the statement.

See ELSE for examples of case statements.

FALSE is a boolean constant that can be used in logical expressions.

Example:

false

IS is a keyword that can be used check whether an expression is null or not.

Examples:

[Column] Is Not Null

Avg([Column]) Is Null

The NEST keyword can be used in categorical custom expressions to specify that the category levels should be nested. This means that only those combinations of values that actually hold data are shown.

Example:

<[A] nest [B]>

NULL is a keyword that can either be used to specify a null (empty) value, or, it can be a part of the 'Is Not Null' or 'Is Null' operators.

Examples:

null

[Column] Is Not Null

OVER is a keyword that can be used after an aggregation to specify that the aggregation should be calculated over another window than the default. The OVER keyword is always used together with an OVER function which specifies how to group the data, based on the nodes in the referenced hierarchy.

Example:

Sum([Sales]) OVER (AllPrevious([Axis.X]))

The THEN keyword can be used in two different contexts: If it is part of a CASE expression, it simply determines what happens when a condition is met. THEN can also be used to calculate post-aggregation expressions, in expressions on continuous axes.

In the post-aggregation context, the part of the expression after the THEN keyword is calculated on top of the aggregated data, and the aggregated value is referred to using the [Value] name.

Examples:

CASE WHEN 1 < 2 THEN "a" WHEN 1 < 3 THEN "b" ELSE "c" end

Sum([Sales]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

TRUE is a boolean constant that can be used in logical expressions.

Example:

true

WHEN is used in case statements to define what happens if a condition is met.

See ELSE for examples of case statements.