Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous section

Quick Function Reference

The following table summarizes the syntax and return type of each supported MDX function.

Function Syntax Return Type
%ALL member_expression.%ALL member
%CELL %CELL(relative_column_position, relative_row_position) number or string
%CELLZERO %CELLZERO(relative_column_position, relative_row_position) number or string
%FIRST %FIRST(set_expr, optional_numeric_expr) number
%KPI %KPI(kpi_name, kpi_prop_name, kpi_series_name, parm, value, parm, value,...) number
%LABEL %LABEL(MDX_expr, label, format_details, solve_order, cell_style, heading_style) same as MDX_expr
%LAST %LAST(set_expr, optional_numeric_expr) number
%LIST %LIST(set_expr) string (comma-separated list)
%LOOKUP %LOOKUP(termlist, key, field, default) number or string
%MDX %MDX(“MDX select query”, parm, value, parm, value, parm, value,...) number or string
%NOT member_expression.%NOT member
%OR %OR(set_expr) member
%SEARCH %SEARCH.&[comparison_expression] measure search expression
%SPACE %SPACE() empty space
%TERMLIST %TERMLIST(term_list_name, INCLUDE | EXCLUDE) set
%TIMERANGE %TIMERANGE(start_member, end_member, INCLUSIVE | EXCLUSIVE) member
%TIMEWINDOW %TIMEWINDOW(set_expr, start_member, optional_end_member) set of members
%TOPMEMBERS
level_expr.%TOPMEMBERS
hierarchy_expr.%TOPMEMBERS
dimension_expr.%TOPMEMBERS
set of members
AGGREGATE AGGREGATE(set_expr, optional_numeric_expr) number
ALLMEMBERS
level_expr.ALLMEMBERS
hierarchy_expr.ALLMEMBERS
dimension_expr.ALLMEMBERS
set of members
ANCESTOR ANCESTOR(member_expr, ancestor_level) member
AVG AVG(set_expr, optional_numeric_expr) number
BOTTOMCOUNT BOTTOMCOUNT(set_expr, element_count, optional_ordering_expr) set of members or tuples
BOTTOMPERCENT BOTTOMPERCENT(set_expr, element_count, optional_ordering_expr) set of members or tuples
BOTTOMSUM BOTTOMSUM(set_expr, element_count, optional_ordering_expr) set of members or tuples
CHILDREN member_expr.CHILDREN set of members
CLOSINGPERIOD CLOSINGPERIOD(ancestor_level, member_expr) member
COUNT
COUNT(set_expr)
COUNT(set_expr, EXCLUDEEMPTY)
number
COUSIN COUSIN(member_expr, higher_member_expr) member
CROSSJOIN
CROSSJOIN(set_expr1, set_expr2)
NON EMPTY CROSSJOIN(set_expr1, set_expr2)
set of tuples
CURRENTMEMBER
hierarchy_expr.CURRENTMEMBER
dimension_expr.CURRENTMEMBER
member
DESCENDANTS DESCENDANTS(member_expression, level_expression, OPTIONAL_FLAG)
DESCENDANTS(member_expression, level_offset, OPTIONAL_FLAG)
set of members
DISTINCT DISTINCT(set_expr) set
EXCEPT
EXCEPT(set_expr1, set_expr2, ALL)
EXCEPT(set_expr1,set_expr2)
set
FILTER FILTER(set_expr, logical_expr) set
FIRSTCHILD member_expr.FIRSTCHILD member
FIRSTSIBLING member_expr.FIRSTSIBLING member
HEAD HEAD(set_expr, optional_integer_expr, optional_sample_flag) set
HIERARCHIZE, HIERARCHISE
HIERARCHIZE(set_expr)
HIERARCHIZE(set_expr, POST)
set of members
IIF IIF(logical_expr, expression1, expression2) number or string
INTERSECT INTERSECT(set_expr1, set_expr2) set
ISNULL ISNULL(scalar_expression,scalar_value_if_null) number or string
LAG member_expr.LAG(optional_nonnegative_integer_expr) member
LASTCHILD member_expr.LASTCHILD member
LASTSIBLING member_expr.LASTSIBLING member
LEAD member_expr.LEAD(optional_nonnegative_integer_expr) member
LOG LOG(numeric_expr) number
LOOKUP LOOKUP(term_list_name, lookup_value, default, alternative_field) string
MAX MAX(set_expr, optional_numeric_expr) number
MEDIAN MEDIAN(set_expr, optional_numeric_expr) number
MEMBERS
level_expr.MEMBERS
hierarchy_expr.MEMBERS
dimension_expr.MEMBERS
set of members
MIN MIN(set_expr, optional_numeric_expr) number
NEXTMEMBER member_expr.NEXTMEMBER member
NONEMPTYCROSSJOIN NONEMPTYCROSSJOIN(set_expr1, set_expr2) set of tuples
OPENINGPERIOD OPENINGPERIOD(ancestor_level, member_expr) member
ORDER
ORDER(set_expr, ordering_expr, ASC | DESC | BASC | BDESC)
ORDER(set_expr, ordering_expr)
set
PARALLELPERIOD PARALLELPERIOD(level_expr, offset, member_expr) member
PARENT member_expr.PARENT member
PERCENTILE PERCENTILE(set_expr, numeric_expr, numeric_expr, optional_percentile_value) number
PERCENTILERANK PERCENTILERANK(set_expr, numeric_expr, comparison_value) number
PERIODSTODATE PERIODSTODATE(ancestor_level, member_expr) set of members
POWER POWER(numeric_expr,numeric_expr_for_power) number
PREVMEMBER member_expr.PREVMEMBER member
PROPERTIES member_expr.PROPERTIES(property_name) string
RANK RANK(tuple_expr, set_expr, optional_numeric_expr) number
ROUND ROUND(numeric_expr,decimal_places) number
SIBLINGS member_expr.SIBLINGS set of members
SQRT SQRT(numeric_expr) number
STDDEV, STDEV STDDEV(set_expr, optional_numeric_expr) number
STDDEVP, STDEVP STDDEVP(set_expr, optional_numeric_expr) number
SUBSET SUBSET(set_expr, first_element_expr, optional_element_count) set
SUM SUM(set_expr, optional_numeric_expr) number
TAIL TAIL(set_expr, optional_integer_expr) set
TOPCOUNT TOPCOUNT(set_expr, element_count, optional_ordering_expr) set of members or tuples
TOPPERCENT TOPPERCENT(set_expr, element_count, optional_ordering_expr) set of members or tuples
TOPSUM TOPSUM(set_expr, element_count, optional_ordering_expr) set of members or tuples
UNION
UNION(set_expr1,set_expr2)
UNION(set_expr1,set_expr2, ALL)
set
VAR, VARIANCE VAR(set_expr, optional_numeric_expr) number
VARP, VARIANCEP VARP(set_expr, optional_numeric_expr) number
VISUALTOTALS VISUALTOTALS(set_expr, optional_parent_name_pattern) set of members