| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3PropertyLookup"
(Using the Standard Hierarchy Parameter Description template) |
(→Parameters) |
||
| (5 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
Returns a member property of a given member. | Returns a member property of a given member. | ||
| − | Use the {{Menu|Insert Formula|Member Property}} menu or ribbon item to insert the formula using a wizard. | + | Use the {{Menu|Insert Formula|Member Property}} menu or ribbon item to insert the formula using a wizard. See [[Insert Member Property|here]] for details. |
==Syntax== | ==Syntax== | ||
| − | {{Code|XL3PropertyLookup( Connection, Hierarchy, Member, Property )}} | + | {{Code|XL3PropertyLookup( Connection, Hierarchy, Member, Property, [TryConvertType] )}} |
==Parameters== | ==Parameters== | ||
| Line 20: | Line 20: | ||
| {{Code|Member}} | | {{Code|Member}} | ||
| Member whose member property should be returned | | Member whose member property should be returned | ||
| + | |- | ||
| + | | {{Code|Property}} | ||
| + | | Property to retrieve | ||
| + | |- | ||
| + | | {{Code|TryConvertType}} | ||
| + | | Optional. A boolean value ({{TRUE}} or {{FALSE}}), try to turn text into a number or date on the web, defaults to true. | ||
|} | |} | ||
| Line 97: | Line 103: | ||
==See Also== | ==See Also== | ||
* [[Formula Reference]] | * [[Formula Reference]] | ||
| + | * [[Insert Member Property|Insert Member Property wizard]] | ||
| + | |||
| + | [[Category:Formulae]] | ||
| + | [[Category:OLAP Formulae]] | ||
Latest revision as of 09:15, 4 November 2022
Returns a member property of a given member.
Use the Insert Formula > Member Property menu or ribbon item to insert the formula using a wizard. See here for details.
Contents
[hide]Syntax
XL3PropertyLookup( Connection, Hierarchy, Member, Property, [TryConvertType] )
Parameters
| Parameter | Description |
|---|---|
| Connection | Connection number to use |
| Hierarchy | Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]" |
| Member | Member whose member property should be returned |
| Property | Property to retrieve |
| TryConvertType | Optional. A boolean value (TRUE or FALSE), try to turn text into a number or date on the web, defaults to true. |
Intrinsic Member Properties
XL3PropertyLookup supports the following Intrinsic Member Properties:
| Property name | Description |
|---|---|
| MEMBER_UNIQUE_NAME | The unique name of the member. For providers that generate unique names by qualification, each component of this name is delimited |
| MEMBER_CAPTION | A label or caption associated with the member. It is used primarily for display purposes. If a caption does not exist, MEMBER_NAME is returned |
| LEVEL_UNIQUE_NAME | Unique name of the level to which the member belongs. For providers that generate unique names by qualification, each component of this name is delimited |
| LEVEL_NUMBER | The distance of the member from the root of the hierarchy. The root level is zero (0) |
| CUBE_NAME | The name of the cube to which this member belongs |
| CATALOG_NAME | The name of the database to which this member belongs |
| CHILDREN_CARDINALITY | The number of children that the member has. This can be an estimate, so you should not rely on this to be the exact count. Providers should return the best estimate possible |
| MEMBER_ORDINAL | The ordinal number of the member. This is the sort rank of the member when members of this dimension are sorted in their natural sort order. If providers do not have the concept of natural ordering, this should be the rank when sorted by MEMBER_NAME |
| DIMENSION_UNIQUE_NAME | The unique name of the dimension to which this member belongs. For providers that generate unique names by qualification, each component of this name is delimited |
| HIERARCHY_UNIQUE_NAME | The unique name of the hierarchy. If the member belongs to more than one hierarchy, there is one row for each hierarchy to which it belongs. For providers that generate unique names by qualification, each component of this name is delimited |
| MEMBER_NAME | The name of the member |
| PARENT_LEVEL | The distance of the member's parent from the root level of the hierarchy. The root level is zero (0) |
| PARENT_UNIQUE_NAME | The unique name of the member's parent. NULL is returned for any members at the root level |
| PARENT_COUNT | The number of parents that this member has |
| MEMBER_KEY | The value of the member's key column. Returns NULL if the member has a composite key |
| IS_PLACEHOLDERMEMBER | A Boolean that indicates whether a member is a placeholder member for an empty position in a dimension hierarchy |
| IS_DATAMEMBER | A Boolean that indicates whether the member is a data member. Returns True if the member is a data member |
| MEMBER_TYPE | The type of the member. It can be one of the following values:
|
Examples (based on the Bicycle Sales cube)
Returns 3, the distance of 'W6000/185' from the root of the hierarchy.
=XL3PropertyLookup( 1, "[Product]", "[Product].[W6000/185]", "LEVEL_NUMBER" )
