mdx functions.docx

8/10/2019 MDX Functions.docx

1/158

http://technet.microsoft.com/en-us/library/ms144932.aspx

http://www.mdxpert.com/Functions/FunctionList.aspx

MDX KPI Functions

KPICurrentTimeMember (MDX)Returns the current time member of the specified Key Performance Indicator (KPI).

SyntaxKPICurrentTimeMember(KPI_Name)

KPI_NameA valid string expression that specifies the name of the KPI.

A KPI can have a different time member from the default member of the time dimension.

KPIValueReturns the member that calculates the value of the specified Key Performance Indicator

(KPI).

SyntaxKPIValue(KPI_Name)

The query below shows the KPIValue for the Revenue KPI. If you look at the value expression forthe Revenue KPI, you will see that it maps to the Sales Amount measure.

SELECT

{KPIVALUE("Revenue"), [Measures].[Sales Amount] } ON COLUMNS,{[Date].[Fiscal].[Fiscal Year].Members} ON ROWS

FROM [Adventure Works]

KPIGoalReturns the member that calculates the value for the goal portion of the specified Key

Performance Indicator (KPI).

SyntaxKPIGoal(KPI_Name)

The query below shows that the KPIGoal for the Revenue KPI for all fiscal years.

SELECT

{KPIGOAL("Revenue")} ON COLUMNS,{[Date].[Fiscal].[Fiscal Year].Members} ON ROWS


KPIWeightReturns the weight of the specified Key Performance Indicator (KPI).

SyntaxKPIWeight(KPI_Name)

The value returned is the contribution of the KPI to the parent.

KPIStatus
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


2/158

Returns a normalized value that represents the status portion of the specified KeyPerformance Indicator (KPI).

SyntaxKPIStatus(KPI_Name)

The query below shows the KPIStatus for the Revenue KPI for all fiscal years. Included on the 0 axis

is the KPIValue and KPIGoal

SELECT

{KPIVALUE("Revenue"), KPIGOAL("Revenue"), KPISTATUS("Revenue")} ONCOLUMNS,

{[Date].[Fiscal].[Fiscal Year].Members} ON ROWSFROM [Adventure Works]

You will notice when the Sales Amount equals the Revenue goal, the value of the KPIStatus is 0.When the Sales Amount is greater than the Revenue Goal, the status is set to 1...and conversely,when the Sales Amount is less than the Revenue Goal, then a negative 1 is shown. The status valueusually ranges from -1 to 1 with -1 meaning not good, 0 meaning ok, and 1 meaning good.

KPITrendReturns the normalized value that represents the trend portion of the specified KeyPerformance Indicator (KPI).

SyntaxKPITrend(KPI_Name)

The query below shows the KPITrend for the Revenue KPI for all fiscal years. Included on the 0 axisis the KPIValue, KPIGoal, and KPITrend.

SELECT{KPIVALUE("Revenue"), KPIGOAL("Revenue"), KPISTATUS("Revenue"),

KPITREND("Revenue")} ON COLUMNS,{[Date].[Fiscal].[Fiscal Year].Members} ON ROWS


You will notice when the Sales Amount equals the Revenue goal, the value of the KPITrend is 0.When the Sales Amount is greater than the Revenue Goal, the trend value is set to 1...andconversely, when the Sales Amount is less than the Revenue Goal, then a negative 1 is shown. Thetrend value usually ranges from -1 to 1 with -1 meaning down trend, 0 meaning no trend, and 1 uptrend.

MDX Metadata Functions

AxisReturns the set of tuples on a specified axis.

Syntax

Axis(Axis_Number)

The query below displays the set of tuples that is on axis 0. The SetToStr function is used toturn the set of tuples into a string so it can be returned as a string value.

WITH

MEMBER [Measures].[SetOfTuplesOnTheColumnsAxis] ASSetToStr(AXIS(0))
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


3/158

SELECT{[Sales Reason].[Sales Reason].[Magazine Advertisement]} ON COLUMNS,

{[Measures].[SetOfTuplesOnTheColumnsAxis]} ON ROWS


The query below returns the set of tuples that are on Axis 0. In each cell that is returned onthe column axis (in this case, Calendar Year), a set is returned as a string (via the SetToStrfunction).

WITH

MEMBER [Measures].[SetOfTuplesOnTheColumnsAxis] ASSetToStr(AXIS(0))

SELECT

{[Date].[Calendar].[Calendar Year].Members} ON COLUMNS,{[Measures].[SetOfTuplesOnTheColumnsAxis]} ON ROWS


HierarchyThe Hierarchy function returns a hierarchy for a specified member or level.

Syntax

Member expression syntaxMember_Expression.Hierarchy

Level expression syntaxLevel_Expression.Hierarchy

The query below returns the name of the hierarchy for the Q1 FY 2003 member.

WITHMEMBER [Hier] AS

[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2003].Hierarchy.NameSELECT

{[Hier]} ON COLUMNSFROM [Adventure Works]

The following query returns the default member (All Periods) for the Fiscal hierarchy for

the Q1 FY 2003 member.

SELECT{[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2003].Hierarchy} ON COLUMNS


Ordinal

The Ordinal function returns a zero-based value for the specified level.

SyntaxLevel_Expression.Ordinal

The following query uses the Ordinal function to determine the level for the current member

of the Fiscal hierarchy.
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


4/158

WITH

MEMBER [Measures].[DayOrMonthLevel] AS

IIF([Date].[Fiscal].CurrentMember.Level.Ordinal = 4, "Month Level","Day Level")SELECT

{[Measures].[DayOrMonthLevel]} ON COLUMNS,

DESCENDANTS([Date].[Fiscal], 4, SELF_AND_AFTER) ON ROWSFROM [Adventure Works]

The query below shows the ordinal value for levels under the Fiscal hierarchy in the Date

dimension.

WITH

MEMBER [Measures].[FiscalYearOrd] AS

[Date].[Fiscal].[Fiscal Year].OrdinalMEMBER [Measures].[FiscalSemesterOrd] AS

[Date].[Fiscal].[Fiscal Semester].OrdinalMEMBER [Measures].[FiscalQuarterOrd] AS

[Date].[Fiscal].[Fiscal Quarter].OrdinalMEMBER [Measures].[MonthOrd] AS[Date].[Fiscal].[Month].Ordinal

MEMBER [Measures].[DateOrd] AS[Date].[Fiscal].[Date].Ordinal

SELECT{[Measures].[FiscalYearOrd], [Measures].[FiscalSemesterOrd],

[Measures].[FiscalQuarterOrd], [Measures].[MonthOrd],

[Measures].[DateOrd]} ON COLUMNS


Count (Dimension)

The Count function, when applied to dimensions, returns the number of hierarchies in acube.

Syntax

Dimensions.Count

The query below shows there are a total of 208 hierarchies in the Adventure Works cube.

WITHMEMBER [Measures].[NumberOfDimensions] AS

Dimensions.CountSELECT [Measures].[NumberOfDimensions] ON COLUMNSFROM [Adventure Works]

The query below shows there are a total of 140 hierarchies in the Mined Customers cube.

WITHMEMBER [Measures].[NumberOfDimensions] AS

Dimensions.CountSELECT

[Measures].[NumberOfDimensions] ON COLUMNSFROM [Mined Customers]
http://void%280%29/http://void%280%29/http://void%280%29/


5/158

Count (Hierarchy Levels)The Count function, when applied to hierarchies, returns the number of levels within the

specified hierarchy.

SyntaxHierarchy_Expression.Levels.Count

The query below shows there are a total of six levels in the Fiscal Date hierarchy of the Datedimension.

WITHMEMBER [Measures].[NumberOfLevels] AS[Date].[Fiscal].Levels.Count

SELECT[Measures].[NumberOfLevels] ON COLUMNS


The six levels include: All Level Fiscal Year Fiscal Semester Fiscal Quarter Month Date

The query below shows there is one level in the Measures dimension.

WITH

MEMBER [Measures].[NumberOfLevels] ASMeasures.Levels.Count

SELECT

[Measures].[NumberOfLevels] ON COLUMNSFROM [Adventure Works]

Count (Tuple)The Count function, when applied to tuples, returns the number of dimensions within the

specified tuple.

Syntax

Tuple_Expression.Count

The query below shows there are a total of four dimensions in the supplied tuple.

WITHMEMBER [Measures].[NumberOfDims] AS

([Date].[Fiscal].[Date].[July 19, 2003], [Reseller].[Reseller].[All

Cycle Shop], Measures, [Product].[Product].[Fork End]).Count

