Difference between revisions of "XL3MemberNavigate"
(>30 parameters allowed)
 
(20 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
Returns a member based on its relationship to the member passed in. Allows you to navigate a hierarchy, moving between members and levels using formulae alone.
 
Returns a member based on its relationship to the member passed in. Allows you to navigate a hierarchy, moving between members and levels using formulae alone.
 +
 +
You can combine formulae, for example to move up a hierarchy, then along the level you get to.
  
 
Use the {{Menu|Insert Formula|Member Navigate}} menu or ribbon item to insert the formula using a wizard.  
 
Use the {{Menu|Insert Formula|Member Navigate}} menu or ribbon item to insert the formula using a wizard.  
Line 5: Line 7:
 
==Syntax==
 
==Syntax==
  
−
{{Code|XL3MemberNavigate( Connection, Hierarchy, Member or Level, Relationship, [Index] )}}
+
{{Code|XL3MemberNavigate( Connection, Hierarchy, Member or Level, Relationship, [Index], [Hierarchy1], [Member1],…, [Hierarchy100], [Member100] )}}
  
 
==Parameters==
 
==Parameters==
Line 26: Line 28:
 
| {{Code|Index}}
 
| {{Code|Index}}
 
| Index of the member in the Relationship, meaning depends on the Relationship
 
| Index of the member in the Relationship, meaning depends on the Relationship
 +
{{Standard_HierarchyMember_List| in order to specify that [[#Non-Empty behaviour|only non-empty members should be returned]]}}
 
|}
 
|}
  
Line 39: Line 42:
 
| {{Code|FirstMember}}
 
| {{Code|FirstMember}}
 
| {{Code|Level}}
 
| {{Code|Level}}
−
| By default returns the first member of the specified level. If the Index parameter is passed it gets that member of the level.
+
| By default returns the first member of the specified level. If the Index parameter is passed as 'x' it gets the x<sup>th</sup> member of the level.
 
|-
 
|-
 
| {{Code|LastMember}}
 
| {{Code|LastMember}}
 
| {{Code|Level}}
 
| {{Code|Level}}
−
| By default returns the last member of the specified level. If the Index parameter is passed it gets the member that distance from the end of the level.
+
| By default returns the last member of the specified level. If the Index parameter is passed as 'x' it gets the x<sup>th</sup> member from the end of the level.
 
|-
 
|-
 
| {{Code|Members}}
 
| {{Code|Members}}
Line 51: Line 54:
 
| {{Code|Parent}}
 
| {{Code|Parent}}
 
| {{Code|Member}}
 
| {{Code|Member}}
−
| Gets the parent of the member passed in. If index is greater than one it gets the ancestor at that distance.
+
| Gets the parent of the member passed in. If index is 2, it will retreive the grandparent (parent of the parent), 3 the great-grandparent etc.
 +
|-
 +
| {{Code|ParentAtLevel}}
 +
| {{Code|Member}}
 +
| Gets the parent or ancestor of the member passed in. If the index parameter is passed this is the level number the parent must come from. Level number is one-based. (new in Version 8)
 
|-
 
|-
 
| {{Code|Next}}
 
| {{Code|Next}}
 
| {{Code|Member}}
 
| {{Code|Member}}
−
| Gets the next member following the member passed in. If index is greater than one then it skips forwards that distance.
+
| Gets the next member following the member passed in. If index is greater than one then it skips forwards that number of members.
 
|-
 
|-
 
| {{Code|Previous}}
 
| {{Code|Previous}}
 
| {{Code|Member}}
 
| {{Code|Member}}
−
| Gets the previous member preceding the member passed in. If index is greater than one then it skips backwards that distance.
+
| Gets the previous member preceding the member passed in. If index is greater than one then it skips backwards that number of members.
 
|-
 
|-
 
| {{Code|FirstSibling}}
 
| {{Code|FirstSibling}}
Line 67: Line 74:
 
| {{Code|LastSibling}}
 
| {{Code|LastSibling}}
 
| {{Code|Member}}
 
| {{Code|Member}}
−
| Gets the last member with the same parent as the member passed in. If index is greater than one then it gets the child at that index from the end of the children.
+
| Gets the last member with the same parent as the member passed in. If index is greater than one then it gets the child at that index from the last sibling.
 
|-
 
|-
 
| {{Code|Siblings}}
 
| {{Code|Siblings}}
Line 79: Line 86:
 
| {{Code|LastChild}}
 
| {{Code|LastChild}}
 
| {{Code|Member}}
 
| {{Code|Member}}
−
| Gets the last child of the member passed in. If index is greater than one then it gets the child at that index from the end of the children.
+
| Gets the last child of the member passed in. If index is greater than one then it gets the child at that index from the last child.
 
|-
 
|-
 
| {{Code|Children}}
 
| {{Code|Children}}
 
| {{Code|Member}}
 
| {{Code|Member}}
 
| Equivalent to FirstChild or LastChild. If index is positive it acts like FirstChild, if it is negative it acts like LastChild.
 
| Equivalent to FirstChild or LastChild. If index is positive it acts like FirstChild, if it is negative it acts like LastChild.
 +
|-
 +
| {{Code|FirstDescendantAtLevel}}
 +
| {{Code|Member}}
 +
| Gets the first descendant of the member at a level specified by index. Index is one based. (New in V7.1)
 +
|-
 +
| {{Code|LastDescendantAtLevel}}
 +
| {{Code|Member}}
 +
| Gets the last descendant of the member at a level specified by index. Index is one based. (New in V7.1)
 +
|-
 +
| {{Code|FirstDescendantAtDistance}}
 +
| {{Code|Member}}
 +
| Gets the first descendant of the member at a distance specified by index. An index of one is equivalent of children. (New in V7.1)
 +
|-
 +
| {{Code|LastDescendantAtDistance}}
 +
| {{Code|Member}}
 +
| Gets the last descendant of the member at a distance specified by index. An index of one is equivalent of children. (New in V7.1)
 +
|-
 +
| {{Code|DefaultMember}}
 +
| {{Code|Any}}
 +
| Gets the default member for the hierarchy. (New in [[Version 7.6]])
 +
|-
 +
| {{Code|Item}}
 +
| {{Code|Level}}
 +
| If the Index parameter is passed as 'x' it gets the x<sup>th</sup> member of the level. Useful as it also supports sets using the [[Mdx:|MdxSet:]] syntax (New in [[Version 8.1]])
 
|}
 
|}
  
−
==Examples (based on the [[BicycleSales Demo Cube|Bicycle Sales cube]])==
+
==Non-Empty behaviour==
 +
 
 +
Sometimes you need to restrict the members by those with data. To do this you can specify hierarchy/member pairs for which data must exist.
 +
 
 +
For example you may want the last date where data exists. To do this you could add a measure to a XL3MemberNavigate on the LastMember of a level.
  
−
''Member name 'W6000/185'''
+
For example:
  
−
{{Code|1==XL3Member( 1, "Product", "[Product].&[W6000/185]" )}}
+
{{Code|1==XL3MemberNavigate(1,"[Date].[Calendar]","[Date].[Calendar].[Date]","LastMember")}}
 +
*August 31, 2004
  
−
===Multiple Members===
+
{{Code|1==XL3MemberNavigate(1,"[Date].[Calendar]","[Date].[Calendar].[Date]","LastMember", 1, "[Measures]", "[Measures].[Reseller Sales Amount]")}}
 +
*June 1, 2004
  
−
You can specify multiple members for an {{Code|XL3Member}} formula by using additional parameters.
+
==Examples (based on the [[BicycleSales Demo Cube|Bicycle Sales cube]])==
  
−
''When referred to by an {{Code|[[XL3Lookup]]}} formula, it returns the sum of Allround and Mountain Bikes.''
+
===Level Relationships===
 +
''These examples assume that [Time].[Year] is in cell A1''
  
−
{{Code|1==XL3Member( 1, "Product", "[Product].&[Allround]", "[Product].&[Mountain]" )}}
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstMember" )}}
 +
*2002
  
−
===XL3Member using MDX===
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastMember", 2 )}}
 +
*2004
  
−
You can specify an MDX calculation for members using the {{Code|MDX:}} syntax. These cells can then be referenced by {{Code|XL3Lookup}} formulae to use the created calculated members.
+
===Member Relationships===
 +
''These examples assume that [Time].[All].&[2003].&[Q2] is in cell A1''
  
−
''The last month in 2005.''
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Parent" )}}
 +
*2003
  
−
{{Code|1==XL3Member( 1, "[Time]", "MDX:[Time].[All].[2005].LastChild" )}}
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Parent", 2 )}}
 +
*All
  
−
===Using XL3Member in other Formulae===
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Next" )}}
 +
*Q3
  
−
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.
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "Previous", 2 )}}
 +
