Home • About • Contact
Login • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

MDX functions are grouped according to what the MDX functions returns! Here is a grouping and listing of the major MDX functions. Note some of these functions would traditionally be categorized as methods or properties.

• OLAP Objects
  • Dimension
  • Hierarchy
  • Level
  • Member
• OLAP Object Groups
  • Tuple
  • Set
• Miscellany
  • Numeric
  • String
  • Array
  • Logical
  • Miscellany

OLAP Objects

Dimension

• {Hierarchy|Level|Member}.Dimension.
• Dimensions(NumericExpression). Returns the dimension whose zero-based position within the cube is specified by NumericExpression. Note The Measures dimension is always represented by Dimensions(0).
• Dimensions(StringExpression). Returns the dimension whose name is specified by StringExpression.

Hierarchy

• {Member|Level}.Hierarchy.

Level

• Member.Level.
• Dimension.Levels(NumericExpression). "Returns the level whose zero-based position is specified by NumericExpression.
• Levels(StringExpression). Returns the level whose name is specified by StringExpression.

Member

• Often used with time dimensions.
  • ClosingPeriod([Level[, Member]]). "Returns the last sibling among the descendants of a member at a specified level."
  • Member.Lag(numSiblingsPriorToCurrentMember).
  • Member.Lead(numSiblingsAfterCurrentMember).
  • LinkMember(Member, Hierarchy). Returns the member equivalent to a specified member in a specified hierarchy.
    • LinkMember([Calendar].[1999].[Jan].[12], [Fiscal Year]). Returns [Fiscal Year].[1999].[Qtr1].[Jan].[12].
  • Member.NextMember. Equivalent to Member.Lead(1).
  • OpeningPeriod([Level[, Member]]). "Returns the first sibling among the descendants of a member at a specified level."
  • ParallelPeriod([AncestorLevel[, MMemberPeriodsAgo[, NthMemberOfMemberA]]]). Returns the Nth child of member A that was M member periods prior on a particular ancestor level of member A.
    • The default of the NthMemberOfMemberA is Time.CurrentMember (if AncestorLevel is not specified). If AncestorLevel is specified, then the NthMemberOfMemberA is the current member of the AncestorLevel.Dimension.
    • If MMemberPeriodsAgo is not specified, then it assumes one period ago.
    • If AncestorLevel is not specified, then it is NthMemberOfMemberA.ParentMember.
    • These examples assume that Time.CurrentMember is [1999April] and the levels are Year, Quarter, and Month.
      • ParallelPeriod(Year,2,[1999Q3]). Returns [1997Q3].
      • ParallelPeriod(Year,2). Returns [1997April].
      • ParallelPeriod(Year). Returns  [1998April].
      • ParallelPeriod(). Returns [1999January] because AncestorLevel is assumed to be the Quarter level.
  • Member.PrevMember. Equivalent to Member.Lag(1).
• "Family".
  • Ancestor(Member, AncesLevel) or Ancestor(Member, numLevelsAgo).
  • Cousin(NthMemberOfMemberA, UncleMemberB). Returns the Nth child of UncleMemberB, where UncleMemberB may be of a different dimension all together from member A's dimension.
  • Member.FirstChild.
  • Member.FirstSibling.
  • Member.LastChild.
  • Member.LastSibling.
  • Member.Parent.
• Miscellany
  • Dimension.CurrentMember.
  • Member.DataMember. "Returns the system-generated data member that is associated with a nonleaf member of a dimension. "
  • Dimension.DefaultMember or Hierarchy.DefaultMember.
  • StrToMember(StringExpression). "Returns a member from a string in MDX format containing a member, specified in StringExpression."
  • ValidMeasure(Tuple). "Returns a valid measure from a tuple in a virtual cube specified in Tuple."

OLAP Object Groups

Tuple