SELECT[Measures].[NumberOfDims] ON COLUMNS


Level

The Level function returns a level for the specified member.Syntax

Member_Expression.Level

The query below returns the Quarter level for the Q1 FY 2003 member.

SELECT

{} ON COLUMNS,{[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2003].Level} ON ROWS


6/158


The following query returns the Name of the Quarter level for the Q1 FY 2003 member.

WITH

MEMBER [LevelName] AS

[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2003].Level.NameSELECT

{[LevelName]} ON COLUMNS


The following query returns the UniqueName of the Quarter level for the Q1 FY 2003

member.

WITHMEMBER [LevelName] AS[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2003].Level.UniqueName

SELECT

{[LevelName]} ON COLUMNS


LevelsReturns the level whose position in a dimension or hierarchy is specified by a numeric

expression or whose name is specified by a string expression.Syntax

Numeric expression syntax

Hierarchy_Expression.Levels( Level_Number )

String expression syntaxHierarchy_Expression.Levels( Level_Name )

The query below returns the all level (by number) for the fiscal hierarchy of the Date

dimension.

SELECT{} ON COLUMNS,

{[Date].[Fiscal].Levels(0)} ON ROWSFROM [Adventure Works]

The query below returns the Month level (by number) for the fiscal hierarchy of the Datedimension.

SELECT

{} ON COLUMNS,{[Date].[Fiscal].Levels(4)} ON ROWS


The query below returns the Quarter level (by name) for the fiscal hierarchy of the Datedimension.

SELECT

{} ON COLUMNS,


7/158

{[Date].[Fiscal].Levels("Fiscal Quarter")} ON ROWSFROM [Adventure Works]

NameThe Name function returns the name of Dimensions, Hierarchies, Levels, and MembersSyntax

Dimension expression syntaxDimension_Expression.Name

Hierarchy expression syntax

Hierarchy_Expression.Name

Level_expression syntax

Level_Expression.Name

Member expression syntaxMember_Expression.Name

The query below shows the difference between the name and the UniqueName for a

member.WITH

MEMBER [MemName] AS[Date].Fiscal.[December 2002].Name

MEMBER [MemUniqueName] AS[Date].Fiscal.[December 2002].UniqueName

SELECT{[MemName], [MemUniqueName]} ON COLUMNS


The following query returns the name for a dimension, hierarchy, level, and member.

WITHMEMBER [DimName] AS

[Date].Name

MEMBER [HierName] AS

[Date].Fiscal.NameMEMBER [LevName] AS[Date].Fiscal.Month.Name

MEMBER [MemName] AS[Date].Fiscal.[December 2002].Name

SELECT{[DimName], [HierName], [LevName], [MemName]} ON COLUMNS


UniqueNameThe UniqueName function returns the unique name of Dimensions, Hierarchies, Levels, and

Members.SyntaxDimension expression syntax

Dimension_Expression.UniqueName


Hierarchy_Expression.UniqueName


8/158

Level expression syntax

Level_Expression.UniqueName

Member expression syntaxMember_Expression.UniqueName

The query below shows the difference between the name and the UniqueName for amember.

WITHMEMBER [MemName] AS

[Date].Fiscal.[December 2002].NameMEMBER [MemUniqueName] AS

[Date].Fiscal.[December 2002].UniqueNameSELECT

{[MemName], [MemUniqueName]} ON COLUMNS


The following query returns the unique name for a dimension, hierarchy, level, and member.

WITH

MEMBER [DimUniqueName] AS[Date].UniqueNameMEMBER [HierUniqueName] AS

[Date].Fiscal.UniqueNameMEMBER [LevUniqueName] AS

[Date].Fiscal.Month.UniqueNameMEMBER [MemUniqueName] AS

[Date].Fiscal.[December 2002].UniqueName

SELECT{[DimUniqueName], [HierUniqueName], [LevUniqueName],

[MemUniqueName]} ON COLUMNSFROM [Adventure Works]

MDX NAVIGATION FUNCTIONSANCESTORThe Ancestor function returns a member at the specified level for for the supplied member.

SyntaxLevel syntax

Ancestor(Member_Expression, Level_Expression)

Numeric syntax

Ancestor(Member_Expression, Distance)

The query below returns the Clothing member which is an ancestor at the Product Categorylevel for the member Half-Finger Gloves, S.


{ANCESTOR([Product].[Product Categories].[Product].[Half-Finger

Gloves, S], [Product].[Product Categories].Category)} ON ROWSFROM [Channel Sales]


9/158

This query uses the distance (numeric expression) to return the parent (one level up) of

Half-Finger Gloves, S.


{ANCESTOR([Product].[Product Categories].[Product].[Half-FingerGloves, S], 1)} ON ROWSFROM [Channel Sales]

The query below uses the distance (numeric expression) to return the set two levels up from

the Product member Half-Finger Gloves, S..

SELECT{} ON COLUMNS,{ANCESTOR([Product].[Product Categories].[Product].[Half-Finger

Gloves, S],2)} ON ROWSFROM [Channel Sales]

This query below returns members at the Category, SubCategory and Product levels for the

supplied member.

SELECT{} ON COLUMNS,{ANCESTOR([Product].[Product Categories].[Product].[Half-Finger

Gloves, S],2), ANCESTOR([Product].[Product Categories].[Product].[Half-Finger Gloves, S],1), ANCESTOR([Product].[Product

Categories].[Product].[Half-Finger Gloves, S],0)} ON ROWSFROM [Channel Sales]

AncestorsThe Ancestors function returns a set of all ancestors at the specified level for the suppliedmember.

Syntax

Level syntaxAncestors(Member_Expression, Level_Expression)

Numeric syntaxAncestors(Member_Expression, Distance)

This sample uses the distance (numeric expression) to return the parent (one level up) of

November 2002.

SELECT{} ON COLUMNS,{ANCESTORS([Date].[Fiscal].[Month].[November 2002],1)} ON ROWS

FROM [Channel Sales]

This sample uses the distance (numeric expression) to return the set three levels up of


10/158

November 2002which in this case is Fiscal Year.


{ANCESTORS([Date].[Fiscal].[Month].[November 2002],3)} ON ROWSFROM [Channel Sales]

This query below returns a set that includes the November 2002 member itself, along with

three levels above it.

SELECT{} ON COLUMNS,{ANCESTORS([Date].[Fiscal].[Month].[November 2002],0),

ANCESTORS([Date].[Fiscal].[Month].[November 2002],1),

ANCESTORS([Date].[Fiscal].[Month].[November 2002],2),

ANCESTORS([Date].[Fiscal].[Month].[November 2002],3)} ON ROWSFROM [Channel Sales]

The query below returns the Fiscal Year ancestor for the member November 2002.SELECT

{} ON COLUMNS,{ANCESTORS([Date].[Fiscal].[Month].[November

2002],[Date].[Fiscal].[Fiscal Year])} ON ROWSFROM [Channel Sales]

ASCENDANTSThe Ascendants function returns a set of ascendants for a specified member along with the

member itself.

Syntax

Ascendants(Member_Expression)

This query below returns a set of all members above the Touring Tire member of theProduct Categories hierarchy in the Product dimension.


{ASCENDANTS([Product].[Product Categories].[Product].[Touring

Tire])} ON ROWSFROM [Adventure Works]

The query below returns the members above the Montana member of the Geographyhierarchy in the Geography dimension.

SELECT{} ON COLUMNS,ASCENDANTS([Geography].[Geography].[State-Province].[Montana])} ON

ROWSFROM [Adventure Works]

CHILDREN


11/158

The Children function returns the children for the supplied member.Syntax

Member_Expression.Children

The query below returns all children for the United States member of the Geography

dimension.


{[Geography].[Geography].[Country].&[United States].Children} ONROWSFROM [Adventure Works]

The query below returns all children for the Marketing member of the Sales Reasons

hierarchy in the Sales Reason dimension.


{[Sales Reason].[Sales Reasons].[Reason Type].[Marketing].Children}ON ROWS


COUSINThe Cousin function returns a child member that has the same relative position to the parent as thesupplied member.SyntaxCousin( Member_Expression , Ancestor_Member_Expression )

The query below returns the cousin of July 2003 for the Calendar Year 2004, which is July

2004.

SELECTCOUSIN([Date].[Calendar].[Month].[July 2003], [Date].[Calendar].[CY

2004]) ON COLUMNSFROM [Adventure Works]

This query returns the cousin of March 15, 2002 for the Fiscal Year 2003, which is March

15, 2003

