Quick Function Reference (MDX)
The following table summarizes the syntax and return type of each supported MDX function in Business Intelligence.
| 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 |
