Database Reference
In-Depth Information
Figure 6.2
The result of a properly scoped Tuple.
will result in the first formula working correctly, even though it is using the implied
[Scenario] member rather than the explicit [Actual] member.
It is a better practice to scope the formulas properly using a more fully defined tuple
rather than relying on default behaviors. relying on default behaviors can result in
formulas returning an incorrect value in the future when an outline change may hap-
pen. In the example provided, assume six months from now someone decides to add a
[Budget] member to the outline and sets it as the first child of [Scenario]. If the formula
for [What If] is ([Sales]) * 1.1, the results for [What If] will now be based on [Budget]
rather than [Actual]. This behavior should not be overlooked. once you understand this
concept, you will see how it can apply later to much more complex formulas.
6.4.1.5 Sets
In its simplest form, a Set is a collection of tuples. Similar to the rules for
a tuple, a Set can be made of one tuple, and since a tuple can consist of one member,
a Set can simply be a single member. most Sets contain more than one member/tuple.
however, it should be noted that a Set can be empty. I will demonstrate the use of empty
Sets in a query later in the chapter. A common example of a Set is a member range.
A member range can be defined using a function like memberrange([Jan], [Dec]) or
an alternate syntax like [Jan]
:
[Dec]. other common Set functions are Children(),
Descendants(), and members().
Sets have some basic rules. If you have multiple tuples in a Set, they must have the
same dimensional cardinality and they must adhere to the same dimensional order.
{([Actual], [Jan]), ([Budget], [Feb])} is a valid set.
{([Actual], [Jan]), ([Feb], [Budget])} and {([Jan]), ([Feb], [Budget])} are invalid sets
because the first set has a mixed dimension order and the second set has unbalanced
cardinality.
note that unless the set is being returned by a function, it must be enclosed in curly
braces { }. Additionally, a Set also can be made up of other Sets.
6.4.1.6 Member Specification and Fully Qualified Member Names
A member name can
be specified in a few different ways. The most common method is to solely use the mem-
ber name (or alias) by itself. A member name only requires brackets [ ] if the name
contains spaces. For BSo developers, these are the same rules that apply with double
quotation marks in the calc script language. however, as a best practice for better read-
ability, I suggest always putting members in brackets. For further clarity, a member also
can be defined not only by its name but by identifying its dimension or any one ancestor
member name and attaching it to the member name as a prefix. While it is acceptable to
use a member name alone like [Jan], [Period].[Jan] or [Qtr1].[Jan] provides more clarity.
This is particularly useful when scoping shared members from an alternate hierarchy.