SELECTCOUSIN([Date].[Fiscal].[Date].[March 15, 2002], [Date].[Fiscal].[FY

2003]) ON COLUMNS


CURRENT

The Current function returns the current tuple in a set during iteration.Syntax

Set_Expression.Current

The query below returns the unique name for Product Line members using the Current function.

WITHMEMBER [Measures].[CurrentTuple] AS

GENERATE([Product].[Product Line].CurrentMember AS[ANamedSet], [ANamedSet].Current.Item(0).UniqueName)

SELECT


12/158

{[Measures].[CurrentTuple]} ON COLUMNS,{[Product].[Product Line].Members} ON ROWS


The above query uses the Generate function for iteration since the Current function will only workduring iteration.

CURRENTMEMBER

The CurrentMember functon returns the current member during iteration along a specified

hierarchy.SyntaxHierarchy_Expression.CurrentMember

The query below returns the ID for the currentmember for Product Line members.

WITHMEMBER [Measures].[CurrMemID] AS

[Product].[Product Line].CurrentMember.Properties("ID")

SELECT{[Measures].[CurrMemID]} ON COLUMNS,

{[Product].[Product Line].Members} ON ROWSFROM[Adventure Works]

The following query returns the unique name for the currentmember for Product Line members.

WITH

MEMBER [Measures].[CurrMemID] AS[Product].[Product Line].CurrentMember.UniqueName

SELECT

{[Measures].[CurrMemID]} ON COLUMNS,{[Product].[Product Line].Members} ON ROWS

FROM

[Adventure Works]

CurrentOrdinal

The CurrentOrdinal function returns the current iteration number within a set duringiteration.

Syntax

Set_Expression.CurrentOrdinal

The query below uses the Generate function to iterate over the six members of the Product Linehierarchy. The CurrOrd calculated member returns a string that shows the six CurrentOrdinal numbersseparated by the pipe character.

WITH

MEMBER [Measures].[CurrOrd] ASGENERATE([Product].[Product Line].Members AS[ANamedSet], [ANamedSet].CurrentOrdinal, " | ")

SELECT

{[Measures].[CurrOrd]} ON COLUMNSFROM [Adventure Works]

DataMemberThe DataMember function returns the data member that is associated with a nonleaf

member of a dimension.


13/158

SyntaxMember_Expression.DataMember

The query below returns the data member (using the default measure) for the Q2 FY 2003member in the Fiscal Quarter level.SELECT

{[Date].[Fiscal].[Fiscal Quarter].[Q2 FY 2003].DataMember} ON COLUMNSFROM [Adventure Works]

The following query returns the data member (for Sales Amount) for the Australia memberin the Country level of the Geography hierarchy in the Geography dimension.

SELECT{[Geography].[Geography].[Country].[Australia].DataMember} ON COLUMNS


WHERE ([Measures].[Sales Amount])

DefaultMemberThe DefaultMember function returns the default member of a hierarchy.

Syntax

Hierarchy_Expression.DefaultMember

The query below shows the default member value (Reseller Sales Amount) of the Measures dimension.SELECT

{Measures.DefaultMember} ON COLUMNSFROM [Adventure Works]

The following query shows the default member for Measures dimension and the Promotionshierarchy. Notice the Measures dimension was not explicitly defined on an axis, yet the defaultmeasure value is returned. This is due to the context of the query. All queries are run int he contextof the default members for all hierarchies.

SELECT{[Promotion].[Promotions].DefaultMember} ON COLUMNS

FROM[Adventure Works]

The query below shows the UniqueName of the default members for several hierarchies.

WITH

MEMBER [Measures].[EmpDeparDM] AS[Employee].[Employee Department].DefaultMember.UniqueName

MEMBER [Measures].[ProdCatDM] AS[Product].[Product Categories].DefaultMember.UniqueNameMEMBER [Measures].[DelDateFiscalDM] AS

[Delivery Date].[Fiscal].DefaultMember.UniqueNameMEMBER [Measures].[OrgCurCodeDM] AS[Organization].[Currency Code].DefaultMember.UniqueName

SELECT{[Measures].[EmpDeparDM],

[Measures].[ProdCatDM],[Measures].[DelDateFiscalDM],[Measures].[OrgCurCodeDM]} ON COLUMNS


14/158


FirstChild

The FirstChild function returns the first child of a supplied member.Syntax

Member_Expression.FirstChild

The query below returns the first child for the member Washington in the CustomerGeography hierarchy.

SELECT

{} ON COLUMNS,

{[Customer].[Customer Geography].[State-Province].[Washington].FirstChild} ON ROWS


The following query returns the first child for the April 2003 member in the Fiscal hierarchyof the Date dimension.

SELECT

{} ON COLUMNS,

{[Date].[Fiscal].[Month].[April 2003].FirstChild} ON ROWS


FirstSibling

The FirstSibling function returns the first sibling for the parent of a supplied member.Syntax

Member_Expression.FirstSibling

The query below returns the first sibling for the parent of the Road Bikes member from theProduct Categories hierarchy in the Product dimension.SELECT

{} ON COLUMNS,{[Product].[Product Categories].[Subcategory].[Road

Bikes].FirstSibling} ON ROWSFROM [Adventure Works]

The query below returns the first sibling for the parent of the member November 2003 in

the Calendar hierarchy in the Date dimension.SELECT

{} ON COLUMNS,{[Date].[Calendar].[Month].[November 2003].FirstSibling} ON ROWS


The following query returns the first sibling for the parent of the member November 15,

2003 in the Calendar hierarchy in the Date dimension.SELECT

{} ON COLUMNS,

{[Date].[Calendar].[November 15, 2003].FirstSibling} ON ROWS



15/158

IsAncestorThe IsAncestor function returns true or false depending if a supplied member is an ancestorof another supplied member.

The IsAncestorfunction returns trueif the first member specified is an ancestor of thesecond member specified. Otherwise, the function returns false.Syntax


IsAncestor(Member_Expression1, Member_Expression2)

The query below returns True for the member Bikes since it is an ancestor of Road-650

Black, 58. All other Category members are not ancestors for Road-650 Black, 58, therefore,the IsAncestor function returns False.WITH

MEMBER [Measures].[IsAnAncestor] ASISANCESTOR([Product].[Product Categories].CurrentMember,

[Product].[Product Categories].[Product].[Road-650 Black, 58])

SELECT{[Measures].[IsAnAncestor]} ON COLUMNS,

{[Product].[Product Categories].[Category]} ON ROWS


The query below returns True for the member CY 2001 since it is an ancestor of September

2001. All other Calendar Years are not ancestors for September 2001, therefore, theIsAncestor function returns FalseWITH

MEMBER [Measures].[TrueOrFalse] ASISANCESTOR([Date].[Calendar].CurrentMember,

[Date].[Calendar].[September 2001])SELECT

{[Measures].[TrueOrFalse]} ON COLUMNS,{[Date].[Calendar].[Calendar Year].Members} ON ROWSFROM [Adventure Works]

IsGenerationThe IsGeneration function returns either true or false depending on whether the supplied

member is in a supplied generation.SyntaxIsGeneration(Member_Expression, Generation_Number)

The query below returns True or False if the current member of the Calendar hierarchy is atthe first generation. The first generation is the month level.WITH

MEMBER [Measures].[IsAGen] ASISGENERATION([Date].[Calendar].CurrentMember, 1)

SELECT{[Measures].[IsAGen]} ON COLUMNS,

{[Date].[Calendar].AllMembers} ON ROWS


The query below returns True or False if the current member of the Calendar hierarchy is atthe third generation. The third generation is the Calendar Semester level.


16/158

WITH

MEMBER [Measures].[IsAGen] AS

ISGENERATION([Date].[Calendar].CurrentMember, 3)SELECT

{[Measures].[IsAGen]} ON COLUMNS,{[Date].[Calendar].AllMembers} ON ROWS


The following query uses Filter function along with the IsGeneration function to return allmembers from the fourth generation (Calendar Year level).SELECT

{} ON COLUMNS,

FILTER({[Date].[Calendar].AllMembers},ISGENERATION([Date].[Calendar].CurrentMember, 4)) ON ROWSFROM [Adventure Works]

IsLeafThe IsLeaf function returns either true or false depending on whether the supplied member

is a leaf.

Syntax

IsLeaf(Member_Expression)

The query below returns True or False if the current member of the Calendar hierarchy is aleaf.WITH

MEMBER [Measures].[IsALeaf] ASISLEAF([Date].[Calendar].CurrentMember)

SELECT{[Measures].[IsALeaf]} ON COLUMNS,

