Difference between revisions of "XL3GridMember"
(13 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
− | Returns the member uniquename for the specified grid cell | + | Returns the member uniquename for the specified grid cell. This can be useful for when you want to get the unique name of a particular member from a grid for passing as an [[XL3Link]] parameter or to use in other formulae (VLOOKUP, [[XL3Lookup]] etc.) - avoiding any issues with duplicate/translated captions etc. |
==Syntax== | ==Syntax== | ||
Line 6: | Line 6: | ||
==Parameters== | ==Parameters== | ||
+ | The parameters can take one of two forms: | ||
+ | |||
{| class="wikitable" | {| class="wikitable" | ||
! Parameter | ! Parameter | ||
Line 13: | Line 15: | ||
|Shows member uniquename for grid cell | |Shows member uniquename for grid cell | ||
|} | |} | ||
+ | |||
+ | or | ||
+ | |||
+ | {| class="wikitable" | ||
+ | ! Parameter | ||
+ | ! Description | ||
+ | |- | ||
+ | | {{Code|Grid name}} | ||
+ | |Name of the grid to get the member for | ||
+ | |- | ||
+ | | {{Code|Axis}} | ||
+ | |1 for Columns, 2 for Rows | ||
+ | |- | ||
+ | | {{Code|Dimension}} | ||
+ | |Hierarchy on the axis to look at, 1-based. | ||
+ | |- | ||
+ | | {{Code|Member Index}} | ||
+ | |Member index to look at, 1-based. | ||
+ | |} | ||
+ | |||
+ | ==Examples== | ||
+ | |||
+ | ===Form 1=== | ||
+ | |||
+ | Used in conjunction with [[XL3Link]], the following formula copies the unique name from the row when the report user clicks on "..." into the cell $A$14: | ||
+ | |||
+ | =XL3Link(XL3Address($D$7),"...",,XL3Address($A$14),XL3GridMember(A7)) | ||
+ | |||
+ | [[Image:XL3GridMember1.png]] | ||
+ | |||
+ | ===Form 2=== | ||
+ | |||
+ | The second structure provides a more dynamic way to address the members that appear on the grid, for example: | ||
+ | |||
+ | |||
+ | Return the first member from the first hierarchy on columns: | ||
+ | |||
+ | =XL3GridMember("My Grid",1,1,1) | ||
+ | |||
+ | In the above example this is: [Geography].[Geography].[All Geographies] | ||
+ | |||
+ | |||
+ | Return the nth member from the first hierarchy on rows: | ||
+ | |||
+ | =XL3GridMember("My Grid",2,1,nth) | ||
+ | |||
+ | You would only need to consider the hierarchy number if the Axis has cross-joined hierarchies. | ||
+ | |||
+ | ===Limitations=== | ||
+ | An individual cell can only include one reference to an XL3GridMember(Address), from V9.1.47 you can use XL3Address for more then one reference: | ||
+ | |||
+ | =XL3GridMember(XL3Address(C13)) & " - " & XL3GridMember(XL3Address(D13)) | ||
==See Also== | ==See Also== |
Latest revision as of 15:06, 4 April 2018
Returns the member uniquename for the specified grid cell. This can be useful for when you want to get the unique name of a particular member from a grid for passing as an XL3Link parameter or to use in other formulae (VLOOKUP, XL3Lookup etc.) - avoiding any issues with duplicate/translated captions etc.
Syntax
XL3GridMember( )
Parameters
The parameters can take one of two forms:
Parameter | Description |
---|---|
Grid cell location | Shows member uniquename for grid cell |
or
Parameter | Description |
---|---|
Grid name | Name of the grid to get the member for |
Axis | 1 for Columns, 2 for Rows |
Dimension | Hierarchy on the axis to look at, 1-based. |
Member Index | Member index to look at, 1-based. |
Examples
Form 1
Used in conjunction with XL3Link, the following formula copies the unique name from the row when the report user clicks on "..." into the cell $A$14:
=XL3Link(XL3Address($D$7),"...",,XL3Address($A$14),XL3GridMember(A7))
Form 2
The second structure provides a more dynamic way to address the members that appear on the grid, for example:
Return the first member from the first hierarchy on columns:
=XL3GridMember("My Grid",1,1,1)
In the above example this is: [Geography].[Geography].[All Geographies]
Return the nth member from the first hierarchy on rows:
=XL3GridMember("My Grid",2,1,nth)
You would only need to consider the hierarchy number if the Axis has cross-joined hierarchies.
Limitations
An individual cell can only include one reference to an XL3GridMember(Address), from V9.1.47 you can use XL3Address for more then one reference:
=XL3GridMember(XL3Address(C13)) & " - " & XL3GridMember(XL3Address(D13))