• Set.Current. Returns the current tuple from a set during an iteration such as during the Generate() function.
• Set.Item(StringExpression[, StringExpression...] | Index). "Returns a tuple from the set specified in Set. The tuple to be returned is specified either by name in StringExpression, or by the zero-based position of the tuple in the set in Index.
• StrToTuple(StringExpression). Constructs a tuple from a string.

Set

• "Family"
  • {Dimension|Level}.AllMembers. Returns all direct member of the object, including calculated members.
  • Ascendants(Member). Returns the set which is the member, its parent, and grandparent, etc. until the top level.
  • Member.Children.
  • Descendants(Member, [DescLevel[, DescFlags]]). or Descendants(Member, [MLevelsLater[, DescFlags]]). Returns the set of descendant members for a specified member at a specified level or distance down, including or excluding descendant members at other levels.
    • If DescLevel, MLevelsLater, and DescFlags are all not provided, then DescLevel is assumed to be Member.Level, and DescFlags is assumed to be SELF_BEFORE_AFTER.
    • If Level is the same level as Member, then only Member is returned.
    • The DescFlags available follow. The following examples assume Descendants(Time.y1998.q1,1,DescFlag), where the levels are year, quarter, month, day, and hour.
      • SELF. Default. Returns descendant members from Level only. Includes Member, if and only if Level specified is the level of Member. Returns approximately Level.Members.
        • Returns months of 1998q1.
      • AFTER. Returns descendant members from all levels subordinate to Level.
        • Returns days of each month in 1998q1 + hours of each of those days.
      • BEFORE. Returns descendant members from all levels between Member and Level, not including members from Level.
        • Returns 1998q1.
      • BEFORE_AND_AFTER. Returns descendant members from all levels subordinate to the level of Member except members from Level.
        • Returns 1998q1 + days of each month in 1998q1 + hours of each of those days.
      • SELF_AND_AFTER. Returns descendant members from Level and all levels subordinate to Level.
        • Returns months of 1998q1 + days of each month in 1998q1 + hours of each of those days.
      • SELF_AND_BEFORE. Returns descendant members from Level and all levels between Member and Level.
        • Returns 1998q1 + months of 1998q1.
      • SELF_BEFORE_AFTER. Returns descendant members from all levels subordinate to the level of Member.
        • Returns 1998q1 + months of 1998q1 + days of each month in 1998q1 + hours of each of those days.
      • LEAVES. Returns leaf descendant members between Member and Level.
        • Returns months of 1998q1.
  • {Dimension|Hierarchy|Level}.Members or Members.(strForDimOrHierOrLevel). Returns a large set of members for all levels when used on a dimension object.
  • Member.Siblings.
• Constructing
  • AddCalculatedMembers(Set). Returns the specified set plus any calculated members that are sibling to the specified set.
  • Crossjoin(Set1, Set2) or Set1 * Set2. Returns a set that combines sets from two different dimensions, i.e. a Cartesian product of the two sets. The following examples are equivalent and assume that the set ProductName.Members = {cup, bowl}.
    • Crossjoin(ProductName.Members,  {q1, q2, q3, q4}).
    • {ProductName.Members * {q1, q2, q3, q4}}.
    • {(cup,q1),(cup,q2),(cup,q3),(cup,q4),(bowl,q1),(bowl,q2),(bowl,q3),(bowl,q4)}.
  • Distinct(Set). Returns a set where the duplicates have been eliminated.
  • Except(Set1, Set2[, ALL]) or Set1 - Set2. Returns a set where none of the members of Set2 are found in Set1.  The function defaults to eliminating duplicates.  The alternate syntax defaults to including duplicates.
  • Extract(Set, Dimension[, DimensionN...]). Takes a set and removes any members who are part of the specified dimensions. Sort of the opposite of Crossjoin().
  • Filter(Set, SearchCondition).  Note that in SQL, the 2D result set is filtered with the WHERE clause, but in MDX, the MD result set can be filtered for each axis using multiple instances of the Filter() function.
    • Filter(ProductName.Members, [Unit Count] > 5).
  • Generate(Set1, Set2[, ALL]). Extraneous function that merely unions two sets. Optionally duplicates can be returned as well.
  • Intersect(Set1, Set2[, ALL]). Returns a set that whose members are in both input sets. The function defaults to eliminating duplicates.
  • NonEmptyCrossjoin(Set1, Set2[, Set3...][, MCrossjoinSet]). Crossjoins but also filters out empty tuples and tuples without fact table data (i.e. ignores calculated members). The MCrossjoinSet parameter specifies which of the provided sets will be used in the returned crossjoined set. The remaining sets are used to effectively filter the crossjoined set.
  • StripCalculatedMembers(Set). Returns the specified set minus any calculated members.
  • Subset(Set, numStartingTuple[, numEndingTuple]). Returns subset of specified set, where the tuples are numbered starting from zero.
  • Union(Set1, Set2[, ALL])  or {Set1, Set2} or Set1 + Set2. Returns a set that contains members from both input sets. The function defaults to eliminating duplicates. The alternates syntaxes default to including duplicates.
