get level zero member from a set of UDA parents

get level zero member from a set of UDA parents

KCC
KCC

March 24th, 2006, 4:22 am #1

Hi all Essbase guru,

I have an UDA associated with some parent members, is there any way I can select the level zero members from this UDA parent??? This UDA will be different or change every 3 months. But I can't get the calculate script to validate; Am I able to do this? or I need to think of a different way?? Any suggestions???

For example; Product1 = @Relative(@UDA(Products,Prod_Parents),0);

Thanks in advance!!!
Quote
Share

David A
David A

March 27th, 2006, 6:39 am #2

Hi

One possible way to do this would be to use the API calls EsbBeginReport, EsbSendString EsbBeginCalc etc, calls such that you run a report script to get all the members that have the UDA and parse the results into an array. Then loop though all the values in the Array parsing each variable into to a calc script with the @descendants command (assumption is that the UDA is at level 1). This will set off one calc for each Product with the specified UDA - I know not the most elegant solution but it will work.

I hope this makes sense.

David
Quote
Share

James
James

March 27th, 2006, 3:28 pm #3

Hi all Essbase guru,

I have an UDA associated with some parent members, is there any way I can select the level zero members from this UDA parent??? This UDA will be different or change every 3 months. But I can't get the calculate script to validate; Am I able to do this? or I need to think of a different way?? Any suggestions???

For example; Product1 = @Relative(@UDA(Products,Prod_Parents),0);

Thanks in advance!!!
If the right-hand side of your equation returns more then one member, how will essbase know which value to use? You might wrap what you have in sum function, but I'm not certain this will be the number you want. You may need some IF statements (isuda, isparent, isdesc) to get value you want.

Product1 = @sum(@Relative(@UDA(Products,Prod_Parents),0));

Quote
Share

KCC
KCC

March 28th, 2006, 3:19 am #4

What about if i want to use it in the FIX statement, like

FIX ( @RELATIVE ( @UDA ("Product",Prodparent) , 0)

Can I do this???

Thanks!!!
Quote
Share

Chris Cameron
Chris Cameron

March 28th, 2006, 4:19 am #5

No. Unfortunately @UDA() return a LIST of items, but @RELATIVE() expects a SINGLE MEMBER.

What is it you want to do? Do you want to do something

*TO* the Lev 0 members below all the @UDA ("Product",Prodparent) members

OR

*WITH* the Lev 0 members below all the @UDA ("Product",Prodparent) members
?

If with then perhaps something along the lines of

FIX ( @UDA ("Product",Prodparent) )
X = @SUMRANGE( Y , @RELATIVE( @CURRMBR( "Product" ), 0 ) )
ENDFIX

Also, will @UDA ("Product",Prodparent) always return a single member
Quote
Share

KCC
KCC

March 28th, 2006, 3:56 pm #6

I would like to get "*TO* the Lev 0 members below all the @UDA ("Product",Prodparent) members" in the Fix statment and use the level 0 member to multiply a percentage. Since the UDA will be changing every month, therefore, I can't use a static member in the calc script. Any suggestions???

Thanks!!!!
Quote
Share

Chris Cameron
Chris Cameron

March 28th, 2006, 10:35 pm #7

Any chance you can use some external process to move the UDA to the children?
Quote
Share

Anonymous
Anonymous

March 28th, 2006, 11:35 pm #8

Hi

One possible way to do this would be to use the API calls EsbBeginReport, EsbSendString EsbBeginCalc etc, calls such that you run a report script to get all the members that have the UDA and parse the results into an array. Then loop though all the values in the Array parsing each variable into to a calc script with the @descendants command (assumption is that the UDA is at level 1). This will set off one calc for each Product with the specified UDA - I know not the most elegant solution but it will work.

I hope this makes sense.

David
Hi

I though I would provide an example of what I meant - assuming you have the access to the EssbAPIProcedures here is a portion of code that will enable you to do what you want.

Declare variables and initialise first then.

pOutput = ESB_YES
pLock = ESB_NO

phCtx = EssVGetHctxFromSheet("[Book.xls]Sheet")

sts = EsbBeginReport(phCtx, pOutput, pLock)

sts = EsbSendString(phCtx,"{SUPFEED}")
sts = EsbSendString(phCtx,"{SUPPAGEHEADING }")
sts = EsbSendString(phCtx,"{FIXCOLUMNS 1}")
sts = EsbSendString(phCtx,"<ROW(Products)")
sts = EsbSendString(phCtx,"<UDA (Products,Prod_Parents)")
sts = EsbSendString(phCtx, "!")

sts = EsbEndReport(phCtx)
x = 1
If sts = 0 Then
sts = EsbGetString(phCtx, RString, szRString)
Do While Mid$(RString, 1, 1) <> Chr$(0)
Array1(x) = RString
x = x + 1
sts = EsbGetString(phCtx, RString, szRString)
Loop
End If

Do Until Array1(x) = ""
sts = EsbGetProcessState(phCtx, ProcState)
If ProcState.State = ESB_STATE_DONE Then
sts = EsbBeginCalc(phCtx, Calculate)
sts = EsbSendString(phCtx, " FIX (""@RELATIVE(" & Trim(Array1(x)) & ")")
sts = EsbSendString(phCtx, "))")
sts = EsbSendString(phCtx, "x = Whatever you want;")
sts = EsbSendString(phCtx, "ENDFIX)
sts = EsbEndCalc(phCtx)
x = x + 1
End If
Loop

Or is there a reason you can not do this?

David
Quote
Share

Joined: December 22nd, 2000, 2:10 pm

March 29th, 2006, 3:06 am #9

I would like to get "*TO* the Lev 0 members below all the @UDA ("Product",Prodparent) members" in the Fix statment and use the level 0 member to multiply a percentage. Since the UDA will be changing every month, therefore, I can't use a static member in the calc script. Any suggestions???

Thanks!!!!
How is the UDA set? Is this an automated or manual process? Either way, you might want to consider using a Substition Variable either instead or along with the UDA.

Then you would be able to use @RELATIVE( &CurMonth, 0) in your calculation script to reach the Level 0 members below the member.

- Jay

---------------------------------------
Jay Nish
Senior Essbase Consultant
Clarity Systems
jnish@claritysystems.com
www.claritysystems.com
Quote
Like
Share

Javier
Javier

March 29th, 2006, 8:12 am #10

I would like to get "*TO* the Lev 0 members below all the @UDA ("Product",Prodparent) members" in the Fix statment and use the level 0 member to multiply a percentage. Since the UDA will be changing every month, therefore, I can't use a static member in the calc script. Any suggestions???

Thanks!!!!
How about FIX-ing on all level 0 products and then calculating your measure using the following conditional test?

if (@count (SKIPNONE, (@ancestors (@currmbr (Products)) and @uda (Products, udaName))) > 0)
   ... do something ...
endif

The conditional statement will return false if no ancestor of the level 0 member has the specified UDA.
Quote
Share


Confirmation of reply: