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 |