Forum Discussion

dbarkhol's avatar
dbarkhol
New Contributor
3 years ago

Convert Essbase @SUMRANGE

Hi OS gurus. Hoping to get a little help on converting a Hyperion line of code
that is using SUMRANGE to OneStream.

Here is the Essbase line I am trying to convert:

IF(@ISMBR("Assoc Ex Elim"))
@SUMRANGE("Cash - Asset",@CHILDREN("Assoc Ex Elim"));
ENDIF

This piece of code is going to reside in a Flow member that has already been setup as a DynamicCalcTextInput member. It already has code
where it is calculating account "A29970 - Assoc Ex Elim" based on if a Cash account is positive or negative. It works good but is doing it for all UD1 members, we want UD1 member named
"Assoc Ex Elim" to not use this Cash account formula but rather just consolidate what it's children have. This is what the Hyperion code is doing.

I have tried the following but no data comes back for it:

If api.Pov.Account.Name = "A29970"
Dim UD1NAME As String = api.Pov.UD1.Name
   If api.Pov.UD1.Name = "Assoc Ex Elim"
    api.Data.Calculate("F#ENDBALCALC=F#ENDBALCALC","A#A29970",,,,"UD1#[Assoc Ex Elim].Children",,,,,,,,,,)
   End If
End If

Am I on the right path but maybe have this api not correct or is this the wrong approach and maybe need to do a loop to add up the children data?

Thanks so much for any help.

Finance
  • ckattookaran's avatar
    ckattookaran
    VIP
    3 years ago

    You will have to use an Eval buffer to do this. Inside the eval buffer, you can check for the UD and perform a cumulative sum.

    • dbarkhol's avatar
      dbarkhol
      New Contributor
      3 years ago

      Thanks Calvin. Would this Eval in a buffer reside in a business rule and then we would
      call this BR within our dynamic flow member within the rest of code?

      Most examples I have seen, including in your OS Planning book page 67, seem
      to show this CalculateWithEval in a BR. I haven't really seen this in a dynamic Flow or Account member so this is why I ask.

      Thanks again.

      • ckattookaran's avatar
        ckattookaran
        VIP
        3 years ago

        You can use EvalBuffer on a member formula as well. Yes, we took an approach of not using member formulas in Planning as most of the Planning solutions are like that. Search for OnEvalDataBuffer and there is one example of that in the documentation.