Analysis Services
MDX Function List
This topic contains lists of the Multidimensional Expressions (MDX) functions in Microsoft® SQL Server™ 2000 Analysis Services. You can use these lists to find functions by their category of return value, or you can select a function by name from the alphabetical list in the table of contents.
Samples Used in Examples
For many expression examples in the following topics, SampleSet is defined as:
{USA, Buffalo, France, NYC, London, California, LA, Nice, UK, Paris}
The following table lists sales data for each member of the set.
| Location | 1995 sales | 1996 sales |
|---|---|---|
| UK | 1900 | 1700 |
| London | 250 | 300 |
| France | 2500 | 2500 |
| Paris | 365 | 250 |
| Nice | 27 | 100 |
| USA | 5000 | 6500 |
| Boston | 900 | 1100 |
| Buffalo | 300 | 200 |
| California | 2000 | 3500 |
| Los Angeles | 500 | 900 |
MDX Function Groups
The following tables list the MDX functions grouped by their return value categories. You can use the links in the tables to jump to the function reference topics.
Array Functions
| Function | Description |
|---|---|
| SetToArray | Converts one or more sets to an array for use in a user-defined function. |
Dimension, Hierarchy, and Level Functions
Dimension Functions
| Function | Description |
|---|---|
| Dimension | Returns the dimension that contains a specified hierarchy, level, or member. |
| Dimensions | Returns the dimension whose zero-based position within the cube is specified by a numeric expression or whose name is specified by a string. |
Hierarchy Functions
| Function | Description |
|---|---|
| Hierarchy | Returns the hierarchy of a level or member. |
Level Functions
| Function | Description |
|---|---|
| Level | Returns the level of a member. |
| Levels | Returns the level whose position in a dimension is specified by a numeric expression or whose name is specified by a string expression. |
Logical Functions
| Function | Description |
|---|---|
| Is | Returns True if two compared objects are equivalent, False otherwise. |
| IsAncestor | Determines whether a specified member is an ancestor of another specified member. |
| IsEmpty | Determines whether an expression evaluates to the empty cell value. |
| IsGeneration | Determines whether a specified member is in a specified generation. |
| IsLeaf | Determines whether a specified member is a leaf member. |
| IsSibling | Determines whether a specified member is a sibling of another specified member. |
Member Functions
| Function | Description |
|---|---|
| Ancestor | Returns the ancestor of a member at a specified level or at a specified distance from the member. |
| ClosingPeriod | Returns the last sibling among the descendants of a member at a level. |
| Cousin | Returns the member with the same relative position under a member as the member specified. |
| CurrentMember | Returns the current member along a dimension during an iteration. |
| DataMember | Returns the system-generated data member associated with a nonleaf member. |
| DefaultMember | Returns the default member of a dimension or hierarchy. |
| FirstChild | Returns the first child of a member. |
| FirstSibling | Returns the first child of the parent of a member. |
| Ignore | Reserved. |
| Item | Returns a member from a tuple. |
| Lag | Returns a member prior to the specified member along the member's dimension. |
| LastChild | Returns the last child of a member. |
| LastSibling | Returns the last child of the parent of a member. |
| Lead | Returns a member further along the specified member's dimension. |
| LinkMember | Returns a hierarchized member. |
| Members | Returns the member whose name is specified by a string expression. |
| NextMember | Returns the next member in the level that contains a specified member. |
| OpeningPeriod | Returns the first sibling among the descendants of a member at a level. |
| ParallelPeriod | Returns a member from a prior period in the same relative position as a specified member. |
| Parent | Returns the parent of a member. |
| PrevMember | Returns the previous member in the level that contains a specified member. |
| StrToMember | Returns a member based on a string expression. |
| ValidMeasure | Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level. |
Numeric Functions
| Function | Description |
|---|---|
| Aggregate | Returns a calculated value using the appropriate aggregate function, based on the context of the query. |
| Avg | Returns the average value of a numeric expression evaluated over a set. |
| CalculationCurrentPass | Returns the current calculation pass of a cube for the current query context. |
| CalculationPassValue | Returns the value of an MDX expression evaluated over a specified calculation pass of the current cube. |
| CoalesceEmpty | Coalesces an empty cell value to a number or a string. |
| Correlation | Returns the correlation of two series evaluated over a set. |
| Count | Returns the number of dimensions in a cube, the number of levels in a dimension, the number of cells in a set, or the number of dimensions in a tuple. |
| Covariance | Returns the population covariance of two series evaluated over a set, using the biased population formula. |
| CovarianceN | Returns the sample covariance of two series evaluated over a set, using the unbiased population formula. |
| DistinctCount | Returns the count of tuples in a set, excluding duplicate tuples. |
| IIf | Returns one of two numeric or string values determined by a logical test. |
| LinRegIntercept | Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b. |
| LinRegPoint | Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b. |
| LinRegR2 | Calculates the linear regression of a set and returns R2 (the coefficient of determination). |
| LinRegSlope | Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b. |
| LinRegVariance | Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b. |
| LookupCube | Returns the value of an MDX expression evaluated over another specified cube in the same database. |
| Max | Returns the maximum value of a numeric expression evaluated over a set. |
| Median | Returns the median value of a numeric expression evaluated over a set. |
| Min | Returns the minimum value of a numeric expression evaluated over a set. |
| Ordinal | Returns the zero-based ordinal value associated with a level. |
| Predict | Evaluates the string expression within the data mining model specified within the current coordinates. |
| Rank | Returns the one-based rank of a tuple in a set. |
| RollupChildren | Scans the children of the member parameter and applies the string expression operator to their evaluated value. |
| Stddev | Alias for Stdev. |
| StddevP | Alias for StdevP. |
| Stdev | Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula. |
| StdevP | Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula. |
| StrToValue | Returns a value based on a string expression. |
| Sum | Returns the sum of a numeric expression evaluated over a set. |
| Value | Returns the value of a measure. |
| Var | Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula. |
| Variance | Alias for Var. |
| VarianceP | Alias for VarP. |
| VarP | Returns the population variance of a numeric expression evaluated over a set, using the biased population formula. |
Other Functions
| Function | Description |
|---|---|
| Call | Executes the string expression containing a user-defined function. |
Set Functions
| Function | Description |
|---|---|
| AddCalculatedMembers | Adds calculated members to a set. |
| AllMembers | Returns a set containing all members of a specified dimension or level, including calculated members. |
| Ancestors | Returns all the ancestors of a member at a specified distance. |
| Ascendants | Returns the set of the ascendants of the member, including the member itself. |
| Axis | Returns the set associated with the main axis. |
| BottomCount | Returns a specified number of items from the bottom of a set, optionally ordering the set first. |
| BottomPercent | Sorts a set and returns the bottom n elements whose cumulative total is at least a specified percentage. |
| BottomSum | Sorts a set and returns the bottom n elements whose cumulative total is at least a specified value. |
| Children | Returns the children of a member. |
| Crossjoin | Returns the cross product of two sets. |
| Descendants | Returns the set of descendants of a member at a specified level or at a specified distance from a member, optionally including or excluding descendants in other levels. |
| Distinct | Eliminates duplicate tuples from a set. |
| DrilldownLevel | Drills down the members of a set, at a specified level, to one level below.
Alternatively, drills down on a specified dimension in the set. |
| DrilldownLevelBottom | Drills down the bottom n members of a set, at a specified level, to one level below. |
| DrilldownLevelTop | Drills down the top n members of a set, at a specified level, to one level below. |
| DrilldownMember | Drills down the members in a set that are present in a second specified set. |
| DrilldownMemberBottom | Similar to DrilldownMember, except that it includes only the bottom n children. |
| DrilldownMemberTop | Similar to DrilldownMember, except that it includes only the top n children. |
| DrillupLevel | Drills up the members of a set that are below a specified level. |
| DrillupMember | Drills up the members in a set that are present in a second specified set. |
| Except | Finds the difference between two sets, optionally retaining duplicates. |
| Extract | Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin. |
| Filter | Returns the set resulting from filtering a set based on a search condition. |
| Generate | Applies a set to each member of another set and joins the resulting sets by union. |
| Head | Returns the first specified number of elements in a set. |
| Hierarchize | Orders the members of a set in a hierarchy. |
| Intersect | Returns the intersection of two input sets, optionally retaining duplicates. |
| LastPeriods | Returns a set of members prior to and including a specified member. |
| Members | Returns the set of all members in a dimension, hierarchy, or level. |
| Mtd | A shortcut function for the PeriodsToDate function that specifies the level to be Month. |
| NameToSet | Returns a set containing a single member based on a string expression containing a member name. |
| NonEmptyCrossjoin | Returns the cross product of two or more sets, excluding empty members. |
| Order | Arranges members of a set, optionally preserving or breaking the hierarchy. |
| PeriodsToDate | Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member. |
| Qtd | A shortcut function for the PeriodsToDate function that specifies the level to be Quarter. |
| Siblings | Returns the siblings of a member, including the member itself. |
| StripCalculatedMembers | Removes calculated members from a set. |
| StrToSet | Constructs a set from a string expression. |
| Subset | Returns a subset of elements from a set. |
| Tail | Returns a subset from the end of a set. |
| ToggleDrillState | Toggles the drill state of members. This function is a combination of DrillupMember and DrilldownMember. |
| TopCount | Returns a specified number of items from the top of a set, optionally ordering the set first. |
| TopPercent | Sorts a set and returns the top n elements whose cumulative total is at least a specified percentage. |
| TopSum | Sorts a set and returns the top n elements whose cumulative total is at least a specified value. |
| Union | Returns the union of two sets, optionally retaining duplicates. |
| VisualTotals | Dynamically totals child members specified in a set using a pattern for the total label in the result set. |
| Wtd | A shortcut function for the PeriodsToDate function that specifies the level to be Week. |
| Ytd | A shortcut function for the PeriodsToDate function that specifies the level to be Year. |
String Functions
| Function | Description |
|---|---|
| CalculationPassValue | Returns the value of an MDX expression evaluated over the specified calculation pass of a cube. |
| CoalesceEmpty | Coalesces an empty cell value to a string or number. |
| Generate | Returns a concatenated string created by evaluating a string expression over a set. |
| IIf | Returns one of two string or numeric values determined by a logical test. |
| LookupCube | Returns the value of an MDX expression evaluated over another specified cube in the same database. |
| MemberToStr | Constructs a string from a member. |
| Name | Returns the name of a dimension, hierarchy, level, or member. |
| Properties | Returns a string containing a member property value. |
| SetToStr | Constructs a string from a set. |
| TupleToStr | Constructs a string from a tuple. |
| UniqueName | Returns the unique name of a dimension, level, or member. |
| UserName | Returns the domain name and user name of the current connection. |
Tuple Functions
| Function | Description |
|---|---|
| Current | Returns the current tuple from a set during an iteration. |
| Item | Returns a tuple from a set. |
| StrToTuple | Constructs a tuple from a string. |