Returns a member based on its relationship to the member passed in.
Use the Insert Formula > Member Navigate menu or ribbon item to insert the formula using a wizard.
XL3MemberNavigate( Connection, Hierarchy, Member or Level, Relationship, [Index] )
|Connection||Connection number to use|
|Hierarchy||Name of the hierarchy that the member applies to e.g. "Measures" or "[Customer].[Customer Geography]"|
|Member or Level||Members or Level to use as starting position|
|Relationship||Relationship of the desired member to the Member or Level passed in, see below|
|Index||Index of the member in the Relationship, meaning depends on the Relationship|
Examples (based on the Bicycle Sales cube)
Member name 'W6000/185'
=XL3Member( 1, "Product", "[Product].&[W6000/185]" )
You can specify multiple members for an XL3Member formula by using additional parameters.
When referred to by an XL3Lookup formula, it returns the sum of Allround and Mountain Bikes.
=XL3Member( 1, "Product", "[Product].&[Allround]", "[Product].&[Mountain]" )
XL3Member using MDX
You can specify an MDX calculation for members using the MDX: syntax. These cells can then be referenced by XL3Lookup formulae to use the created calculated members.
The last month in 2005.
=XL3Member( 1, "[Time]", "MDX:[Time].[All]..LastChild" )
Using XL3Member in other Formulae
To allow other formulae to use multiple members for a single hierarchy, it is necessary to use XL3Member to create a calculated member. You can reference the XL3Member directly in the other formula, or reference the cell that the XL3Member is in.
Using XL3Member in-line to perform a multiple member XL3Lookup: the sum of Allround and Road
=XL3Lookup( 1, "[Product]", XL3Member( 1, "[Product]", "[Product].[All].&[Allround]", "[Product].[All].&[Road]" ) )
Sharing an XL3Member between multiple lookups: the members between Q2 2003 and Q1 2004 (inclusive)
- In A1:
- =XL3Member( 1, "[Time]", "MDX:[Time].[All].&.&[Q2]:[Time].[All].&.&[Q1]" )
- In A4 (returns the value of the sum):
- =XL3Lookup( 1, "[Time]", A1 )
- In A5 (returns a chart of products, for that timespan):
- =XL3SparkColumnsM( XL3DataSeriesLookup( 1, XL3MemberSet( 1, "[Product]", "[Product].[All]", "Children" ), "[Measures].[Value]", "[Time]", A1 ) )