Declaration of functions written in MDX.
icCube allows for declaring a function within the scope of a SELECT query. The syntax is very much similar to the declaration of a calculated member.
WITH FUNCTION fun(a,b) AS a + b
The parameter declaration supports some sort of type declaration. Currently the 'Value' type is supported.
WITH FUNCTION fun( Value val ) AS ....
means that the 'val' parameter will be converted to a value before being used within the function. When calling the function with a tuple ' t ', this is equivalent to ' fun( t.value ) '. Declaring a parameter as a 'Value' is simplifying the call of the method and ensure that the value is computed once only. E.g.:
WITH FUNCTION square( Value val ) AS val * val
Note that a function can be passed as a function parameter.
A function can be declared as static using the STATIC keyword. In that case, the evaluation of the function is performed outside the current context and both the slicer and the sub-select are ignored. The optional CACHED property allows for caching the evaluation (default is true). Applies to STATIC or CONST functions only.
WITH STATIC FUNCTION fun() AS ... [, CACHED = true|false ]
A function can be declared as const using the CONST keyword. In that case, the evaluation of the function is considered as a constant and is evaluated once outside the current context except for the slicer and the sub-select. The optional CACHED property allows for caching the evaluation (default is true). Applies to STATIC or CONST functions only. Several limitations apply to const functions as described in the following examples.
WITH CONST FUNCTION fun() AS ... [, CACHED = true|false ]
A function can be declared as native using the NATIVE keyword. In that case, it is not defined using MDX. It can for example be defined as a R script.
WITH NATIVE FUNCTION fun() AS
rnorm( 5, mean=0.5, sd=0.25 )
Similarly, functions can be declared (and removed) at schema level using the CREATE / DROP statements.
DROP FUNCTION fun
CREATE [STATIC|CONST] [NATIVE] FUNCTION fun(a,b) AS a+b, [ , CUBE = cube-name ] [, CACHED = yes|true ]
The following examples are demonstrating several features of these function declarations.
Find here the value of the XMLA properties when not specified otherwise.