Forum Stats

  • 3,752,194 Users
  • 2,250,468 Discussions
  • 7,867,747 Comments

Discussions

Logic to run a business rule only if SmartList value equals 'Yes'?

User_BP7C5
User_BP7C5 Member Posts: 29 Green Ribbon
edited Aug 21, 2020 1:56PM in Planning and Budgeting

Hi Everyone!  Hope everyone is well and safe!!

Wondering in PBCS or Planning if there's a way to only run a business rule during FDMEE or Data Management Data Load Rule if a SmartList equals 'Yes'.   The idea here is we have a 'reclass' that needs to be done but only on months that are equal to or after Jun->FY20.  So I created a SmartList and it's populated with 'Yes' for all periods and years that are Jun->FY20 or greater and 'No' for every period/year up to that point.  Data is loaded via Data Management in PBCS and there are business rules that run after the load.  I need to add this business rule too, but somehow if the 'SmartList' is 'No' then I need it to just exit the business rule or skip to the bottom or something.  I'm trying to avoid a bunch of 'IF' statements in the business rule as it would make it too cumbersome.  I know this is simple in a batch script, but just wondering how I can skip to a certain part of a business rule based off some logic.

Possible?

Something like:

If SmartList = ‘Yes’ Then goto RUN_CODE ELSE goto SKIP_CODE

:RUN_CODE

Lines of code

Lines of code

Lines of code

  1. Etc.

:SKIP_CODE

Exit

Best Answer

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Aug 19, 2020 12:28PM Accepted Answer

    Hi,

    using variables, not smartlist, but this is how I don't process Feb (or whatever month), so say &fcstmonths is set to Apr:Dec then the below block will not take any processing time...

    fix(@intersect("Feb",&fcstmonths), Working, Budget)

    --your logic in here

    endfix

Answers

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Aug 19, 2020 12:28PM Accepted Answer

    Hi,

    using variables, not smartlist, but this is how I don't process Feb (or whatever month), so say &fcstmonths is set to Apr:Dec then the below block will not take any processing time...

    fix(@intersect("Feb",&fcstmonths), Working, Budget)

    --your logic in here

    endfix

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Aug 19, 2020 12:34PM

    I don't understand the @Intersect.   How does it read?   If you don't want to process 'Feb' then just don't Fix on it?  Not getting how this helps.. Sorry

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Aug 21, 2020 5:48AM

    Say &fcstmonths = Jan:Mar then the intersect of Jan:Mar with Feb (as above) is Feb, so Feb would get processed.

    Say &fcstmonths = Apr:Dec, the intersect of Apr:Dec with Feb is nothing, so (note this well) provided you have set emptymembersets to ON, see this for why ->

    https://docs.oracle.com/cd/E57185_01/ESBTR/set_emptymembersets.html

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Aug 21, 2020 5:51AM

    So, say you follow fairly standard practise and always process on the 'Working' version then; -

    set emptymembersets on;

    FIX(@intersect("Working",&YourBoolean))

    --your logic in here

    endfix

    In the above example, if you set the &YourBoolean to anything other than Working then the logic within the FIX statement, however lengthy or complex, would not be evaluated.

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Aug 21, 2020 8:35AM

    Hi Robert!!

    Thanks for that clarification.  That helps immensely in understanding the @Intersect.   I think it can be quite powerful and eliminate many IF statements in the future.  I do have a question though.

    I set a variable 'ViewToExecute'=YTD

    When I put the @Intersect all by itself at the top of the calc, then I get desired results.

    Fix(@Intersect("MTD",&ViewToExecute))

    However when I make this the first Fix, then it still runs the entire calc.  Not sure, but @Intersect must be by itself?

    Fix( "FY25" , "P06" , "USD" ,@Intersect("MTD",&ViewToExecute) )

    Or did I do something wrong?

    Thanks for advice!!

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Aug 21, 2020 11:48AM

    Yes, you need to nest the FIX, otherwise the result will not be 'catchable' by the emptymembersets, as the rest of your fix statement will not yield an empty member set, and so will not be suppressed.

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Aug 21, 2020 12:00PM

    Makes sense and it works.   I'm also told via Data Management as this is PBCS there are things called Logic Groups that you can do this fairly easily.  I'll explore that too.  Many options as it turns out.

    Thanks again for the lesson!!!

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Aug 21, 2020 12:07PM

    On another note, I see this in one of our calc's.

      Fix (@Intersect(@Relative("IncomeStmt",0),@UDA("Account","Avgrate" ) ))
         Fixparallel(8,@Intersect(@Remove(@Relative("TotalEMEA",0),@Relative("TotalOps",0)),@List(@UDA("Entity","USD"))) )
            DATACOPY "FC" to "USD";
         Endfixparallel

    Is this:

    1 - Fixing on Income Statement Accounts only when the UDA is 'Avgrate'?

    2 - Fixing on only the Lev0 members of 'TotalEMEA' sans 'TotalOps' when the UDA is 'USD' on Entity?

    Make sure I'm reading that right.  IF I am, this avoids all 'IF' statements was the idea I think....

  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Aug 21, 2020 12:29PM

    Easy way to see is run it in a test environment and look at what the logged action was, without knowing your cube's particular metadata and without knowing what your emptymemberset setting is in this case I would not like to comment.

    But yes, to simplify; -

    Provided your emptymembersets is on, if the outer fix evaluates to an empty member set then Essbase completely ignores it and all of its contents.

    Likewise, if the outer fix does not evaluate to an empty member set but the inner fix does, then the datacopy will not happen.

    Note 'empty member set' is purely in reference to the metadata, not the data.

    So Intersect (A, B:C) is always an emptymember set.

    For future reference good forum etiquette is one question per post, and close / mark your posts when you are done for the benefit of all.

  • User_BP7C5
    User_BP7C5 Member Posts: 29 Green Ribbon
    edited Aug 21, 2020 1:56PM

    yes. sorry.

    I did mark it answered prior didn't I?