How to use if statement to check calculation flag

[SimpleLove]

Dear OneStream Experts:

Thank you so much for the great help in the past.

I am converting one Hyperion Planning application to OneStream application and would like to know how to use if statement to check calculation flag. For example: I have 3 dimensions

Account dimension has the following 3 members

Sales

Rebate

Rebate_Flag

UD1 - Product Dimension has many Members

TOTAL_Product  Parent member with children(Product_1,Product_2,Product_3,,,,,,Product_N).

No_Product

UD2 - Country of Sale Dimension has one parent member ALL_Country and 2 children members

ALL_Country

  USA

  Canada

In Hyperion application, I have

Rebate_Flag->USA->No_Product =1

Rebate_Flag->Canada->No_Product =2

I want to calculate Rebate only when Rebate_Flag=1. In OneStream, I want to do

api.Data.Calculation("A#Rebate=A#Sales * 0.10", "UD1#Total_Product.base",true)

But how to I apply the if logic. In Hyperion, I can do

Fix(@Relative("All_Country",0),@Relative("TOTAL_Product",0))

If("Rebate_Flag"->"No_Product"=1) Then

    Rebate=Sales*0.10;

End If

But in OneStream, how can I do If("Rebate_Flag"->"No_Product"=1), cross dimension calculation flag check?

Your help is truly appreciated. Thank you in advance.

[FredLucas]

Hi @SimpleLove,

The first thing I'd like to mention is that depending on the use case OS has got a better way to configure this flag account type logic. An example would be that if these flags are used to determine the type of Entity so calculations can have that into account and run differently depending on the type, this could be maintained as an Entity Text property.

You then be able to check the text property (see code below) and configure the calc accordingly:

using Dim sValue As String = api.Entity.Text(textPropertyIndex)

However if we indeed need the flag to be stored as data then I'd look into using either the GetDataCell to retrieve the flag and do the calc for that data unit based on that, or if the flag is actually within the data unit use a databuffer to do all the logic and if statements inside. That would look like something like this:

Dim myDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#60000 - A#41000")  '<-- Update with your data buffer information
api.Data.FormulaVariables.SetDataBufferVariable("myDataBuffer", myDataBuffer, False)

'Loop over data buffer and get member names
For Each sourceCell As DataBufferCell In myDataBuffer.DataBufferCells.Values
    'Can now access member information for the current cell in the data buffer
    Dim accountName As String = sourceCell.DataBufferCellPk.GetAccountName(api)
    Dim ud1Name As String = sourceCell.DataBufferCellPk.GetUD1Name(api)
    Dim ud2Name As String = sourceCell.DataBufferCellPk.GetUD2Name(api)
Next

[FredLucas]

More specifically, and taking a few assumptions so I can give you a more exact example..

Assuming this flag is set by Country and can be part of a Country member config rather than a data entry.

Also assuming that Countries are part of your Entity dimension (hence different data units) and taking into account that Finance Rules are executed by DataUnit, your rule could look like this:

Dim rebateFlag As String = api.Entity.Text(1) 'assuming we use text property 1 for this
If rebateFlag = "RebateCountry" Then
  api.Data.Calculate("A#Rebate = A#Sales * 0.10", True)
End If

If this is all you are calculating, another option could be to have the Data Management Step to only trigger this calculation for the relevant Entities, something like: E#Group.Base.Where(Text1 = RebateCountry)

Hope this helps.

[SimpleLove]

@FredLucas: Thank you so much for your reply. Looks like we indeed need the flag to be stored as data and  I need to look into using GetDataCell to retrieve the flag and do the calc for that data unit based on the flag. Could you please give one example of using GetDataCell to retrieve the flag. Thanks again.

[SimpleLove]

I try the following

Dim FlagDataCell As DataCell =api.Data.GetDataCell("A#Rebate_Flag:UD1#No_Product:UD2#All_Country")

If ((Not FlagDataCell Is Nothing) And (Not FlagDataCell.CellStatus.IsNoData) And (FlagDataCell.CellAmount=1)) Then
     api.Data.Calculate("A#Rebate=A#Sales*0.10",,,,,"UD1#Total_Product.base",,,,True)

End If

I wonder if my approach is correct?

[Henning]

Hi, how many flags do you have? Assuming in your example you have one flag, you will need to define the exact POV for the intersection you store your flag at. Keep in mind that flags should be pulled only to keep the system performant, but that is the next step. Global variables are used for that.

[SimpleLove]

@Henning Thank you for the reply. I do have many flags. Each Country member has a flag and I may have many countries. I think I need to create a data buffer for the Flag and loop through the buffer.

[FredLucas · Accepted solution]

If you need the flag to be data (and do bear in mind that in OS Text attributes can be set differently by Period and Scenario Type and could be maintained via a dashboard / business rule if required), then yes, your approach is correct.

As @Henning mentioned for the GetDataCell you'll want to specify the full POV (except for the Data Unit dimensions that if not specified, will use the ones from the Data Unit POV).

Here's an example:

Dim flag as DataCell = api.Data.GetDataCell("V#YTD:A#[Staff Costs]:F#None:O#Forms:I#None:U1#IT:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None")

Another thing you might be wondering is, how can I do some debugging to understand what is happening.

You can use this function to write messages and print the content of your variables to the error log (that you can then check from (system -> Error Log):

api.LogMessage($"Flag = {flag.CellAmount}; Data Unit POV - Scenario: {api.Pov.Scenario.Name}; Entity: {api.Pov.Entity.Name}; Time: {api.Pov.Time.Name}")

[SimpleLove]

@FredLucas :Thank you so much for you advice. I will try this approach.