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" ...........)

in the finance business rule I created. When I run the rule through Data Management Step, I have to specify Scenario and I select Forecast, I got error S#Actual is not valid dimension member when I run the business rule through custom calculate step. I really appreciate any helps from you. Thanks in advance.

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.

  • aformenti's avatar
    aformenti
    Contributor II
    7 months ago

    HI SimpleLove ,

    Could you share the Business rule? I assume that the Scenario Actual exist with this name within the Scenario Dimension?

    Also, the screenshot you sent is missing the Cube Definition, and pulling the Entity, and Time from the Application POV. Is that correct? I suggest you start with a pre-defined/hardcoded POV (Cube, Entity and Time) to troubleshoot. 

    Hope that helps, 

    Albert

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

      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.