{[Date].[Calendar].AllMembers} ON ROWS


The following query uses the Filter function along with the IsLeaf function to return allmembers of the Calendar hierarchy that are at the leaf level.SELECT

{} ON COLUMNS,

FILTER({[Date].[Calendar].AllMembers},

ISLEAF([Date].[Calendar].CurrentMember)) ON ROWSFROM [Adventure Works]

http://technet.microsoft.com/en-us/library/ms144932.aspx

MDX KPI Functions

1) KPICurrentTimeMemberReturns the current time member of the specified Key Performance Indicator (KPI).Syntax

KPICurrentTimeMember(KPI_Name)ArgumentsKPI_NameA valid string expression that specifies the name of the KPI.

RemarksA KPI can have a different time member from the default member of the time dimension.


17/158

2) KPIGoal

Returns the member that calculates the value for the goal portion of the specified KeyPerformance Indicator (KPI).Syntax

KPIGoal(KPI_Name)

ArgumentsKPI_NameA valid string expression that specifies the name of a KPI.

Remarks

ExampleThe following example returns the KPI value, KPI goal, KPI status, and KPI trend for thechannel revenue measure for the descendants of three members of the Fiscal Year attribute

hierarchy:SELECT

{ KPIValue("Channel Revenue"),KPIGoal("Channel Revenue"),KPIStatus("Channel Revenue"),KPITrend("Channel Revenue")

} ON Columns,Descendants

( { [Date].[Fiscal].[Fiscal Year].&[2002],[Date].[Fiscal].[Fiscal Year].&[2003],[Date].[Fiscal].[Fiscal Year].&[2004]

}, [Date].[Fiscal].[Fiscal Quarter]) ON Rows


3) KPIStatusReturns a normalized value that represents the status portion of the specified Key

Performance Indicator (KPI).Syntax

KPIStatus(KPI_Name)

Arguments

KPI_Name

A valid string expression that specifies the name of the KPI.Remarks

The status value is generally a normalized value between -1 and 1.Example

The following example returns the KPI value, KPI goal, KPI status, and KPI trend for thechannel revenue measure for the descendants of three members of the Fiscal Year attribute

hierarchy:SELECT

{ KPIValue("Channel Revenue"),KPIGoal("Channel Revenue"),KPIStatus("Channel Revenue"),

KPITrend("Channel Revenue")} ON Columns,

Descendants( { [Date].[Fiscal].[Fiscal Year].&[2002],

[Date].[Fiscal].[Fiscal Year].&[2003],[Date].[Fiscal].[Fiscal Year].&[2004]


http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


18/158

4) KPITrendReturns the normalized value that represents the trend portion of the specified Key

Performance Indicator (KPI).Syntax

KPITrend(KPI_Name)Arguments

KPI_NameA valid string expression that specifies the name of the KPI.Remarks

The trend value is generally a normalized value between -1 and 1.

ExampleThe following example returns the KPI value, KPI goal, KPI status, and KPI trend for thechannel revenue measure for the descendants of three members of the Fiscal Year attribute

hierarchy:SELECT






5) KPIValueReturns the member that calculates the value of the specified Key Performance Indicator

(KPI).Syntax

KPIValue(KPI_Name)

Arguments

KPI_Name

A valid string expression that specifies the name of the KPI.Remarks

ExampleThe following example returns the KPI value, KPI goal, KPI status, and KPI trend for the

channel revenue measure for the descendants of three members of the Fiscal Year attributehierarchy.SELECT







19/158

6) KPIWeightReturns the weight of the specified Key Performance Indicator (KPI).

SyntaxKPIWeight(KPI_Name)Arguments

KPI_Name

A valid string expression that specifies the name of the KPI.RemarksThe value returned is the contribution of the KPI to the parent.

MDX Metadata Functions1) Axis

Returns the set of tuples on a specified axis.Syntax

Axis(Axis_Number)Arguments

Axis_Number

A valid numeric expression that specifies the axis number.Remarks

The Axisfunction uses the zero-based position of an axis to return the set of tuples on an

axis. For example, Axis(0) returns the COLUMNS axis, Axis(1) returns the ROWS axis, andso on. The Axisfunction cannot be used on the filter axis. This function can be used tomake calculated members aware of the context of the query that is being run. For example,

you might need a calculated member that provides the sum of only those members selected

on the Rows axis. It can also be used to make the definition of one axis dependent on thedefinition of another. For example, by ordering the contents of the Rows axis according tothe value of the first item on the Columns axis.

Note

An axis can reference only a prior axis. For example, Axis(0) must occur after the COLUMNS

axis has been evaluated, such as on a ROW or PAGE axis.ExamplesThe following example query shows how to use the Axis function:

WITH MEMBER MEASURES.AXISDEMO AS

SETTOSTR(AXIS(1))

SELECT MEASURES.AXISDEMO ON 0,[Date].[Calendar Year].MEMBERS ON 1


The following example shows the use of the Axis function inside a calculated member:

WITH MEMBER MEASURES.AXISDEMO ASSUM(AXIS(1), [Measures].[Internet Sales Amount])

SELECT {[Measures].[Internet Sales Amount],MEASURES.AXISDEMO} ON 0,{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]} ON 1FROM [Adventure Works]

2) Count (Dimension)Returns the number of hierarchies in a cube.
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


20/158

Syntax

Dimensions.CountRemarksReturns the number of hierarchies in a cube,including the [Measures].[Measures] hierarchy.

Example

The following example returns the number of hierarchies in the Adventure Works cube.

WITH MEMBER measures.X ASdimensions.count

SELECT Measures.X ON 0FROM [Adventure Works]

3) Count (Hierarchy Levels)Returns the number of levels in hierarchy.Syntax

Hierarchy_Expression.Levels.CountArguments

Hierarchy_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a hierarchy.Remarks

Returns the number of levels in a hierarchy, including the [All] level, if applicable.

Important

When a dimension contains only a single visible hierarchy, the hierarchy can be referred to

either by the dimension name or by the hierarchy name, because the dimension name isresolved to its only visible hierarchy. For example,Measures.Levels.Count is a valid MDX

expression because it resolves to the only hierarchy in the Measures dimension.

Example

The following example returns a count of the number of levels in the ProductCategories user-defined hierarchy in the Adventure Works cube.WITH MEMBER measures.X AS

[Product].[Product Categories].Levels.Count

Select Measures.X ON 0FROM [Adventure Works]

4) Count (Tuple)Returns the number of dimensions in a tuple.Syntax

Tuple_Expression.Count

ArgumentsTuple_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a tuple.

RemarksReturns the number of dimensions in a tuple.

ExampleThe calculated measure in the following query returns the value 2, which is the number of

hierarchies present in the tuple([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Year].&[2001]):
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


21/158

WITH MEMBER MEASURES.COUNTTUPLE AS

COUNT(([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].&[2001]))

SELECT MEASURES.COUNTTUPLE ON 0


5) HierarchyReturns the hierarchy that contains a specified member or level.Syntax

Member expression syntax

Member_Expression.Hierarchy

Level expression syntax

Level_Expression.Hierarchy

ArgumentsMember_Expression

A valid Multidimensional Expressions (MDX) expression that returns a member.

Level_Expression

A valid Multidimensional Expressions (MDX) expression that returns a level.ExamplesThe following example returns the name of the Calendar hierarchy in the Data dimension in

the AdventureWorks cube.

WITH

MEMBER Measures.HierarchyName as[Date].[Calendar].Currentmember.Hierarchy.Name

SELECT {Measures.HierarchyName} ON 0,{[Date].[Calendar].[All Periods]} ON 1FROM [Adventure Works]

6) LevelReturns the level of a member.Syntax

Member_Expression.Level


A valid Multidimensional Expression (MDX) that returns a member.

ExamplesThe following example uses the Levelfunction to return all months in the Adventure Works

cube.

SELECT[Date].[Fiscal].[Month].[February 2002].Level.Members ON 0,[Measures].[Internet Sales Amount] ON 1


The following example uses the Levelfunction to return the name of the level for the All-

Purpose Bike Stand in the Model Name attribute hierarchy in the Adventure Works cube.

WITH MEMBER Measures.x AS

[Product].[Model Name].[All-Purpose Bike Stand].Level.Name


22/158

SELECT Measures.x ON 0


7) LevelsReturns the level whose position in a dimension or hierarchy is specified by a numericexpression or whose name is specified by a string expression.

SyntaxNumeric expression syntaxHierarchy_Expression.Levels( Level_Number )

String expression syntaxHierarchy_Expression.Levels( Level_Name )Arguments

