| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3Member"
m (Moved link to XL3Lookup to the first time it's mentioned.) |
(→Using XL3Member in other Formulae) |
||
| (6 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Returns a name or caption for a member or members. Multiple members can be specified (as separate parameters) to allow a multi-member set to be used. | Returns a name or caption for a member or members. Multiple members can be specified (as separate parameters) to allow a multi-member set to be used. | ||
| − | Use the {{Menu|Insert Formula|Members}} menu or ribbon item to insert the formula using a wizard. | + | Use the {{Menu|Insert Formula|Members}} menu or ribbon item to insert the formula using a wizard. See [[Insert Members|here]] for details. |
==Syntax== | ==Syntax== | ||
| Line 43: | Line 43: | ||
{{Code|1==XL3Member( 1, "[Time]", "MDX:[Time].[All].[2005].LastChild" )}} | {{Code|1==XL3Member( 1, "[Time]", "MDX:[Time].[All].[2005].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 {{Code|Allround}} and {{Code|Road}}'' | ||
| + | |||
| + | {{Code|1==XL3Lookup( 1, "[Product]", XL3Member( 1, "[Product]", "[Product].[All].&[Allround]", "[Product].[All].&[Road]" ) )}} | ||
| + | |||
| + | ''Sharing an XL3Member between multiple lookups: the members between {{Code|Q2 2003}} and {{Code|Q1 2004}} (inclusive)'' | ||
| + | |||
| + | * ''In {{Code|A1}}:'' | ||
| + | *: {{Code|1==XL3Member( 1, "[Time]", "MDX:[Time].[All].&[2003].&[Q2]:[Time].[All].&[2004].&[Q1]" )}} | ||
| + | * ''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== | ||
| + | * [[Formula Reference]] | ||
| + | * [[Insert Members|Insert Members wizard]] | ||
| + | |||
| + | [[Category:Formulae]] | ||
| + | [[Category:OLAP Formulae]] | ||
Latest revision as of 13:00, 19 November 2010
Returns a name or caption for a member or members. Multiple members can be specified (as separate parameters) to allow a multi-member set to be used.
Use the Insert Formula > Members menu or ribbon item to insert the formula using a wizard. See here for details.
Contents
[hide]Syntax
XL3Member( Connection, Hierarchy, Member1, [Member2],…,[MemberN] )
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]" |
| Member1,…, MemberN | Members to use |
Examples (based on the Bicycle Sales cube)
Member name 'W6000/185'
=XL3Member( 1, "Product", "[Product].&[W6000/185]" )
Multiple Members
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].[2005].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].&[2003].&[Q2]:[Time].[All].&[2004].&[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 ) )
