Using Iterative Functions

Table 118 lists the functions that loop through sets of data, and perform search conditions and results that you specify:

Table 118. List of Iterative Functions

FunctionDescription

Filter

Returns the subset of tuples in set for which the value of the search condition is TRUE.

IIF

Performs a conditional test and returns an appropriate numeric expression or set depending on whether the test evaluates to TRUE or FALSE.

Case

Performs conditional tests and returns the results you specify.

Generate

An iterative function. For each tuple in set1, returns set2.

Filter Function Example

The following query returns all Market dimension members for which the expression IsChild([Market].CurrentMember,[East]) returns TRUE; the query returns all children of East.

SELECT
  Filter([Market].Members,
    IsChild([Market].CurrentMember,[East])
  )
ON COLUMNS
FROM Sample.Basic

The Filter function in MDX is comparable to the RESTRICT command in Report Writer.

For more examples of Filter and other iterative functions, see the Oracle Essbase Technical Reference.