Hierarchy_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a hierarchy.

Level_NumberA valid numeric expression that specifies a level number.

Level_NameA valid string expression that specifies a level name.

RemarksIf a level number is specified, the Levelsfunction returns the level associated with thespecified zero-based position.

If a level name is specified, the Levelsfunction returns the specified level.

Note

Use the string expression syntax for user-defined functions.

Examples

The following examples illustrate each of the Levelsfunction syntaxes.

NumericThe following example returns the Country level:

SELECT [Geography].[Geography].Levels(1) ON 0FROM [Adventure Works]

String

The following example returns the Country level:

SELECT [Geography].[Geography].Levels('Country') ON 0FROM [Adventure Works]

8) NameReturns the name of a dimension, hierarchy, level, or member.

Syntax

Dimension expression syntaxDimension_Expression.Name

Hierarchy expression syntaxHierarchy_Expression.Name

Level_expression syntax

Level_Expression.Name


23/158

Member expression syntaxMember_Expression.Name

ArgumentsDimension_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a dimension.

Hierarchy_Expression

A valid Multidimensional Expressions (MDX) expression that returns a hierarchy.Level_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a level.

Member_Expression


Remarks

The Namefunction returns the name of the object, not the unique name.

ExamplesDimension, Hierarchy and Level Expression ExampleThe following example returns the dimension name for the Date dimension and the

hierarchy and level names for the July 2001 member.

WITH MEMBER Measures.DimensionName AS [Date].NameMEMBER Measures.HierarchyName AS [Date].[Calendar].[July 2001].Hierarchy.Name

MEMBER Measures.LevelName as [Date].[Calendar].[July 2001].Level.Name

SELECT {Measures.DimensionName, Measures.HierarchyName, Measures.LevelName} ON 0

from [Adventure Works]

Member Expression ExampleThe following example returns the member name, along with the member value, member

key and member caption.

WITH MEMBER MemberName AS [Date].[Calendar].[July 1, 2001].Name

MEMBER Measures.ValueColumn as [Date].[Calendar].[July 1, 2001].MemberValueMEMBER Measures.KeyColumn as [Date].[Calendar].[July 1, 2001].Member_KeyMEMBER Measures.NameColumn as [Date].[Calendar].[July 1, 2001].Member_Name

SELECT {Measures.MemberName, Measures.ValueColumn, Measures.KeyColumn,Measures.NameColumn} ON 0from [Adventure Works]

9) OrdinalReturns the zero-based ordinal value associated with a level.Syntax

Level_Expression.Ordinal

Arguments

Level_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a level.

RemarksThe Ordinalfunction is frequently used in conjunction withthe IIFand CurrentMemberfunctions to conditionally display different values at different

hierarchy levels, based on the ordinal position of each specific cell in the query result. For

example, you can use the Ordinalfunction to perform calculations at certain levels anddisplay a default value of "N/A" at other levels.Example
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


24/158

The following example returns the ordinal number for the Calendar Quarter level in theCalendar hierarchy.

WITH MEMBER Measures.x AS [Date].[Calendar].[Calendar Quarter].Ordinal

SELECT Measures.x on 0


UniqueNameReturns the unique name of a specified dimension, hierarchy, level, or member.

SyntaxDimension expression syntaxDimension_Expression.UniqueName


Hierarchy_Expression.UniqueName

Level expression syntaxLevel_Expression.UniqueName

Member expression syntaxMember_Expression.UniqueName

Arguments

Dimension_ExpressionA valid Multidimensional Expressions (MDX) expression that resolves to a dimension.

Hierarchy_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a hierarchy.

Level_Expression

A valid Multidimensional Expressions (MDX) expression that returns a level.Member_Expression


RemarksThe UniqueNamefunction returns the unique name of the object, not the name returnedby theNamefunction. The returned name does not include the name of the cube. The

results returned depend upon the server-side settings or the MDX Unique Name Style

connection string property.Example

The following example returns the unique name value for the Product dimension, the

Product Categories hierarchy, the Subcategory level, and the Bike Racks member in theAdventure Works cube.

WITH MEMBER DimensionUniqueNameAS [Product].UniqueName

MEMBER HierarchyUniqueName

AS [Product].[Product Categories].UniqueNameMEMBER LevelUniqueName

AS [Product].[Product Categories].[Subcategory].UniqueName

MEMBER MemberUniqueNameAS [Product].[Product Categories].[Subcategory].[Bike Racks]
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145583.aspxhttp://technet.microsoft.com/en-us/library/ms145583.aspxhttp://technet.microsoft.com/en-us/library/ms145583.aspxhttp://technet.microsoft.com/en-us/library/ms145583.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/


25/158

SELECT

{DimensionUniqueName

, HierarchyUniqueName, LevelUniqueName

, MemberUniqueName }ON 0


MDX Navigation Functions1) Ancestor

A function that returns the ancestor of a specified member at a specified level or at a

specified distance from the member.Syntax

Level syntaxAncestor(Member_Expression, Level_Expression)

Numeric syntaxAncestor(Member_Expression, Distance)

ArgumentsMember_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a member.


2) Distance

A valid numeric expression that specifies the distance from the specified member.

Remarks

With the Ancestorfunction, you provide the function with an MDX member expression and

then provide either an MDX expression of a level that is an ancestor of the member or anumeric expression that represents the number of levels above that member. With thisinformation, the Ancestorsfunction returns the ancestor member at that level.

Note

To return a set containing the ancestor member, instead of just the ancestor member, use

theAncestors (MDX)function.

If a level expression is specified, the Ancestorfunction returns the ancestor of specified

member at the specified level. If the specified member is not within the same hierarchy asspecified level, the function returns an error.

If a distance is specified, the Ancestorfunction returns the ancestor of the specifiedmember that is the number of steps specified up in the hierarchy specified by the member

expression. A member may be specified as a member of an attribute hierarchy, a user-defined hierarchy, or in some cases, a parent-child hierarchy. A number of 1 returns a

member's parent and a number of 2 returns a member's grandparent (if one exists). A

number of 0 returns the member itself.

Note
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145620.aspxhttp://technet.microsoft.com/en-us/library/ms145620.aspxhttp://technet.microsoft.com/en-us/library/ms145620.aspxhttp://technet.microsoft.com/en-us/library/ms145620.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/


26/158

Use this form of the Ancestorfunction for cases in which the level of the parent is unknown orcannot be named.

Examples

The following example uses a level expression and returns the Internet Sales

Amount for each State-Province in Australia and its percent of the total InternetSales Amount for Australia.

WITH MEMBER Measures.x AS [Measures].[Internet Sales Amount] /

([Measures].[Internet Sales Amount],

Ancestor

([Customer].[Customer Geography].CurrentMember,

[Customer].[Customer Geography].[Country]

)), FORMAT_STRING = '0%'

SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0,

{Descendants

(

[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[State-Province], SELF

)} ON 1


The following example uses a numeric expression and returns the Internet Sales

Amount for each State-Province in Australia and its percent of the total Internet

Sales Amount for all countries.

WITH MEMBER Measures.x AS [Measures].[Internet Sales Amount] /

([Measures].[Internet Sales Amount],

Ancestor

([Customer].[Customer Geography].CurrentMember, 2)

), FORMAT_STRING = '0%'SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0,

{

Descendants(

[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[State-Province], SELF

)} ON 1


3) AncestorsA function that returns the set of all ancestors of a specified member at a specified level orat a specified distance from the member. With Microsoft SQL Server Analysis Services, the


27/158

set returned will always consist of a single member - Analysis Services does not supportmultiple parents for a single member.

Syntax

Level syntaxAncestors(Member_Expression, Level_Expression)

Numeric syntaxAncestors(Member_Expression, Distance)



4) Distance

A valid numeric expression that specifies the distance from the specified member.

RemarksWith the Ancestorsfunction, you provide the function with an MDX member expression andthen provide either an MDX expression of a level that is an ancestor of that member or a

numeric expression that represents the number of levels above that member. With thisinformation, the Ancestorsfunction returns the set of members (which will be a set

consisting of one member) at that level.

Note

To return an ancestor member, rather than an ancestor set, use theAncestorfunction.

If a level expression is specified, the Ancestorsfunction returns the set of all ancestors ofthe specified member at the specified level. If the specified member is not within the same

hierarchy as the specified level, the function returns an error.If a distance is specified, the Ancestorsfunction returns the set of all members that are the

number of steps specified up in the hierarchy specified by the member expression. Amember may be specified as a member of an attribute hierarchy, a user-defined hierarchy,

or, in some cases, a parent-child hierarchy. A number of 1 returns the set of members at

the parent level and a number of 2 returns the set of members at the grandparent level (ifone exists). A number of 0 returns the set including only the member itself.

Note

Use this form of the Ancestorsfunction for cases in which the level of the parent is unknown orcannot be named.

Examples

The following example uses the Ancestorsfunction to return the Internet Sales Amount

measure for a member, its parent, and its grandparent. This example uses level expressionsto specify the levels to be returned. The levels are in the same hierarchy as the memberspecified in the member expression.

SELECT {
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145616.aspxhttp://technet.microsoft.com/en-us/library/ms145616.aspxhttp://technet.microsoft.com/en-us/library/ms145616.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145616.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/


28/158

Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver,38],[Product].[Product Categories].[Category]),

Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver,38],[Product].[Product Categories].[Subcategory]),

Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver,38],[Product].[Product Categories].[Product])

} ON 0,[Measures].[Internet Sales Amount] ON 1FROM [Adventure Works]

The following example uses the Ancestorsfunction to return the Internet Sales Amountmeasure for a member, its parent, and its grandparent. This example uses numericexpressions to specify the levels being returned. The levels are in the same hierarchy as the

member specified in the member expression.

SELECT {Ancestors(

[Product].[Product Categories].[Product].[Mountain-100 Silver, 38],2),

Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver, 38],1),

Ancestors([Product].[Product Categories].[Product].[Mountain-100 Silver, 38],0

)} ON 0,

[Measures].[Internet Sales Amount] ON 1FROM [Adventure Works]

The following example uses the Ancestorsfunction to return the Internet Sales Amountmeasure for the parent of a member of an attribute hierarchy. This example uses a numeric

expression to specify the level being returned. Since the member in the member expressionis a member of an attribute hierarchy, its parent is the [All] level.

SELECT {

Ancestors(

[Product].[Product].[Mountain-100 Silver, 38],1)

} ON 0,[Measures].[Internet Sales Amount] ON 1


5) AscendantsReturns the set of the ascendants of a specified member, including the member itself.

SyntaxAscendants(Member_Expression)


Remarks

The Ascendantsfunction returns all of the ancestors of a member from the member itselfup to the top of the members hierarchy; more specifically, it performs a post -ordertraversal of the hierarchy for the specified member, and then returns all ascendant


29/158

members related to the member, including itself, in a set. This is in contrast totheAncestorfunction, which returns a specific ascendant member, or ancestor, at a specific

level.Examples

The following example returns the count of reseller orders for the [Sales

Territory].[Northwest] member and all the ascendants of that member from the AdventureWorkscube. The Ascendantsfunction constructs the set that includes the[SalesTerritory].[Northwest] member and its ascendants for the ROWS axis.

SELECTMeasures.[Reseller Order Count] ON COLUMNS,Order(

Ascendants([Sales Territory].[Northwest]

),DESC

) ON ROWSFROM

[Adventure Works]

6) ChildrenReturns the set of children of a specified member.

Syntax

Member_Expression.ChildrenArguments

Member_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a member.

Remarks

The Childrenfunction returns a naturally ordered set that contains the children of a

specified member. If the specified member has no children, this function returns an emptyset.

ExampleThe following example returns the children of the United States member of the Geographyhierarchy in the Geography dimension.

SELECT [Geography].[Geography].[Country].&[United States].Children ON 0FROM [Adventure Works]

The following example returns all members in the Measuresdimension on the column axis,

this includes all calculated members, and the set of all children of the [Product].[ModelName] attribute hierarchy on the row axis from theAdventure Workscube.

SELECTMeasures.AllMembers ON COLUMNS,[Product].[Model Name].Children ON ROWS


CousinReturns the child member with the same relative position under a parent member as thespecified child member.
http://technet.microsoft.com/en-us/library/ms145616.aspxhttp://technet.microsoft.com/en-us/library/ms145616.aspxhttp://technet.microsoft.com/en-us/library/ms145616.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145616.aspx


30/158

Syntax

Cousin( Member_Expression , Ancestor_Member_Expression )

Arguments

Member_Expression


Ancestor_Member_Expression

A valid Multidimensional Expressions (MDX) member expression that returns an ancestor

member.

Remarks

This function operates on the order and position of members within levels. If two hierarchiesexist, in which the first one has four levels and the second one has five levels, the cousin of

the third level of the first hierarchy is the third level of the second hierarchy.Examples

The following example retrieves the cousin of the fourth quarter of fiscal year 2002, basedon its ancestor at the year level in fiscal year 2003. The retrieved cousin is the fourth

quarter of fiscal year 2003.

SELECT Cousin

( [Date].[Fiscal].[Fiscal Quarter].[Q4 FY 2002],[Date].[Fiscal].[FY 2003]

) ON 0FROM [Adventure Works]

The following example retrieves the cousin of the month of July of fiscal year 2002 based on

its ancestor at the quarter level in the second quarter of fiscal year 2004. The retrieved

cousin is the month of October of 2003.

SELECT Cousin([Date].[Fiscal].[Month].[July 2002] ,

[Date].[Fiscal].[Fiscal Quarter].[Q2 FY 2004]) ON 0


Current

Returns the current tuple from a set during iteration.

SyntaxSet_Expression.Current

Arguments

Set_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a set.

RemarksAt each step during an iteration, the tuple being operated upon is the current tuple.

The Currentfunction returns that tuple. This function is only valid during an iteration over a

set.MDX functions that iterate through a set include theGeneratefunction.
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


31/158

Note

This function only works with sets that are named, either using a set alias or by defining anamed set.

Examples

The following example shows how to use the Currentfunction inside Generate:WITH

//Creates a set of tuples consisting of all Calendar Years crossjoined with

//all Product Categories

SET MyTuples AS CROSSJOIN(

[Date].[Calendar Year].[Calendar Year].MEMBERS,[Product].[Category].[Category].MEMBERS)

//Iterates through each tuple in the set and returns the name of the Calendar

//Year in each tuple

MEMBER MEASURES.CURRENTDEMO ASGENERATE(MyTuples, MyTuples.CURRENT.ITEM(0).NAME, ", ")

SELECT MEASURES.CURRENTDEMO ON 0


CurrentMemberReturns the current member along a specified hierarchy during iteration.

Syntax

Hierarchy_Expression.CurrentMember

ArgumentsHierarchy_Expression

A valid Multidimensional Expressions (MDX) expression that returns a hierarchy.

Remarks

When iterating through a set of hierarchy members, at each step in the iteration, themember being operated upon is the current member. The CurrentMemberfunction returnsthat member.

Important

When a dimension contains only a single visible hierarchy, the hierarchy can be referred to

either by the dimension name or by the hierarchy name, because the dimension name is

resolved to its only visible hierarchy. For example,Measures.CurrentMember is a valid MDXexpression because it resolves to the only hierarchy in the Measures dimension.

ExamplesThe following query shows how Currentmembercan be used to find the current memberfrom hierarchies on the Columns, Rows and slice axis:

WITH MEMBER MEASURES.CURRENTDATEAS[Date].[Calendar].CURRENTMEMBER.NAME


32/158

MEMBER MEASURES.CURRENTPRODUCTAS[Product].[Product Categories].CURRENTMEMBER.NAME

MEMBER MEASURES.CURRENTMEASUREASMEASURES.CURRENTMEMBER.NAME

MEMBER MEASURES.CURRENTCUSTOMERAS[Customer].[Customer Geography].CURRENTMEMBER.NAME

SELECT[Product].[Product Categories].[Category].MEMBERS

*

{MEASURES.CURRENTDATE,MEASURES.CURRENTPRODUCT,MEASURES.CURRENTMEASURE,MEASURES.CURRENTCUSTOMER}

ON 0,[Date].[Calendar].MEMBERS

ON 1FROM [Adventure Works]

WHERE([Customer].[Customer Geography].[Country].&[Australia])

The current member changes on a hierarchy used on an axis in a query. Therefore, thecurrent member on other hierarchies on the same dimension that are not used on an axiscan also change; this behavior is called 'auto-exists' and more details can be found inKey

Concepts in MDX (Analysis Services).For example, the query below shows how the currentmember on the Calendar Year hierarchy of the Date dimension changes with the current

member on the Calendar hierarchy, when the latter is displayed on the Rows axis:

WITH MEMBER MEASURES.CURRENTYEAR AS[Date].[Calendar Year].CURRENTMEMBER.NAME

SELECT{MEASURES.CURRENTYEAR}

ON 0,[Date].[Calendar].MEMBERS