• Ordering
  • BottomCount(Set, Count[, NumericExpression]). Like a combination of Order() and Tail(). Breaks the hierarchy.
  • BottomPercent(Set, MinPercentage[, NumericExpression]). Returns a set whose cumulative total is at least the percentage specified. Breaks the hierarchy.
  • BottomSum(Set, MinValue[, NumericExpression]). Returns a set whose cumulative total is at least the value specified. Breaks the hierarchy.
  • Head(Set[, numTopMembersOfSetToReturn]).
  • Hierarchize(Set[, POST]). Returns a set hierarchized by level. This function defaults to sorting by the natural order.
  • Order(Set, {StringExpression|NumericExpression} [, ASC | DESC | BASC | BDESC]). Takes a set and orders it according to a numeric or string expression. The ordering will either break the hierarchy (BASC or BDESC), i.e. sort each member compared to all others , or preserve the hierarchy (ASC or DESC), i.e. arrange members by hierarchy, then sort siblings within each level. Note that in SQL, the 2D result set is sorted with the ORDER BY clause, but in MDX, the MD result set can be sorted for each axis using multiple instances of the MDX Order() function.
    • Order(ProductName.Members, [UnitCount], DESC).
  • Tail(Set[, numBottomMembersOfSetToReturn]).
  • TopCount(Set, Count[, NumericExpression]). Like a combination of Order() and Head(). Breaks the hierarchy.
  • TopPercent(Set, Percentage[, NumericExpression]). Returns a set whose cumulative total is at least the percentage specified. Breaks the hierarchy.
  • TopSum(Set, MinValue[, NumericExpression]). Returns a set whose cumulative total is at least the value specified. Breaks the hierarchy.
• Time
  • LastPeriods(NPeriods[, Member]). "Returns a set of members prior to and including a specified member or Time.CurrentMember if not specified.
  • PeriodsToDate([LevelOfScope[, Member]]). "Returns a set of periods (members) from a specified level (starting with the first period and ending with a specified member." If LevelOfScope is not provided, then Member is Time.CurrentMember and LevelOfScope is Time.CurrentMember.Parent.Level. If LevelOfScope is specified but not Member, then Member is Dimension.CurrentMember where Dimension is that of the specified LevelOfScope.
  • Mtd([Member]). "Returns a set of members from the Month level in a Time dimension starting with the first period and ending with a specified member. " Same as PeriodsToDate(Month[, Member]).
  • Qtd([Member]). "Returns a set of members from the Quarter level in a Time dimension starting with the first period and ending with a specified member. " Same as PeriodsToDate(Quarter[, Member]).
  • Ytd([Member]). "Returns a set of members from the Year level in a Time dimension starting with the first period and ending with a specified member. " Same as PeriodsToDate(Year[, Member]).
