Special Functions

Use special functions from the Functions list to specify the expressions for the destination cube coordinates (points).

Operations

Elements for standard operations are the following:

Operation	Brief description
+	Addition.
-	Subtraction.
*	Multiplication.
/	Division.
>	Greater than.
<	Less than.
>=	Greater than or equal to.
<=	Less than.
=	Equal.

Math

Elements for mathematical operations are the following:

Function	Syntax	Description
Nz	Nz(Term, E)	It returns the expression result if the value of source cube coordinate is NoData, otherwise it returns value of the source cube coordinate. Parameters: Term is the coordinate of the source cube. E is the expression.
Power	Power(Term, K)	It returns the result of raising a real number to real power. Parameters: Term is the coordinate of the source cube. K is the power.
Abs	Abs(Term)	It returns the absolute value (modulus) of a real number. Parameter: Term is the coordinate of the source cube.
Trunc	Trunc(Term, K)	It truncates fractional part of a value to a given accuracy. Parameters: Term is the coordinate of the source cube. K is the precision.
Round	Round(Term, K)	It rounds a value to a specified number of decimal places. If K is less then 0, the value is approximated to the defined number to the left of the decimal point. If K equals 0, the value is approximated to the nearest integer value. If K is greater than 0, the value is approximated to a defined number of decimal places to the right of the decimal point. Parameters: Term is the coordinate of the source cube. K is the number of decimal places, to which a value should be rounded.
Exp	Exp(K)	It returns the result of raising the number "e" (the base of a natural logarithm) to a given power. Parameter: K is the power.
Ln	Ln(Term)	It returns the natural logarithm for the specified number. Parameter: Term is the coordinate of the source cube.
MAvg	MAvg(Term1, Term2, Term3, Term4)	It calculates average of the selected elements. Parameters: Term1, Term2, Term3, Term4 - source cube coordinates.

Boolean

Elements for logical operations are the following:

Function	Syntax	Description
AND	AND	Logical AND.
OR	OR	Logical OR.
NOT	NOT	Logical negation.
IIF	IIF(<condition>,<expression , if condition is true>,<expression , if condition is false>).	Conditional operator.
IsNull	IsNull(Term, V1, V2)	The function returns V1 value if the selected cube coordinate (Term) is an empty value (NULL) or there is no record for this coordinate; otherwise the function returns V2. Parameters: Term is the coordinate of the source cube. V1, V2 are values; the source-cube coordinates may be used as values.

Other

Function	Syntax	Description
Min	Min(Term1, Term2)	It selects the least value of two compared elements. Parameters: Term1, Term2 - source cube coordinates.
Max	Max(Term1, Term2)	It selects the greatest of two compared elements. Parameters: Term1, Term2 - source cube coordinates.
MMin	MMin(Term1, Term2, …TermN)	It selects the minimum value in the array. Parameters: Term1, Term2, ...TermN - source cube coordinates.
MMax	MMax(Term1, Term2, …TermN)	It selects the maximum value in the array. Parameters: Term1, Term2, ...TermN - source cube coordinates.
Shift	Shift(Term, P, K)	The result of calculating the function is the value of source cube coordinate shifted by date by the specified number of steps. To use this function, open the Structure page of multidimensional calculation wizard of the cube source for a calendar dimension, open context menu and select the Date Shift (SHIFT) checkbox. Parameters: Term is the coordinate of the source cube. P - the period: Year, Quart, Month, Day. K - the number of steps in the specified period (negative values are possible).
NoData	NoData(Term, C1, C2)	The function returns the following: The value of defined cube coordinate if a record is defined for this coordinate, and this record is not NULL. The C1 constant if a record is not defined for this cube coordinate. The C2 constant if its defined cube-coordinate value is NULL. Parameters: Term is the coordinate of the source cube. C1, C2 are the constants. NOTE. It is important to make the distinction between an empty or null record and a merely undefined record. A record is empty if and only if it is defined but its value is NULL.
Day	-	It is used to specify period of the Shift function.
Month	-	It is used to specify period of the Shift function.
Quart	-	It is used to specify period of the Shift function.
Year	-	It is used to specify period of the Shift function.
Check	Check(Term)	This is used to check values by specified coordinate. If the specified coordinate contains empty value (NULL), then the entire formula is not calculated. Parameter: Term is the coordinate of the source cube.

Working with Sets

Functions are used to work with set of values by a cube coordinate. In order the functions work, it is assumed that the table that is used to build a cube includes one indicator more that the number of dimensions in the cube. Function operand is the coordinate of the cube value which, due to the missing dimension in the cube is considered as a series of values.

NOTE. If coordinates of the source cube used as parameters in functions used to work with sets, are calculated, that is, they have formulas set in the multidimensional calculation, which uses the Consider Dependencies between Formulas checkbox is used, result of the function with the set is 0.

Function	Syntax	Description
DMin	DMin(Term)	It selects the least value from the series of values in the cube coordinate. Parameter: Term is the coordinate of the source cube.
DMax	DMax(Term)	It selects the greatest value from the series of values in the cube coordinate. Parameter: Term is the coordinate of the source cube.
DMedian	DMedian(Term)	It calculates median by values from the series of values in the cube coordinate. Parameter: Term is the coordinate of the source cube. NOTE. This function is relevant for the Oracle DBMS 10.2 or later.
DSum	DSum(Term)	It calculates sum of values from the values in the cube coordinate. Parameter: Term is the coordinate of the source cube.
DCnt	DCnt(Term)	It calculates the number of values from the series of values in the cube coordinate. Parameter: Term is the coordinate of the source cube.
DCntD	DCntD(Term)	It calculates the number of unique values from the series of values in the cube coordinate. Parameter: Term is the coordinate of the source cube.
DAvg	DAvg(Term)	It calculates average of values from the series of values in the cube coordinate. Parameter: Term is the coordinate of the source cube.

NOTE. Work with sets is supported for Microsoft SQL Server, Teradata and Oracle DBMS.