Q: How in SSAS 2008 can I get the list of available MDX functions from DMVs?
Q: How in SSAS 2008 can I get the list of available MDX functions from DMVs?
A: SSAS 2008 provides DMV $system.MDSCHEMA_FUNCTIONS that returns list of available MDX functions with descriptions and list of parameters.
Here is the sample query:
SELECT FUNCTION_NAME, [DESCRIPTION], PARAMETER_LIST, INTERFACE_NAME
FROM $system.MDSCHEMA_FUNCTIONS
On the default SSAS 2008 installation this query returns 154 functions. Please note that some of the functions are listed multiple times - each time with a different set of available parameters:
FUNCTION_NAME | DESCRIPTION | PARAMETER_LIST | INTERFACE_NAME |
| ADDCALCULATEDMEMBERS | Returns a set generated by adding calculated members to a specified set. | «Set» | Set |
| AGGREGATE | Returns a calculated value using the appropriate aggregate function, based on the aggregation type of the member. | «Set»[, «Numeric Expression»] | Statistical |
| ALLMEMBERS | Returns a set containing all members of the level, including calculated members. | (none) | Set |
| ALLMEMBERS | Returns a set containing all members of the hierarchy, including calculated members. | (none) | Set |
| ANCESTOR | Returns the ancestor of a member at a specified level. | «Member», «Level» | Navigation |
| ANCESTOR | Returns the ancestor of a member at a specific distance away in the hierarchy. | «Member», «Distance» | Navigation |
| ANCESTORS | Returns a set of all ancestors of a member at a specific distance above the member in the hierarchy. | «Member», «Distance» | Navigation |
| ANCESTORS | Returns a set of all ancestors of a member at a specified level. | «Member», «Level» | Navigation |
| ASCENDANTS | Returns the set of the ascendants of the member. | «Member» | Navigation |
| AVG | Returns the average value of a numeric expression evaluated over a set. | «Set»[, «Numeric Expression»] | Statistical |
| AXIS | Returns a set defined in an axis. | «Numeric Expression» | Metadata |
| BOTTOMNCOUNT | Returns a specified number of items from the bottom of a set, optionally sorting the set first. | «Set», «Count»[, «Numeric Expression»] | Set |
| BOTTOMPERCENT | Sorts a set and returns the specified number of bottommost elements whose cumulative total is at least a specified percentage. | «Set», «Percentage», «Numeric Expression» | Set |
| BOTTOMSUM | Sorts a set and returns the specified number of bottommost elements whose cumulative total is at least a specified value. | «Set», «Value», «Numeric Expression» | Set |
| CALCULATIONCURRENTPASS | Returns the current calculation pass of a cube for the specified query context. | (none) | Other |
| CALCULATIONPASSVALUE | Returns the value of a numeric MDX expression evaluated over the specified calculation pass of a cube. | «Numeric Expression», «Pass Value»[[, «Access Flag»], ALL] | Other |
| CHILDREN | Returns the children of a member. | (none) | Navigation |
| CLOSINGPERIOD | Returns the last sibling among the descendants of a member at a specified level. | [«Level»[, «Member»] ] | Time |
| COALESCEEMPTY | Coalesces an empty cell value to a number. | «Numeric Expression»[, «Numeric Expression»...] | Statistical |
| CORRELATION | Returns the correlation of two series evaluated over a set. | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| DIMENSIONS.COUNT | Counts the number of hierarchies in the cube. | (none) | Metadata |
| COUNT | Counts the number of members in the tuple. | (none) | Metadata |
| COUNT | Counts the number of tuples in the set. | (none) | Statistical |
| LEVELS.COUNT | Counts the number of levels in the hierarchy. | (none) | Metadata |
| COUNT | Returns the number of tuples in a set, optionally including or excluding empty tuples. | «Set»[, EXCLUDEEMPTY | INCLUDEEMPTY] | Statistical |
| COUSIN | Returns the child member with the same relative position under a parent member as the specified child member. | «Member1», «Member2» | Navigation |
| COVARIANCE | Returns the covariance of two series evaluated over a set, using the biased population formula. | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| COVARIANCEN | Returns the covariance of two series evaluated over a set, using the unbiased population formula. | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| CROSSJOIN | Returns the cross product of two sets. | «Set1», «Set2» | Set |
| CURRENT | Returns the current tuple from a set during an iteration. | (none) | Navigation |
| CURRENTMEMBER | Returns the current member along a hierarchy | (none) | Navigation |
| CURRENTORDINAL | Returns the current ordinal during an iteration over a set | (none) | Navigation |
| CUSTOMDATA | Returns the value of CustomData property | (none) | Other |
| DATAMEMBER | Returns the system-generated data member associated with a nonleaf member. | (none) | Navigation |
| DEFAULTMEMBER | Returns the default member of a hierarchy. | (none) | Navigation |
| DESCENDANTS | Returns the set of descendants of a member at a specified level, optionally including or excluding descendants in other levels. | «Member»[, «Level»[, «Desc_flags»]] | Set |
| DESCENDANTS | Returns the set of descendants of a member at a specific distance away in the hierarchy, optionally including or excluding descendants in other levels. | «Member»,«Distance»[, «Desc_flags»] | Set |
| DISTINCT | Returns a set, removing duplicate tuples from a specified set. | «Set» | Set |
| DISTINCTCOUNT | Returns the number of distinct tuples in a set. | «Set» | Statistical |
| DRILLDOWNLEVEL | Drills down the members of a set one level below the lowest level represented in the set, or to one level below an optional level of a member represented in the set. | «Set»[, «Level» ] | UI |
| DRILLDOWNLEVEL | Drills down the members of a set one level below the lowest level represented in the set, or to one level below a dimension optionally selected by its zero-based index of a member represented in the set. | «Set»[, , «Index»] | UI |
| DRILLDOWNLEVELBOTTOM | Drills down the members of a specified count of bottom members of a set, at a specified level, to one level below. | «Set», «Count»[, [«Level»][, «Numeric Expression»] ] | UI |
| DRILLDOWNLEVELTOP | Drills down a specified count of top members of a set, at a specified level, to one level below. | «Set», «Count»[, [«Level»][, «Numeric Expression»] ] | UI |
| DRILLDOWNMEMBER | Drills down the members in a specified set that are present in a second specified set. | «Set1», «Set2»[, RECURSIVE] | UI |
| DRILLDOWNMEMBERBOTTOM | Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of bottommost members. | «Set1», «Set2», «Count»[, [«Numeric Expression»] [, RECURSIVE] ] | UI |
| DRILLDOWNMEMBERTOP | Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of topmost members. | «Set1», «Set2», «Count»[, [«Numeric Expression»] [, RECURSIVE] ] | UI |
| DRILLUPLEVEL | Drills up the members of a set that are below a specified level. | «Set»[, «Level»] | UI |
| DRILLUPMEMBER | Drills up the members in a specified set that are present in a second specified set. | «Set1», «Set2» | UI |
| EXCEPT | Finds the difference between two sets, optionally retaining duplicates. | «Set1», «Set2»[, ALL] | Set |
| EXISTING | (none) | Set | |
| EXISTS | «Set1», «Set2» | Set | |
| EXTRACT | Returns a set of tuples from extracted dimension elements. | «Set», «Dimension»[, «Dimension»...] | Set |
| FILTER | Returns the set resulting from filtering a set based on a search condition. | «Set», «Search Condition» | Set |
| FIRSTCHILD | Returns the first child of a specified member. | (none) | Navigation |
| FIRSTSIBLING | Returns the first child of the parent of a specified member. | (none) | Navigation |
| GENERATE | Applies a set to each member of another set and joins the resulting sets by union. | «Set1», «Set2»[, ALL] | Set |
| GENERATE | Returns a concatenated string created by evaluating a string expression over a set. | «Set», «String Expression»[, «Delimiter»] | String |
| HEAD | Returns the first specified number of elements in a set. | «Set»[, «Numeric Expression»] | Set |
| HIERARCHIZE | Orders the members of a specified set in a hierarchy in natural or, optionally, post-natural order. | «Set»[, POST] | Set |
| HIERARCHY | Returns the hierarchy of a specified level. | (none) | Metadata |
| HIERARCHY | Returns the hierarchy of a specified dimension. | (none) | Metadata |
| IIF | Returns one of values determined by a logical test. | «Logical Expression», «object», «object» | Value |
| INTERSECT | Returns the intersection of two sets, optionally retaining duplicates. | «Set1», «Set2»[, ALL] | Set |
| IS | Returns TRUE if two compared objects are equivalent, FALSE otherwise. | (none) | Value |
| ISANCESTOR | Returns TRUE if a specified member is an ancestor of another specified member, FALSE otherwise. | «Member1»,«Member2» | Navigation |
| ISEMPTY | Returns TRUE if the evaluated expression is the empty cell value, FALSE otherwise. | «Value Expression» | Value |
| ISGENERATION | Returns TRUE if a specified member is in a specified generation, FALSE otherwise. | «Member»,«Numeric Expression» | Navigation |
| ISLEAF | Returns TRUE if a specified member is a leaf member, FALSE otherwise. | «Member» | Navigation |
| ISSIBLING | Returns TRUE if a specified member is a sibling of another specified member, FALSE otherwise. | «Member1»,«Member2» | Navigation |
| ITEM | Returns a member from a specified tuple. | «Numeric Expression» | Other |
| ITEM | Returns a tuple from a specified set. | «String Expression»[, «String Expression»...] | «Index» | Other |
| KPICURRENTTIMEMEMBER | Returns the current time member of the KPI | «String Expression» | KPI |
| KPIGOAL | Returns the goal of the KPI. | «String Expression» | KPI |
| KPISTATUS | Returns the measure with normalized status between -1 and 1 of the KPI. | «String Expression» | KPI |
| KPITREND | Returns the measure with normalized trend between -1 and 1 of the KPI | «String Expression» | KPI |
| KPIVALUE | Returns the measure with normalized value between -1 and 1 of the KPI | «String Expression» | KPI |
| KPIWEIGHT | Returns the measure with weight of the KPI | «String Expression» | KPI |
| LAG | Returns the member that is a specified number of positions prior to a specified member along the dimension of the member. | «Numeric Expression» | Navigation |
| LASTCHILD | Returns the last child of a specified member. | (none) | Navigation |
| LASTPERIODS | Returns a set of members prior to and including a specified member. | «Index»[, «Member»] | Time |
| LASTSIBLING | Returns the last child of the parent of a specified member. | (none) | Navigation |
| LEAD | Returns the member that is a specified number of positions following a specified member along the dimension of the member. | «Numeric Expression» | Navigation |
| LEVEL | Returns the level of a member. | (none) | Metadata |
| LEVELS | Returns the level whose zero-based position in a dimension is specified by a numeric expression. | «Numeric Expression» | Metadata |
| LINKMEMBER | Returns the member equivalent to a specified member in a specified hierarchy. | «Member>, «Hierarchy» | Navigation |
| LINREGINTERCEPT | Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b. | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| LINREGPOINT | Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b. | «Numeric Expression», «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| LINREGR2 | Calculates the linear regression of a set and returns R² (the coefficient of determination). | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| LINREGSLOPE | Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b. | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| LINREGVARIANCE | Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b. | «Set», «Numeric Expression»[, «Numeric Expression»] | Statistical |
| LOOKUPCUBE | Returns the value of an MDX expression evaluated over another specified cube in the same database. | «Cube Name», «Numeric Expression» | Navigation |
| MAX | Returns the maximum value of a numeric expression evaluated over a set. | «Set»[, «Numeric Expression»] | Statistical |
| MEASUREGROUPMEASURES | Returns the set of measures in the measure group. | «String Expression» | Set |
| MEDIAN | Returns the median value of a numeric expression evaluated over a set. | «Set»[, «Numeric Expression»] | Statistical |
| MEMBERS | Returns the set of all members in a specified hierarchy. | (none) | Set |
| MEMBERS | Returns the set of all members at a specified level in a dimension. | (none) | Set |
| MEMBERTOSTR | Returns a string in Multidimensional Expressions (MDX) format from a member. | «Member» | String |
| MEMBERVALUE | Returns the value of a member with its original data type. | (none) | Value |
| MIN | Returns the minimum value of a numeric expression evaluated over a set. | «Set»[, «Numeric Expression»] | Statistical |
| MTD | Returns a set of members from the Month level in a Time dimension starting with the first period and ending with a specified member. | [«Member»] | Time |
| NAME | Returns the name of a specified member. | (none) | Metadata |
| NAME | Returns the name of a specified hierarchy. | (none) | Metadata |
| NAME | Returns the name of a specified level. | Level | Metadata |
| NAMETOSET | Returns a set containing a single member based on a string expression containing a member name. | «Member Name» | String |
| NEXTMEMBER | Returns the next member in the level that contains a specified member. | (none) | Navigation |
| NONEMPTY | Returns subset of first set with removed empty tuples based on the cross product with a second set. | «Set1», «Set2» | Set |
| NONEMPTYCROSSJOIN | Returns the cross product of two or more sets as a set, excluding empty members. | «Set1», «Set2»[, «Set3»...][, «Crossjoin Count»] | Set |
| OPENINGPERIOD | Returns the first sibling among the descendants of a specified level, optionally at a specified member. | [«Level»[, «Member»] ] | Time |
| ORDER | Arranges the members of a specified set, optionally preserving or breaking the hierarchy. | «Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC] | Set |
| ORDINAL | Returns the zero-based ordinal value associated with a specified level. | (none) | Metadata |
| PARALLELPERIOD | Returns a member from a prior period in the same relative position as a specified member. | [«Level»[, «Numeric Expression»[, «Member»] ] ] | Time |
| PARENT | Returns the parent of a specified member. | (none) | Navigation |
| PERIODSTODATE | Returns a set of members (periods) from a specified level starting with the first member and ending with a specified member. | [«Level»[, «Member»] ] | Time |
| PREDICT | Returns a value of a numeric expression evaluated over a data mining model. | «Mining Model Name», «Numeric Expression» | Other |
| PREVMEMBER | Returns the previous member in the level that contains a specified member. | (none) | Navigation |
| PROPERTIES | Returns a string containing the value of the specified member property. | «String Expression»[, TYPED] | Navigation |
| QTD | Returns a set of members from the Quarter level in a Time dimension starting with the first period and ending with a specified member. | [«Member»] | Time |
| RANK | Returns the one-based rank of a specified tuple in a specified set. | «Tuple», «Set» | Statistical |
| ROLLUPCHILDREN | Returns a value generated by rolling up the values of the children of a specified member using the specified unary operator. | «Member», «String Expression» | Statistical |
| SETTOARRAY | Converts one or more sets to an array for use in a user-defined function. | «Set»[, «Set»...][, «Numeric Expression»] | Other |
| SETTOSTR | Constructs a string in MDX format from a set. | «Set» | String |
| SIBLINGS | Returns the set of siblings of a specified member, including the member itself. | (none) | Navigation |
| STDDEV | Returns the standard deviation of a numeric expression evaluated over a set, using an unbiased population. (Alias for Stdev.) | «Set»[, «Numeric Expression»] | Statistical |
| STDDEVP | Returns the standard deviation of a numeric expression evaluated over a set, using a biased population. (Alias for StdevP.) | «Set»[, «Numeric Expression»] | Statistical |
| STDEVP | Returns the standard deviation of a numeric expression evaluated over a set, using an unbiased population. | «Set»[, «Numeric Expression»] | Statistical |
| STDEVP | Returns the standard deviation of a numeric expression evaluated over a set, using a biased population. | «Set»[, «Numeric Expression»] | Statistical |
| STRIPCALCULATEDMEMBERS | Returns a set generated by removing calculated members from a specified set. | «Set» | Set |
| STRTOMEMBER | Returns a member from a string expression in MDX format. | «String Expression» | String |
| STRTOSET | Constructs a set from a specified string expression in MDX format. | «String Expression» | String |
| STRTOTUPLE | Constructs a tuple from a specified string expression in MDX format. | «String Expression» | String |
| STRTOVALUE | Returns a value from a string expression. | «String Expression» | String |
| SUBSET | Returns a subset of members from a specified set. | «Set», «Start»[, «Count»] | Set |
| SUM | Returns the sum of a numeric expression evaluated over a specified set. | «Set»[, «Numeric Expression»] | Statistical |
| TAIL | Returns a subset of members from the end of a specified set. | «Set»[, «Count»] | Set |
| TOGGLEDRILLSTATE | Toggles the drill state of members. | «Set1», «Set2»[, RECURSIVE] | UI |
| TOPCOUNT | Returns a specified number of items from the topmost members of a specified set, optionally ordering the set first. | «Set», «Count»[, «Numeric Expression»] | Set |
| TOPPERCENT | Sorts a set and returns the topmost elements whose cumulative total is at least a specified percentage. | «Set», «Percentage», «Numeric Expression» | Set |
| TOPSUM | Sorts a set and returns the topmost elements whose cumulative total is at least a specified value. | «Set», «Value», «Numeric Expression» | Set |
| TUPLETOSTR | Returns a string in MDX format from a specified tuple. | «Tuple» | String |
| UNION | Returns a set generated by the union of two sets, optionally retaining duplicate members. | «Set1», «Set2»[, ALL] | Set |
| UNIQUENAME | Returns the unique name of a specified hierarchy. | (none) | Metadata |
| UNIQUENAME | Returns the unique name of a specified member. | (none) | Metadata |
| UNIQUENAME | Returns the unique name of a specified level. | (none) | Metadata |
| UNKNOWNMEMBER | Returns the domain name and user name of the current connection. | (none) | Navigation |
| UNORDER | Returns a set without ordering. | «Set» | Set |
| VALIDMEASURE | Returns a value computed at the granularity of the measure group without unrelated dimensions and/or attributes. | «Tuple» | Value |
| VALUE | Returns the value of a specified member. | (none) | Value |
| VAR | Returns the variance of a numeric expression evaluated over a set, using an unbiased population. | «Set»[, «Numeric Expression»] | Statistical |
| VARIANCE | Returns the variance of a numeric expression evaluated over a set, using an unbiased population. (Alias for Var function.) | «Set»[, «Numeric Expression»] | Statistical |
| VARIANCEP | Returns the variance of a numeric expression evaluated over a set, using a biased population. (Alias for VarP function.) | «Set»[, «Numeric Expression»] | Statistical |
| VARP | Returns the variance of a numeric expression evaluated over a set, using a biased population. | «Set»[, «Numeric Expression»] | Statistical |
| VISUALTOTALS | Returns a set generated by dynamically totaling child members in a specified set, optionally using a pattern for the name of the parent member in the result set. | «Set», «Pattern» | Statistical |
| WTD | Returns a set of members from the Week level in a Time dimension starting with the first period and ending with a specified member. | [«Member»] | Time |
| YTD | Returns a set of members from the Year level in a Time dimension starting with the first period and ending with a specified member. | [«Member»] | Time |
You can use this DMV to get a quick help about MDX functions.