Database Reference
In-Depth Information
member. Then create a member of a Dynamic hierarchy with a member formula called
[headcount] with the following formula:
IIF(IsLeaf([Time].CurrentMember), [Measures].[Load_Headcount],
(ClosingPeriod ([Time].Levels(0), [Time].CurrentMember),
[Measures].[Load_Headcount]))
here we are saying if the member is level-0, go ahead and grab the level-0 member
value of the load member, but if it is a nonlevel-0 member, then using the ClosingPeriod()
function find the value of the last level-0 descendant of the member being queried. you
could just as easily use the openingPeriod() function if your need is to get the starting
balance.
one complication to this is when users want to skip missing values. I'm not a big fan
of this, even when it was easy in BSo, but there are certainly use cases for it. This can be
accomplished using mDx, but admittedly it is a bit more complicated.
IIF(IsLeaf([Time].CurrentMember), [Measures].[Load_Headcount],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [Measures].[Load_
Headcount]) > 0,
([Measures].[Load_Headcount], Tail(Filter(DESCENDANTS([Time].
CurrentMember,10,LEAVES),
Not IsEmpty ([Measures].[Load_Headcount]))).Item(0).Item(0)),
MISSING))
here we are using some conditional logic to seek out the last member with a value
and skip over the missing cells. First we evaluate if the current member of time dimen-
sion is level-0. If it is, then we just return the loaded value. If it is a nonleaf member (leaf
is another way of saying level-0), then we first do an evaluation by doing a nonempty
count of the children. This tells us if there is a value to be found. If there is, then we use
a filter function to find the nonempty members of the set and use the tail() function to
grab the last one.
Another approach to this is to go back to our method of using a view dimension. to
accomplish this, we use uDAs, which as mentioned earlier are exposed to mDx through
custom properties. very simply, you choose the members in your outline that need to be
time Balanced and you add a uDA like 'tB_LASt'.
using the same example with [headCount], you can add the following formula to
your existing [view].[ytD] member:
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(DESCENDANTS([Time].
CurrentMember,10,LEAVES),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(2), [Time].
CurrentMember), [View].[Per]) END
Similar to what we did above, we are still seeking out the last nonempty member, but
this time we are using the IsuDA() function to determine to which members the logic
should be applied. Also note in the ELSE clause that we are inserting our ytD logic.
hence, now we have logic for our ytD member that crosses all members, knows which