ON 1FROM [Adventure Works]

CurrentMemberis very important for making calculations aware of the context of thequery they are being used in. The following example returns the order quantity of each

product and the percentage of order quantities by category and model, from the Adventure

Workscube. The CurrentMemberfunction identifies the product whose order quantity is

to be used during calculation.

WITH

MEMBER [Measures].[Order Percent by Category] AS

CoalesceEmpty(

([Product].[Product Categories].CurrentMember,Measures.[Order Quantity]) /(

Ancestor

( [Product].[Product Categories].CurrentMember,[Product].[Product Categories].[Category]

), Measures.[Order Quantity]
http://technet.microsoft.com/en-us/library/ms144884.aspxhttp://technet.microsoft.com/en-us/library/ms144884.aspxhttp://technet.microsoft.com/en-us/library/ms144884.aspxhttp://technet.microsoft.com/en-us/library/ms144884.aspxhttp://technet.microsoft.com/en-us/library/ms144884.aspxhttp://technet.microsoft.com/en-us/library/ms144884.aspx


33/158

), 0), FORMAT_STRING='Percent'

SELECT{Measures.[Order Quantity],

[Measures].[Order Percent by Category]} ON COLUMNS,

{[Product].[Product].Members} ON ROWSFROM [Adventure Works]WHERE {[Date].[Calendar Year].[Calendar Year].&[2003]}

CurrentOrdinalReturns the current iteration number within a set during iteration.

SyntaxSet_Expression.CurrentOrdinal

Arguments

Set_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a set.

RemarksWhen iterating through a set, such as with theFilter (MDX)orGenerate (MDX)functions,

the CurrentOrdinalfunction returns the iteration number.Examples

The following simple example shows how CurrentOrdinalcan be used with Generatetoreturn a string containing the name of each item in a set along with its position in the set:

WITH SET MySet AS [Customer].[Customer Geography].[Country].MEMBERS

MEMBER MEASURES.CURRENTORDINALDEMO AS

GENERATE(MySet, CSTR(MySet.CURRENTORDINAL) + ") " +MySet.CURRENT.ITEM(0).NAME, ", ")

SELECT MEASURES.CURRENTORDINALDEMO ON 0


The practical use of CurrentOrdinal is limited to very complex calculations. The following

example returns the number of products in the set that are unique, usingthe Orderfunction to order the non-empty tuples before utilizing the Filterfunction.

The CurrentOrdinalfunction is used to compare and eliminate ties.

WITH MEMBER [Measures].[PrdTies] AS Count(Filter

(Order

(NonEmpty

([Product].[Product].[Product].Members, {[Measures].[Reseller Order Quantity]}

), [Measures].[Reseller Order Quantity], BDESC

) AS OrdPrds

, NOT((OrdPrds.CurrentOrdinal < OrdPrds.CountAND [Measures].[Reseller Order Quantity] =

( [Measures].[Reseller Order Quantity]
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms146037.aspxhttp://technet.microsoft.com/en-us/library/ms146037.aspxhttp://technet.microsoft.com/en-us/library/ms146037.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms145526.aspxhttp://technet.microsoft.com/en-us/library/ms146037.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/


34/158

, OrdPrds.Item(OrdPrds.CurrentOrdinal

))

)OR (OrdPrds.CurrentOrdinal > 1

AND [Measures].[Reseller Order Quantity] =([Measures].[Reseller Order Quantity], OrdPrds.Item

(OrdPrds.CurrentOrdinal-2)

))

))

)SELECT {[Measures].[PrdTies]} ON 0


DataMemberReturns the system-generated data member that is associated with a nonleaf member of a

dimension.

Syntax

Member_Expression.DataMember

Arguments

Member_Expression


Remarks

This function operates on nonleaf members in any hierarchy and can be used bytheUPDATE CUBE Statement (MDX)command to writeback data to a nonleaf member

directly, rather than to the leaf member's descendants.

Note

Returns the specified member if the specified member is a leaf member, or if the nonleaf

member does not have an associated data member.

ExampleThe following example uses the DataMemberfunction in a calculated measure to show the

sales quota for each individual employee:

WITH MEMBER measures.InvidualQuota AS

([Employee].[Employees].currentmember.datamember, [Measures].[Sales Amount Quota])

,FORMAT_STRING='Currency'SELECT {[Measures].[Sales Amount Quota],[Measures].InvidualQuota} ON COLUMNS,[Employee].[Employees].MEMBERS ON ROWS


DefaultMemberReturns the default member of a hierarchy.

Syntax
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145488.aspxhttp://technet.microsoft.com/en-us/library/ms145488.aspxhttp://technet.microsoft.com/en-us/library/ms145488.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145488.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/


35/158

Hierarchy_Expression.DefaultMember

ArgumentsHierarchy_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a hierarchy.

RemarksThe default member on an attribute is used to evaluate expressions when an attribute is notincluded in a query.

ExampleThe following example uses the DefaultMemberfunction, in conjunction withthe Namefunction, to return the default member for the Destination Currency dimension in

the Adventure Works cube. The example returns US Dollar. The Namefunction is used toreturn the name of the measure rather than the default property of the measure, which

is value.

WITH MEMBER Measures.x AS[Destination Currency].[Destination Currency].DefaultMember.Name

SELECT Measures.x ON 0FROM [Adventure Works]

FirstChildReturns the first child of a specified member.

Syntax

Member_Expression.FirstChild

Arguments


Example

The following query returns the first child of fiscal year 2003 in the Fiscal hierarchy, which isthe first semester of Fiscal Year 2003.

SELECT [Date].[Fiscal].[Fiscal Year].&[2003].FirstChild ON 0FROM [Adventure Works]

FirstSiblingReturns the first child of the parent of a member.

Syntax


Arguments

Member_ExpressionA valid Multidimensional Expressions (MDX) expression that returns a member.Example

The following query returns the first sibling of fiscal year 2003 in the Fiscal hierarchy, whichis Fiscal Year 2002.


36/158

SELECT [Date].[Fiscal].[Fiscal Year].&[2003].FirstSibling ON 0FROM [Adventure Works]

IsAncestorReturns whether a specified member is an ancestor of another specified member.

SyntaxIsAncestor(Member_Expression1, Member_Expression2)

Arguments

Member_Expression1A valid Multidimensional Expressions (MDX) expression that returns a member.


Remarks

The IsAncestorfunction returns trueif the first member specified is an ancestor of thesecond member specified. Otherwise, the function returns false.

Example

The following example returns trueif [Date].[Fiscal].CurrentMember is an ancestor of

January 2003:

WITH MEMBER MEASURES.ISANCESTORDEMO ASIsAncestor([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Month].&[2003]&[1])

SELECT MEASURES.ISANCESTORDEMO ON 0,

[Date].[Fiscal].MEMBERS ON 1


IsGenerationReturns whether a specified member is in a specified generation.

Syntax

IsGeneration(Member_Expression, Generation_Number)

Arguments


Generation_Number

A valid numeric expression that specifies the generation against which the specified member

is evaluated.

Remarks

The IsGenerationfunction returns trueif the specified member is in the specifiedgeneration number. Otherwise, the function returns false. Also, if the specified memberevaluates to an empty member, the IsGenerationfunction returns false.

For the purposes of generation indexing, leaf members are generation index 0. The

generation index of nonleaf members is determined by first getting the highest generationindex from the union of all child members for the specified member, then adding 1 to that


37/158

index. Because of how the generation index of nonleaf members is determined, a specificnonleaf member could belong to more than one generation.

Example

The following example returns TRUE if [Date].[Fiscal].CurrentMember is part of the secondgeneration:

WITH MEMBER MEASURES.ISGENERATIONDEMO ASIsGeneration([Date].[Fiscal].CURRENTMEMBER, 2)

SELECT {MEASURES.ISGENERATIONDEMO} ON 0,

[Date].[Fiscal].MEMBERS ON 1FROM [Adventure Works]

IsLeafReturns whether a specified member is a leaf member.

Syntax

IsLeaf(Member_Expression)


Remarks

The IsLeaffunction returns trueif the specified member is a leaf member. Otherwise, thefunction returns false.

ExampleThe following example returns TRUE if [Date].[Fiscal].CurrentMember is a leaf member:

WITH MEMBER MEASURES.ISLEAFDEMO ASIsLeaf([Date].[Fiscal].CURRENTMEMBER)

SELECT {MEASURES.ISLEAFDEMO} ON 0,[Date].[Fiscal].MEMBERS ON 1


IsSiblingReturns whether a specified member is a sibling of another specified member.

