jeudi 25 octobre 2012

FIX(@RELATIVE(@UDA("Market", "Major Market"), 0))

I'm sure that my article title cannot be more explicit! Indeed have you ever tried to reach level 0 members from a member list? How many people hoped doing so using the poor LOOP command available in Essbase library?

Well this was not possible at least using the good old RELATIVE calc function :

FIX(@RELATIVE(@UDA("Market", "Major Market"), 0))


A quick look at Essbase techref (11.1.1.3/11.1.1.4 in my case) and I found this calc function which will do the trick :

@LDESCENDANTS(@UDA("Market", "Major Market"), 0)

Here an extract from techref regarding @LDESCENDANTS calculation function :

Returns all descendants of the members in the specified member list or all descendents down to the specified generation or level. This function excludes the specified members.
You can use the @LDESCENDANTS function as a parameter of another function, where the function requires a list of members.

This function was made available with Essbase version 11.1.1.


Thanks to Cameron Lackpour for putting me "the flea in the ear", indeed and originally I was looking for a way to loop accros a list of members.