Forum Discussion

SimpleLove's avatar
SimpleLove
New Contributor III
7 months ago

How to reference a different scenario member in custom calculation business rule

Hello every expert: I am not able to reference a different scenario member in custom calculation business rule. I tried the following api.Data.Calculate("S#Forecast=S#Actual","A#10100" .............
Finance
  • JackLacava's avatar
    JackLacava
    7 months ago

    Have a look at the properties of your Cube (in the Cubes page). Chances are that the dimensions for one scenario type are different from the dimensions in the other.

    OneStream allows you to use different metadata hierarchies for any Cube/Scenario combination, but the dimensions not specified in the formula (in your case, all of them except Scenarios) must match in order to move data through buffers like you're trying to do. Think about it: if you retrieve a bunch of cells that point to account A, B, and C, and then try to copy them into a place where such accounts don't exist in the hierarchy, what is the product supposed to do? 😊

    (In theory you can use a few mapping functions like ConvertDataBufferExtendedMembers, but they are a bit advanced and you'll have to understand where they can actually work - leave them alone for now).

    I would recommend:

    1. identify which dimension hierarchies differ between the two Scenario Types. For example, let's say only Accounts and Flows are different.
    2. Identify specific source and target members for all those dimensions, e.g. source A#10100 and F#OpenBal and target A#1000 and F#AnotherBal
    3. write the formula to incorporate all those members, e.g. "S#Forecast:A#1000:F#AnotherBal = S#Actual:A#10100:F#OpenBal"

    That should do it. Btw, I would recommend you go through the OneStream Financial Calculations Handbook, it's a great resource and easier to digest than the standard documentation.

SimpleLove's avatar
SimpleLove
New Contributor III

Hello Albert: Thank you for the help. I found that my Actual and Forecast scenario need to be same Scenario type. If they are not the same I cannot do the following calculation in the business rule

api.Data.Calculate("S#Forecast=S#Actual")

I did set up all the member in the custom calculation.

Below is my Scenario dimension, If both Actual and Forecast members have Scenario Type=Actual, I can do the calculation. If Forecast members have Scenario Type=Forecast and Actual members have Scenario Type=Actual, the calculation does not take place.

 

JackLacava's avatar
JackLacava
Honored Contributor
7 months ago

Have a look at the properties of your Cube (in the Cubes page). Chances are that the dimensions for one scenario type are different from the dimensions in the other.

OneStream allows you to use different metadata hierarchies for any Cube/Scenario combination, but the dimensions not specified in the formula (in your case, all of them except Scenarios) must match in order to move data through buffers like you're trying to do. Think about it: if you retrieve a bunch of cells that point to account A, B, and C, and then try to copy them into a place where such accounts don't exist in the hierarchy, what is the product supposed to do? 😊

(In theory you can use a few mapping functions like ConvertDataBufferExtendedMembers, but they are a bit advanced and you'll have to understand where they can actually work - leave them alone for now).

I would recommend:

  1. identify which dimension hierarchies differ between the two Scenario Types. For example, let's say only Accounts and Flows are different.
  2. Identify specific source and target members for all those dimensions, e.g. source A#10100 and F#OpenBal and target A#1000 and F#AnotherBal
  3. write the formula to incorporate all those members, e.g. "S#Forecast:A#1000:F#AnotherBal = S#Actual:A#10100:F#OpenBal"

That should do it. Btw, I would recommend you go through the OneStream Financial Calculations Handbook, it's a great resource and easier to digest than the standard documentation.

  • SimpleLove's avatar
    SimpleLove
    New Contributor III
    7 months ago

    JackLacava Your advice is really helpful. Thanks a lot.