Appendix A: MDX Function and Operator Reference

This appendix is a detailed reference to the functions and operators of standard MDX, and extensions as implemented in Microsoft's Analysis Services 2000 and 2005. The main body of the appendix lists the functions and operators in alphabetical order, along with any arguments and the result's data type. We have included a pair of indexes at the beginning to help you navigate.

Index to Functions

We have found that two different types of indexes are useful for looking up a function or operator: by name and by return type.

Alphabetical Index

AddCalculatedMembers (): 703

Aggregate (): 704

.AllMembers (): 705

Ancestor (): 706

Ancestors (): 706

And: 707

Ascendants (): 707

Avg (): 708

Axis (): 708

BottomCount (): 710

BottomPercent (): 710

BottomSum (): 710

Calculate: 710

CalculationCurrentPass (): 710

CalculationPassValue (): 711

Call: 711

CASE: 712

.Children: 713

ClosingPeriod (): 713

CoalesceEmpty (): 713

Correlation (): 714

Count (): 715

.Count: 715

Cousin (): 716

Covariance (): 716

CovarianceN (): 716

Crossjoin (): 718

.Current: 718

.CurrentMember: 718

.CurrentOrdinal: 719

CustomData: 719

.DataMember: 719

.DefaultMember: 720

Descendants (): 720

.Dimension: 724

Dimensions (): 724

Distinct (): 726

DistinctCount (): 726

DrillDownLevel (): 726

DrillDownLevelBottom (): 727

DrillDownLevelTop (): 728

DrillDownMember (): 729

DrillDownMemberBottom (): 731

DrillDownMemberTop (): 731

DrillUpLevel (): 732

DrillUpMember (): 732

Error (): 734

Except (): 735

Existing: 735

Exists (): 736

Extract (): 736

Filter (): 736

.FirstChild: 737

.FirstSibling: 737

Generate (): 738

Head (): 739

Hierarchize (): 739

.Hierarchy: 739

.Ignore: 740

Iif (): 740

Intersect (): 741

Is: 742

IsAncestor (): 743

IsEmpty (): 743

IsGeneration (): 743

IsLeaf (): 744

IsSibling (): 745

.Item (): 745

KPICurrentTimeMember (): 746

KPIGoal (): 747

KPIStatus (): 747

KPITrend (): 747

KPIValue (): 747

KPIWeight (): 747

.Lag (): 748

.LastChild: 748

LastPeriods (): 748

.LastSibling: 750

.Lead (): 750

Leaves (): 750

.Level: 751

Levels (): 751

LinkMember (): 751

LinRegIntercept (): 752

LinRegPoint (): 752

LinRegR2 (): 753

LinRegSlope (): 753

LinRegVariance (): 754

LookupCube (): 754

Max (): 755

MeasureGroupMeasures (): 755

Median (): 755

.Members: 756

Members (): 756

MemberToStr (): 756

Min (): 757

MTD (): 758

.Name: 758

NameToSet (): 758

.NextMember: 759

NonEmpty (): 759

NonEmptyCrossjoin (): 759

NOT: 760

OpeningPeriod: 760

Or: 761

Order (): 761

.Ordinal: 765

ParallelPeriod (): 766

.Parent: 766

PeriodsToDate (): 768

Predict (): 769

.PrevMember: 769

.Properties (): 769

.PropertyName: 770

QTD (): 771

Rank (): 771

RollupChildren (): 772

Root (): 773

Scope (): 774

SetToArray (): 774

SetToStr (): 774

.Siblings: 776

StdDev (): 776

StdDevP (): 776

StDev (): 776

StDevP (): 776

StripCalculatedMembers (): 777

StrToMember (): 777

StrToSet (): 777

StrToTuple (): 778

StrToValue (): 779

Subset (): 779

Sum (): 779

Tail (): 780

This (): 780

ToggleDrillState (): 780

TopCount (): 780

TopPercent (): 781

TopSum (): 782

TupleToStr: 783

Union (): 783

.UniqueName: 784

.UnknownMember: 785

Unorder (): 785

UserName: 786

ValidMeasure (): 786

.Value: 787

Var (): 787

Variance (): 787

VarianceP (): 787

VarP (): 787

VisualTotals (): 788

WTD (): 794