*Q4 (from 2002)
  
−
''Using XL3Member in-line to perform a multiple member XL3Lookup: the sum of {{Code|Allround}} and {{Code|Road}}''
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstSibling" )}}
 +
*Q1
  
−
{{Code|1==XL3Lookup( 1, "[Product]", XL3Member( 1, "[Product]", "[Product].[All].&[Allround]", "[Product].[All].&[Road]" ) )}}
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastSibling", 2 )}}
 +
*Q3
  
−
''Sharing an XL3Member between multiple lookups: the members between {{Code|Q2 2003}} and {{Code|Q1 2004}} (inclusive)''
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "FirstChild" )}}
 +
*April 2003
  
−
* ''In {{Code|A1}}:''
+
{{Code|1==XL3MemberNavigate( 1, "Time", A1, "LastChild", 2 )}}
−
*: {{Code|1==XL3Member( 1, "[Time]", "MDX:[Time].[All].&[2003].&[Q2]:[Time].[All].&[2004].&[Q1]" )}}
+
*May 2003
−
* ''In {{Code|A4}} (returns the value of the sum):''
 
−
*: {{Code|1==XL3Lookup( 1, "[Time]", A1 )}}
 
−
* ''In {{Code|A5}} (returns a chart of products, for that timespan):''
 