• Miscellany
  • Axis(NumericExpression). Uses the zero-based position of an axis, specified in NumericExpression, to return the set defined in the axis.
  • NameToSet(MemberName). "Returns a set containing a single member based on a string expression containing a member name."
  • StrToSet(StringExpression). "Constructs a set from a string expression."

Miscellany

Numeric

• Aggregate
  • Aggregate(Set[, NumericExpression]). "Returns a calculated value using the appropriate aggregate function, based on the aggregation type of the member."
  • Avg(Set[, NumericExpression]). "Returns the average value of a numeric expression evaluated over a set."
  • Correlation(Set, NumericExpressionForYAxis[, NumericExpressionForXAxis]).
  • Count(Set[, ExcludeEmpty | IncludeEmpty]). "Returns the number of cells in a set. This syntax allows empty cells to be excluded or included with the use of the ExcludeEmpty or IncludeEmpty flags, respectively."
  • Set.Count. "Returns the number of cells in a set, with empty cells included."
  • DistinctCount(Set). "Returns the number of distinct, non-empty tuples in a set."
  • Max(Set[, NumericExpression]). "Returns the maximum value of a numeric expression evaluated over a set."
  • Median(Set[, NumericExpression]). "Returns the median value of a numeric expression evaluated over a set."
  • Min(Set[, NumericExpression]). "Returns the minimum value of a numeric expression evaluated over a set."
  • Sum(Set[, NumericExpression]). "Returns the sum of a numeric expression evaluated over a set." This is probably the  most frequently used aggregate function."
• Aggregate Statistical
  • Covariance(Set, NumericExpression[, NumericExpression]). "Returns the population covariance of two series evaluated over a set, using the biased population formula."
  • CovarianceN(Set, NumericExpression[, NumericExpression]). "Returns the sample covariance of two series evaluated over a set, using the unbiased population formula."
  • Stdev(Set[, NumericExpression]) or Stdev(Set[, NumericExpression]). "Returns the sample standard deviation of a numeric expression evaluated over a set, using the unbiased population formula."
  • StdevP(Set[, NumericExpression]) or StdevP(Set[, NumericExpression]). "Returns the population standard deviation of a numeric expression evaluated over a set, using the biased population formula."
  • Var(Set[, NumericExpression]) or Variance(Set[, NumericExpression]). "Returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula."
  • VarP(Set[, NumericExpression]) or VarianceP(Set[, NumericExpression]). "Returns the population variance of a numeric expression evaluated over a set, using the biased population formula."
• Linear Regression
  • LinRegIntercept(Set, NumericExpression[, NumericExpression]). "Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b."
  • LinRegPoint(NumericExpression, Set, NumericExpression[, NumericExpression]). "Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b."
  • LinRegR2(Set, NumericExpression[, NumericExpression]). "Calculates the linear regression of a set and returns R² (the coefficient of determination) as per the least-squares method.
  • LinRegSlope(NumericExpression, Set, NumericExpression[, NumericExpression]). "Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b."
  • LinRegVariance(Set, NumericExpression[, NumericExpression]). "Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b."
