| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3Set"
(Created page with "Returns a name that can be used in place of a set in other formulae. Multiple members can be specified in a range to form one set. This formula can be used with XL3Member...") |
(→Using with XL3MemberSet) |
||
| (3 intermediate revisions by one other user not shown) | |||
| Line 29: | Line 29: | ||
====Using with XL3Member==== | ====Using with XL3Member==== | ||
| − | If you create a set with XL3Set you can use it with XL3Member to retrieve cube values. In the example below we can a set of members in column {{Code|C}} which control a formula report. | + | If you create a set with XL3Set you can use it with [[XL3Member]] to retrieve cube values. In the example below we can a set of members in column {{Code|C}} which control a formula report. |
| − | The set formula in {{Code|A1}} is {{Code|XL3Set(1, "[Geography].[Geography]", "my countries", C1:C10)}}, and the member formula in {{Code|G6}} is {{Code| | + | The set formula in {{Code|A1}} is {{Code|XL3Set(1, "[Geography].[Geography]", "my countries", C1:C10)}}, and the member formula in {{Code|G6}} is {{Code|XL3Member(1,"[Geography].[Geography]",A1)}}. |
[[File:xl3set1.png|thumb|center|500px]] | [[File:xl3set1.png|thumb|center|500px]] | ||
| + | |||
| + | ====Using with XL3MemberSet==== | ||
| + | |||
| + | [[XL3MemberSet]] creates a statement that can be used with the ranking functions [[XL3RankLookup]] and [[XL3ValueRankLookup]]. This statement controls the members being ranked. | ||
| + | |||
| + | With XL3Set you can rank custom lists of members as seen below. We can update the country list and the ranking updates automatically. | ||
| + | |||
| + | The XL3Set formula is the same as in the previous example. | ||
| + | |||
| + | The XL3MemberSet in {{Code|F1}} is {{Code|XL3MemberSet(1, "[Geography].[Geography]", A1, "DescendantsAt", 2)}} which gets the cities in the selected countries. Finally the rank formulae reference this XL3MemberSet cell, for example in {{Code|G6}} the formula is {{Code|XL3RankLookup(1,$F6,6,10,"[Measures].[Reseller Sales Amount]","[Geography].[Geography]",$F$1)}}. | ||
| + | |||
| + | [[File:xl3set2.png|thumb|center|500px]] | ||
==See Also== | ==See Also== | ||
Latest revision as of 17:52, 23 November 2017
Returns a name that can be used in place of a set in other formulae. Multiple members can be specified in a range to form one set.
This formula can be used with XL3Member and XL3MemberSet to drive reports.
Contents
[hide]Syntax
XL3Set( Connection, Hierarchy, Name, 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]" |
| Name | Name of the set to return |
| Member1,…, MemberN | Members to use |
Examples
Using with XL3Member
If you create a set with XL3Set you can use it with XL3Member to retrieve cube values. In the example below we can a set of members in column C which control a formula report.
The set formula in A1 is XL3Set(1, "[Geography].[Geography]", "my countries", C1:C10), and the member formula in G6 is XL3Member(1,"[Geography].[Geography]",A1).
Using with XL3MemberSet
XL3MemberSet creates a statement that can be used with the ranking functions XL3RankLookup and XL3ValueRankLookup. This statement controls the members being ranked.
With XL3Set you can rank custom lists of members as seen below. We can update the country list and the ranking updates automatically.
The XL3Set formula is the same as in the previous example.
The XL3MemberSet in F1 is XL3MemberSet(1, "[Geography].[Geography]", A1, "DescendantsAt", 2) which gets the cities in the selected countries. Finally the rank formulae reference this XL3MemberSet cell, for example in G6 the formula is XL3RankLookup(1,$F6,6,10,"[Measures].[Reseller Sales Amount]","[Geography].[Geography]",$F$1).