−
*: {{Code|1==XL3SparkColumnsM( XL3DataSeriesLookup( 1, XL3MemberSet( 1, "[Product]", "[Product].[All]", "Children" ), "[Measures].[Value]", "[Time]", A1 ) )}}
 
  
 
==See Also==
 
==See Also==

Latest revision as of 13:59, 28 April 2016

Returns a member based on its relationship to the member passed in. Allows you to navigate a hierarchy, moving between members and levels using formulae alone.

You can combine formulae, for example to move up a hierarchy, then along the level you get to.

Use the Insert Formula > Member Navigate menu or ribbon item to insert the formula using a wizard.

Syntax

XL3MemberNavigate( Connection, Hierarchy, Member or Level, Relationship, [Index], [Hierarchy1], [Member1],…, [Hierarchy100], [Member100] )

Parameters

Parameter Description
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
Hierarchy1,…, HierarchyN Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]"
Member1,…, MemberN Either a single member unique name or an XL3Member formula in order to specify that only non-empty members should be returned

Relationship types

The valid relationship types are as follows, and the meaning of the Index parameter is decribed for each.

Relationship Member or Level Description
FirstMember Level By default returns the first member of the specified level. If the Index parameter is passed as 'x' it gets the xth member of the level.
LastMember Level By default returns the last member of the specified level. If the Index parameter is passed as 'x' it gets the xth member from the end of the level.
Members Level Equivalent to FirstMember or LastMember. If index is positive it acts like FirstMember, if it is negative it acts like LastMember.
Parent Member Gets the parent of the member passed in. If index is 2, it will retreive the grandparent (parent of the parent), 3 the great-grandparent etc.
ParentAtLevel Member Gets the parent or ancestor of the member passed in. If the index parameter is passed this is the level number the parent must come from. Level number is one-based. (new in Version 8)
Next Member Gets the next member following the member passed in. If index is greater than one then it skips forwards that number of members.
Previous Member Gets the previous member preceding the member passed in. If index is greater than one then it skips backwards that number of members.
FirstSibling Member Gets the first member with the same parent as the member passed in. If index is greater than one then it gets the child at that index.
LastSibling Member Gets the last member with the same parent as the member passed in. If index is greater than one then it gets the child at that index from the last sibling.
Siblings Member Equivalent to FirstSibling or LastSibling. If index is positive it acts like FirstSibling, if it is negative it acts like LastSibling.
FirstChild Member Gets the first child of the member passed in. If index is greater than one then it gets the child as that index.
LastChild Member Gets the last child of the member passed in. If index is greater than one then it gets the child at that index from the last child.
Children Member Equivalent to FirstChild or LastChild. If index is positive it acts like FirstChild, if it is negative it acts like LastChild.
FirstDescendantAtLevel Member Gets the first descendant of the member at a level specified by index. Index is one based. (New in V7.1)
LastDescendantAtLevel Member Gets the last descendant of the member at a level specified by index. Index is one based. (New in V7.1)
FirstDescendantAtDistance Member Gets the first descendant of the member at a distance specified by index. An index of one is equivalent of children. (New in V7.1)
LastDescendantAtDistance Member Gets the last descendant of the member at a distance specified by index. An index of one is equivalent of children. (New in V7.1)
DefaultMember Any Gets the default member for the hierarchy. (New in Version 7.6)
Item Level If the Index parameter is passed as 'x' it gets the xth member of the level. Useful as it also supports sets using the MdxSet: syntax (New in Version 8.1)

