Adventures in Groovy – Part 30: Dynamically Identify Actionable Months

Print
Almost every ePBCS application will require the need to run business logic and execute data movements on specific months based on a scenario selected.  Almost every Data Form will require action to be taken dynamically based on the selection of the Scenario.  Assuming a fiscal year of Jan to Dec and Actuals being final through Jun,

  • processes on Actuals will run on Jan through Jun
  • processes on Budget/Plan will run on Jan through Dec
  • processes on Forecast will run on Jul through Dec

Prior to Groovy, this was done in an Essbase calculation by writing if statements based on the scenario selected in the POV and checking if the month was part of a variable range.  It might have looked something like this.

Groovy provides a much more efficient way to accomplish this.  Although it may be different based on specific needs, the example below will assume that Forecast and Actuals can be identified based on a variable that is updated monthly identifying the last month of Actuals.

Setting Up The Variables and Run Time Prompts

This application has a substitution variable named v_CurMonth used to identify the current reporting month of Actuals, or the last month Actuals are final.  This will be used in the Run Time Prompt as a default value in several places and this is required.

A Run Time Prompt is also required.  There is nothing unique about the RTP for this use and the application may likely already have one.  This will be a member Type, be connected to the Period dimension, and have a default value of the substitution variable above.  It would look similar to this.

Finally, a variable in the Groovy business rule must be created.  This variable will be set as an override value with the default value equal to the substitution variable created above.  In a Groovy business rule, a variable is instantiated by adding the following line at the top of the rule.  It looks like a comment, but it acts differently when it starts with RTPS:.

After this is added, the Variables tab of the business rule will show all the variable defined.  Set the variable so that it is hidden and is used as an override value.

The Code

At this point, all of the required variables are setup.  The first step is to define the months.  This can be hard coded since this is not something that will change frequently.  If this is added to a script and included in all the business rules it is needed, changing it would be easy if the months did change since it is in one place and shared throughout.

Optionally, this can be set dynamically.  Either option, in my opinion, is acceptable.  The result of the following would produce the exact same value for months.

The next piece of this rule is to get the month from the variable.  This will be the key to identify which months are used in calculations that run on Actuals and Forecast.

The last step is to get the appropriate months based on the Scenario selected.  If Actuals is selected, all the elements in the months list from the first to the index of the month in the RTP are selected.  If Plan is selected, all the months are included.  This can be altered based on needs.  Some companies do mid year plans, for example, and may need to only execute the logic on the last 6 months.  If Forecast is selected, all the elements in the months list AFTER the RTP value to the end of the list are selected.

Using useMonths and useMonthsString

The hard part is over.  Using the variable is the easy part.  It can be used anywhere the months needs to be change from all months to the identified months.

In a DataMap/SmartPush

Same concept but overriding the entire POV (something more realistic in a real world example)

In a GridBuilder

In an Essbase FIX statement

Finally

This could be added to a script and included in all the Business Rules that require such a variable.  It creates a variable once that can be used in many classes.  It improve maintainability and reduces replicating the same logic for each class.  If you have any suggestions, or have something you would like to share, please post a comment.

 

Please follow and like us:
RSS
Facebook
PINTEREST
LinkedIn
 
Posted in:

Leave a Reply

Your email address will not be published. Required fields are marked *