SyntaxIsSibling(Member_Expression1, Member_Expression2)

Arguments

Member_Expression1



RemarksThe IsSiblingfunction returns trueif the first specified member is a sibling of the second

specified member. Otherwise, the function returns false.

Example


38/158

The following example returns TRUE if the current member on the Fiscal hierarchy of theDate dimension is a sibling of July 2002:

WITH MEMBER MEASURES.ISSIBLINGDEMO ASIsSibling([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month].&[2002]&[7])

SELECT {MEASURES.ISSIBLINGDEMO} ON 0,[Date].[Fiscal].MEMBERS ON 1FROM [Adventure Works]

LagReturns the member that is a specified number of positions before a specified member atthe member's level.

SyntaxMember_Expression.Lag(Index)

Arguments


IndexA valid numeric expression that specifies the number of member positions to lag.Remarks

Member positions within a level are determined by the attribute hierarchy's natural order.The numbering of the positions is zero-based.

If the specified lag is zero, the Lagfunction returns the specified member itself.If the specified lag is negative, the Lagfunction returns a subsequent member.

Lag(1) is equivalent to thePrevMemberfunction. Lag(-1) is equivalent totheNextMemberfunction.

The Lagfunction is similar to theLeadfunction, except that the Leadfunction looks in the

opposite direction to the Lagfunction. That is, Lag(n) is equivalent to Lead(-n).Example

The following example returns the value for December 2001:

SELECT [Date].[Fiscal].[Month].[February 2002].Lag(2) ON 0FROM [Adventure Works]

The following example returns the value for March 2002:

SELECT [Date].[Fiscal].[Month].[February 2002].Lag(-1) ON 0FROM [Adventure Works]

LastChild

Returns the last child of a specified member.

Syntax

Member_Expression.LastChild


ExampleThe following example returns the value for September 2001, which is the last child of thefirst fiscal quarter of fiscal year 2002.
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms146081.aspxhttp://technet.microsoft.com/en-us/library/ms146081.aspxhttp://technet.microsoft.com/en-us/library/ms146081.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms146081.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/


39/158

SELECT [Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2002].LastChild ON 0


LastSiblingReturns the last child of the parent of a specified member.

SyntaxMember_Expression.LastSibling

Arguments


ExampleThe following example returns the default measure for the last day in July 2002.

SELECT [Date].[Fiscal].[Date].&[20020717].LastSiblingON 0


LeadReturns the member that is a specified number of positions following a specified memberalong the member's level.

Syntax

Member_Expression.Lead( Index )



IndexA valid numeric expression that specifies a number of member positions.

Remarks

Member positions within a level are determined by the attribute hierarchy's natural order.The numbering of the positions is zero-based.

If the specified lead is zero (0), the Leadfunction returns the specified member.

If the specified lead is negative, the Leadfunction returns a prior member.Lead(1) is equivalent to theNextMemberfunction. Lead(-1) is equivalent to

thePrevMemberfunction.The Leadfunction is similar to theLagfunction, except that the Lagfunction looks in the

opposite direction to the Leadfunction. That is, Lead(n) is equivalent to Lag(-n).

Example

The following example returns the value for December 2001:

SELECT [Date].[Fiscal].[Month].[February 2002].Lead(-2) ON 0FROM [Adventure Works]

The following example returns the value for March 2002:SELECT [Date].[Fiscal].[Month].[February 2002].Lead(1) ON 0


LinkMemberReturns the member equivalent to a specified member in a specified hierarchy.
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms144866.aspxhttp://technet.microsoft.com/en-us/library/ms144866.aspxhttp://technet.microsoft.com/en-us/library/ms144866.aspxhttp://technet.microsoft.com/en-us/library/ms144866.aspxhttp://technet.microsoft.com/en-us/library/ms144719.aspxhttp://technet.microsoft.com/en-us/library/ms145977.aspxhttp://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


40/158

Syntax

LinkMember(Member_Expression, Hierarchy_Expression)

Arguments

Member_Expression


Hierarchy_Expression

A valid Multidimensional Expressions (MDX) expression that returns a hierarchy.

Remarks

The LinkMemberfunction returns the member from the specified hierarchy that matchesthe key values at each level of the specified member in a related hierarchy. Attributes at

each level must have the same key cardinality and data type. In unnatural hierarchies, ifthere is more than one match for an attribute's key value, the result will be an error or

indeterminate.Examples

The following example uses the LinkMemberfunction to return the default measure in theAdventure Works cube for the ascendants of the July 1, 2002 member of the Date.Dateattribute hierarchy in the Calendar hierarchy.

SELECT Hierarchize

(Ascendants(LinkMember

([Date].[Date].[July 1, 2002], [Date].[Calendar])

)

) ON 0FROM [Adventure Works]

LookupCubeReturns the value of a Multidimensional Expressions (MDX) expression evaluated overanother specified cube in the same database.

SyntaxNumeric expression syntax

LookupCube(Cube_Name, Numeric_Expression )

String expression syntaxLookupCube(Cube_Name, String_Expression )

Arguments

Cube_NameA valid string expression that specifies the name of a cube.

Numeric_ExpressionA valid numeric expression that is typically a Multidimensional Expressions (MDX)expression of cell coordinates that return a number.

String_Expression

A valid string expression that is typically a valid Multidimensional Expressions (MDX)expression of cell coordinates that returns a string.
http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/http://void%280%29/


41/158


42/158


RemarksThe Parentfunction returns the parent member of the specified member.

Examples

The following examples return the parent of the July 1, 2001 member. The first examplespecifies this member in the context of the Date attribute hierarchy and returns the AllPeriods member.

SELECT [Date].[Date].[July 1, 2001].Parent ON 0FROM [Adventure Works]

The following example specifies the July 1, 2001 member in the context of the Calendarhierarchy.

SELECT [Date].[Calendar].[July 1, 2001].Parent ON 0


PrevMemberReturns the previous member in the level that contains a specified member.

SyntaxMember_Expression.PrevMember

Arguments


RemarksThe PrevMemberfunction returns the previous member in the same level as the specified

member.

ExampleThe following example shows a simple query that uses the PrevMemberfunction to display

the name of the member immediately before the current member on the rows axis:

WITH MEMBER MEASURES.PREVMEMBERDEMO AS

[Date].[Calendar].CURRENTMEMBER.PREVMEMBER.NAMESELECT MEASURES.PREVMEMBERDEMO ON 0,

[Date].[Calendar].MEMBERS ON 1FROM [Adventure Works]

The following example returns the count of the resellers whose sales have declined over the

previous time period, based on user-selected State-Province member values evaluatedusing the Aggregate function. The Hierarchizeand DrillDownLevelfunctions are used to

return values for declining sales for product categories in the Product dimension.The PrevMemberfunction is used to compare the current time period with the previoustime period.

WITH MEMBER Measures.[Declining Reseller Sales] ASCount(

Filter(


43/158

Existing(Reseller.Reseller.Reseller),[Measures].[Reseller Sales Amount] < ([Measures].[Reseller Sales Amount],

[Date].Calendar.PrevMember))

)MEMBER [Geography].[State-Province].x AS

Aggregate ({[Geography].[State-Province].&[WA]&[US],[Geography].[State-Province].&[OR]&[US] }

)

SELECT NON EMPTY Hierarchize (AddCalculatedMembers (

{DrillDownLevel({[Product].[All Products]})}

))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNSFROM [Adventure Works]

WHERE ([Geography].[State-Province].x,[Date].[Calendar].[Calendar Quarter].&[2003]&[4],

[Measures].[Declining Reseller Sales])

PropertiesReturns a string, or a strongly-typed value, that contains a member property value.

SyntaxMember_Expression.Properties(Property_Name [, TYPED])

Arguments

Member_Expression


Property_NameA valid string expression of a member property name.

Remarks

The Propertiesfunction returns the value of the specified member for the specified

member property. The member property can be any of the intrinsic member properties,such as NAME, ID, KEY, or CAPTION, or it can be a user-defined member property. For

more information, seeIntrinsic Member Properties (MDX)andUser-Defined MemberProperties (MDX).

By default, the value is coerced to be a string. If TYPEDis specified, the return value isstrongly typed.

If the property type is intrinsic, the function returns the original type of the member.

If the property type is user defined, the type of the return value is the same as the

type of the return value of theMemberValuefunction.

Note

Properties ('Key') returns the same result as Key0 except for composite keys. Properties ('Key')will return null for composite keys. Use the Keyxsyntax for composite keys, as illustrated in the

mdx functions.docx

Documents