One of the more surprisingly useful things that can be done in a Groovy calculation is querying metadata. This was discussed in Part 11: Accessing Metadata Properties, but I recently had a situation where users could load data at parent levels and have it allocated down to the bottom of the hierarchies.  Knowing if users selected a parent member in the hierarchy was critical because the business process was different based on whether it was a parent or a level 0 member.  This can obviously be checked in a business rule, but I had the need to alter several functions in the process if the selection was a parent.  Smart Pushes and data synchronizations, as well as the business rules that were executed, were dependent on the level of the hierarchy selected.  This is possible with Groovy.

The Code Explained

Using a combination of methods, the children of any member can be returned in a list.  That list can be used for all kinds of things.  This focuses on getting the size of the list to identify if it has children.

// Normally getting the POV Selected, but this is hard coded for the example
def povProduct = '"' + "Tents" + '"' 
// Setup a connection to the cube
Cube cube = operation.application.getCube("GP")
// Create a dimension object
Dimension productDim = operation.application.getDimension("Product", cube)
// Create a member list - ILev0 of the member passed
def MemberList = productDim.getEvaluatedMembers("ILvl0Descendants($povProduct)", cube) 

println MemberList
println MemberList.size() == 1 // will print true or false

The MemberList variable now holds a list of all the members (level 0 members below tents), including the member passed in the function.  This will always have at least one item in the list, assuming the member exists in the dimension.  If there is more than 1, we know it has children, and therefore, is not a level 0 member.

if(MemberList.size() == 1)
  {
  //actions taken if the POV is a level 0 selection
  }
  else
  {
  //actions taken if the POV is NOT a level 0 selection
  }

Summary

In applications where top down planning is needed, different logic runs when data is entered at a parent verses when it is entered at a leaf (lev0) member.  This can be handled in an Essbase calculation using @ISLEV0, but with Groovy, the fix statement can be altered so that unnecessary logic isn’t executed and data pushes are limited to only what is impacted.

Do you have an idea of how you might benefit from the results of metadata queries?  Come up with your own and share with the group by commenting below.

Now that you are knee deep in Groovy, help yourself out and reuse common code.  The more you learn, the more efficient you will be and will laugh at some of your initial attempts at your Groovy calculations.  If you are like me, you get excited about all the possibilities and learn as you go.  When you find better ways to do something, or even preferable ways, you end up with an application with inconsistent representations of the same logic.  You are too busy to go back and update all the snippets you have improved, so it stays in a somewhat messy state.

Do yourself a favor and start reusing some of the things you need in every script.  When you start doing more in Groovy calculations, the more you can replicate the better.  Making some of the code business rule agnostic will make you more productive and will create a consistent approach to calculation strategy.  An example of this would be variables for the dimensions in the POV.  Or, maybe a common map used to push data from WFP to the P&L.  Regardless of the use, as soon as you see things that will be duplicated, put them in a script.  Scripts can be embedded in Groovy calculations just like regular Business Rules.

Header Script

This is an example of something that I find useful for every Groovy calculation I am writing.  It accomplished a couple things.

1. It stores the forecast months so data maps, smart pushes, Essbase calculations, and grid builder functions, can be dynamically execute on all months for a budget and the out months for the forecast based on the scenario selected.
2. It gets the numeric value for the current month to be used in other localized calculations.
3. It creates a calendar object to get the current time for logging performance.
4. The parameters are logged to the job console.

/*RTPS: {RTP_Month}*/

def Month = rtps.RTP_Month.toString().replaceAll("\"", "")
def MonthFix = rtps.RTP_Month.toString()
def months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
def actualMonths = []
def forecastMonths = []

months.eachWithIndex {item, index ->
  if(index > months.findIndexOf{it == Month})
    forecastMonths <<  item 
  else
    actualMonths <<  item 
}

def actualMonthsFix = """\"${actualMonths.join('", "')}\""""
def forecastMonthsFix = """\"${forecastMonths.join('", "')}\""""
def useMonths = []

if(operation.grid.pov.find{it.dimName =='Scenario'}.essbaseMbrName.toString().toLowerCase().contains('forecast'))
  useMonths = forecastMonths
else
  useMonths = months

//Get time for logging perforance
Calendar calendar = Calendar.getInstance()
calendar.setTimeInMillis(currentTimeMillis())

// Get the numeric value for the month (Jan=0)
int monthNumber = Calendar.instance.with {
  time = new Date().parse( "MMM", Month )
  it[ MONTH ]
}

println "The current month number is $monthNumber"
println "The current month is $Month"
println "The current month (Fix) is $MonthFix"
println "The actual months (list) are ${actualMonths}"
println "The actual months (string) are ${actualMonthsFix}"
println "The forecast months (list) are ${forecastMonths}"
println "The forecast months (string) are ${forecastMonthsFix}"

Conversion Table

If the application moves data from more detailed plan types to a consolidated P&L, it likely has conversions.  This is an example of an account map from a Gross Profit plan type to a P&L plan type.  How it is used will be explained in a later submission, but it is used in every calculation that synchronizes data from the GP to the P&L cube.  Therefore, it is a great candidate to have in a shared library (a script) so it can be maintained in one place.

def acctMap = ['Cases':'Units',
               'Sales':'42001',
               'COGS':'50001',
               'Tax':'50015',
               'Chargeback':'56010',
               'Investment Gains':'50010',
               'Writeoffs':'56055',
               'Growth Attributed to New Products':'56300',
               'Samples':'56092'
                ]

Sharing Scripts

Just as scripts can be embedded in regular business rules, the exact same syntax is used in Groovy calculations.  Assume the header script above is called Common Groovy and sits in an application named FinPlan in the GP plan type.  The inclusion of the script into any  Groovy calculation would be done by inserting the following text.

%Script(name:=" Common Groovy ",application:="FinPlan",plantype:="GP")

Conclusion

These are just examples to get you thinking about how you can reduce the headaches down the road when your Groovy calculations need maintained.  It can easily be expanded to include common POV members and other common code.  Be mindful of how global this is as not all POV members are in all forms.  You might find it useful to have a script that is shared everywhere and a few others shared for like calculations or forms.  You aren’t limited to only including one shared script.

If you have a snippet you are using in all your Groovy calculations, share it with the community.  We always like to get feedback and learn from each other.