Non-Empty behaviour

Sometimes you need to restrict the members by those with data. To do this you can specify hierarchy/member pairs for which data must exist.

For example you may want the last date where data exists. To do this you could add a measure to a XL3MemberNavigate on the LastMember of a level.

For example:

=XL3MemberNavigate(1,"[Date].[Calendar]","[Date].[Calendar].[Date]","LastMember")

  • August 31, 2004

=XL3MemberNavigate(1,"[Date].[Calendar]","[Date].[Calendar].[Date]","LastMember", 1, "[Measures]", "[Measures].[Reseller Sales Amount]")

  • June 1, 2004

Examples (based on the Bicycle Sales cube)

Level Relationships

These examples assume that [Time].[Year] is in cell A1

=XL3MemberNavigate( 1, "Time", A1, "FirstMember" )

  • 2002

=XL3MemberNavigate( 1, "Time", A1, "LastMember", 2 )

  • 2004

Member Relationships

These examples assume that [Time].[All].&[2003].&[Q2] is in cell A1

=XL3MemberNavigate( 1, "Time", A1, "Parent" )

  • 2003

=XL3MemberNavigate( 1, "Time", A1, "Parent", 2 )

  • All

=XL3MemberNavigate( 1, "Time", A1, "Next" )

  • Q3

=XL3MemberNavigate( 1, "Time", A1, "Previous", 2 )

  • Q4 (from 2002)

=XL3MemberNavigate( 1, "Time", A1, "FirstSibling" )

  • Q1

=XL3MemberNavigate( 1, "Time", A1, "LastSibling", 2 )

  • Q3

=XL3MemberNavigate( 1, "Time", A1, "FirstChild" )

  • April 2003

=XL3MemberNavigate( 1, "Time", A1, "LastChild", 2 )

  • May 2003

See Also

Retrieved from ‘https://help.fluencexl.com/index.php?title=XL3MemberNavigate&oldid=362’