Wednesday
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.
Solved! Go to Solution.
Thursday
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}")
Wednesday
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
Wednesday
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.
Wednesday
@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.
Wednesday
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?
Thursday
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.
Thursday
@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.
Thursday
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}")
Thursday
@FredLucas :Thank you so much for you advice. I will try this approach.