XOR: 794

YTD (): 794

KPITrend (): 747

Return Type Index

The functions are all listed here by return type:

Number

Aggregate (): 704

Avg (): 708

CalculationCurrentPass (): 710

CalculationPassValue (): 711

CASE: 712

CoalesceEmpty (): 713

Correlation (): 714

Count (): 715

.Count: 715

Covariance (): 716

CovarianceN (): 716

.CurrentOrdinal: 719

DistinctCount (): 726

Iif (): 740

KPIGoal (): 747

KPIStatus (): 747

KPIValue (): 747

KPIWeight (): 747

LinRegIntercept (): 752

LinRegPoint (): 752

LinRegR2 (): 753

LinRegSlope (): 753

LinRegVariance (): 754

LookupCube (): 754

Max (): 755

Median (): 755

Min (): 757

.Ordinal: 765

Predict (): 769

.Properties (): 769

.PropertyName: 770

Rank: 771

RollupChildren (): 772

StdDev (): 776

StdDevP (): 776

StDev (): 776

StDevP (): 776

StrToValue (): 779

Sum (): 779

.Value: 787

Var (): 787

Variance (): 787

VarianceP (): 787

VarP (): 787

String

CalculationPassValue (): 711

CASE: 712

CoalesceEmpty (): 713

CustomData: 719

Generate (): 738

Iif (): 740

LookupCube (): 754

MemberToStr (): 756

.Name: 758

.Properties (): 769

.PropertyName: 770

SetToStr (): 774

TupleToStr (): 783

.UniqueName: 784

UserName: 786

Logical

And: 707

Is: 742

IsAncestor (): 743

IsEmpty (): 743

IsGeneration (): 744

Member

Ancestor (): 706

CASE: 712

ClosingPeriod (): 713

Cousin (): 716

.CurrentMember: 718

.DataMember: 719

.DefaultMember: 720

.FirstChild: 737

.FirstSibling: 737

.Ignore: 740

.Item (): 745

KPICurrentTimeMember (): 746

.Lag (): 748

AddCalculatedMembers (): 703

.AllMembers: 705

Ancestors (): 706

Ascendants (): 707

Axis (): 708

BottomCount (): 710

BottomPercent (): 710

BottomSum (): 710

CASE: 712

.LastChild: 748

.LastSibling: 750

.Lead (): 750

LinkMember (): 751

Members (): 756

.NextMember (): 759

OpeningPeriod (): 760

ParallelPeriod (): 766

.Parent: 766

.PrevMember: 769

StrToMember (): 777

.UnknownMember: 785

ValidMeasure (): 786

Tuple

CASE: 712

.Current: 718

.Item (): 745

Root (): 773

StrToTuple (): 778

Set

AddCalculatedMembers (): 703

.AllMembers: 705

Ancestors (): 706

Ascendants (): 707

Axis (): 708

BottomCount (): 710

BottomPercent (): 710

BottomSum (): 710

CASE: 712

.Children: 713

Crossjoin (): 718

Descendants (): 720

Distinct (): 726

DrilldownLevel (): 726

DrilldownLevelBottom (): 727

DrilldownLevelTop (): 728

DrilldownMember (): 729

DrilldownMemberBottom (): 731

DrillDownMemberTop (): 731

DrillUpLevel (): 732

DrillUpMember (): 732

Except (): 735

Existing: 735

Exists (): 736

Extract (): 736

Filter (): 736

Generate (): 738

Head (): 739

Hierarchize (): 739

Iif (): 740

Intersect (): 741

LastPeriods (): 748

Leaves (): 750

MeasureGroupMeasures (): 755

.Members: 756

MTD (): 758

NameToSet (): 758

NonEmpty (): 759

NonEmptyCrossjoin (): 759

Order (): 761

PeriodsToDate (): 768

QTD (): 771

.Siblings: 776

StripCalculatedMembers (): 777

StrToSet (): 777

Subset (): 779

Tail (): 780

ToggleDrillState (): 780

TopCount (): 780

TopPercent (): 781

TopSum (): 782

Union (): 783

VisualTotals (): 788

Wtd (): 794

Ytd (): 794

Dimension, Hierarchy, and Level

The following functions return references to dimensions, hierarchies, or levels: