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.
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 |
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.
| Function | Description |
|---|---|
| SetToArray | Converts one or more sets to an array for use in a user-defined function. |
| 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. |
| Function | Description |
|---|---|
| Hierarchy | Returns the hierarchy of a level or member. |
| 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. |
| 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. |
| 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. |
| 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. |
| Function | Description |
|---|---|
| Call | Executes the string expression containing a user-defined function. |
| 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. |
| 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. |
| 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. |