• Miscellany
  • CalculationPassValue(NumericExpression, PassValue[, {ABSOLUTE|RELATIVE}]). Returns a numeric value, evaluating the MDX numeric expression specified in NumericExpression in the calculation pass specified in PassValue. By default the PassValue is the zero-based index of a calculation pass but the RELATIVE flag may be used instead.
  • CoalesceEmpty(NumericExpression[, NumericExpression]...). This function returns the first (from the left) nonempty value expression in the list of value expressions. Returns the empty cell value if all value expressions evaluate to the empty.
  • Dimensions.Count. "Returns the number of dimensions in a cube, including the [Measures] dimension. "
  • {Dimension|Hierarchy}.Levels.Count. "Returns the number of levels in a dimension or hierarchy, including the [All] level if applicable."
  • Tuple.Count. "Returns the number of dimensions in a tuple."
  • IIf(LogicalExpression, numExpressionIfTrue, numExpressionIfFalse). LogicalExpression is anything that evaluates to non-zero. The available comparison operators are =, <>, >, <, >=, and <=. The following can be used to combine conditional expressions: OR, AND, XOR, and NOT.
  • Tuple.Item(Index). "Returns a member from the tuple specified in Tuple. The member to be returned is specified by the zero-based position of the member in the tuple in Index."
  • LookupCube(CubeString, NumericExpression). "The LookupCube function returns a numeric value, evaluating the numeric expression specified in NumericExpression in another cube within the context of the cube specified in CubeString.
  • Level.Ordinal. Returns the zero-based ordinal value of the level.
    • Time.Month.Ordinal. Returns 2 if Time has the level of [All Time], Year, Month, and Day.
  • Rank(Tuple, Set[, CalcExpression]). "Returns the one-based rank of a specified tuple in a specified set." If CalcExpression is not provided, then this function returns the ordinal position of the tuple in the set.
  • StrToValue(strExpression).
  • Member.Value. Returns the value of a measure specified in Member. This is the default property of a measure.
  • CalculationCurrentPass(). Returns the zero-based index of the calculation pass of the current query context flag.

String

• CalculationPassValue(StringExpression, PassValue[, {ABSOLUTE|RELATIVE}]). Returns a numeric value, evaluating the MDX numeric expression specified in StringExpression in the calculation pass specified in PassValue. By default the PassValue is the zero-based index of a calculation pass but the RELATIVE flag may be used instead.
• CoalesceEmpty(StringExpression[, StringExpression]...). This function returns the first (from the left) nonempty value expression in the list of value expressions. Returns the empty cell value if all value expressions evaluate to the empty.
• Generate(Set, StringExpression[, Delimiter]). Goes thru each member of a set and evaluates a string expression. Optionally specifies how to delimit each item in the resulting string.
  • Generate( {Time.[1998], Time.[1999]}, Time.CurrentMember.Name, " and ") . Returns "1998 and 1999".
• IIf(LogicalExpression, strExpressionIfTrue, strExpressionIfFalse).
• LookupCube(CubeString, StringExpression). "The LookupCube function returns a string value, evaluating the string expression specified in StringExpression in another cube within the context of the cube specified in CubeString.
• MemberToStr(Member). "Constructs a string from a member. "
• {Dimension|Hierarcy|Level|Member}.Name.
• Member.Properties(strPropertyName). "Returns a string containing a member property value."
• SetToStr(Set). "Constructs a string from a set."
• TupleToStr(Tuple). "Constructs a string from a tuple."
• Dimension.UniqueName or Hierarcy.UniqueName or Level.UniqueName or Member.UniqueName. Returns a the fully qualified name.
• UserName. Returns a string with the following syntax for the current connection: DomainName/UserName.

Array

• SetToArray(Set[, SetN...][, NumericExpression]). "Converts one or more sets to an array for use in a user-defined function."

Logical

• Object1 Is Object2. Returns True if two compared objects are equivalent, False otherwise.
• IsAncestor(Member1, Member2). Returns True if the 1st member is an ancestor of the 2nd member.
• IsEmpty(ValueExpression). Returns True if ValueExpression is Null. Good for avoiding errors such as division by zero.
• IsGeneration(Member, numGernerationRelativeToLeaf).
• IsLeaf(Member). Determines whether a specified member is a leaf member.
• IsSibling(Member1, Member2).

Miscellany

• Call UDFName. "Executes a void-returning user-defined function."

---

These are unsorted as of yet.

    Numeric
Predict Evaluates the string expression within the data mining model specified within the current coordinates.
RollupChildren Scans the children of the member parameter and applies the string expression operator to their evaluated value.

    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.
ToggleDrillState Toggles the drill state of members. This function is a combination of DrillupMember and DrilldownMember.
VisualTotals Dynamically totals child members specified in a set using a pattern for the total label in the result set.

Page Modified: (Hand noted: 2007-10-12 02:57:12Z) (Auto noted: 2007-11-17 06:46:02Z)