Example Groovy Scripts

This document provides a few example Groovy scripts to demonstrate the usage of the EPM Groovy object model.

When a context requiring a Groovy script will typically use a short (often, one-line) script, we emphasize that fact by calling it an expression, however technically the terms script and expression are interchangeable. Anywhere you can provide a one-line expression is also a valid context for providing a multi-line script if the need arises. Whether you provide a short expression or a multi-line script, the syntax and features at your disposal are the same.

You need only pay attention that your code returns a value of the appropriate type for the context in which you use it. Any Groovy script that returns a String will be considered as returning an Essbase calc script which will be executed against Essbase. Avoid using Strings as the final value of the last statement unless it is a calc script that you wish to execute after the Groovy script launches successfully.

Example expression to update a global driver by returning a calc script

The following Groovy expression returns a calc script to set the value of the driver member in Essbase.

/*RTPS: {Driver} {DriverValue}*/
"""FIX("No Scenario", "No Version", "No Entity", "No Year", "BegBalance", "Local") $rtps.Driver = $rtps.DriverValue; ENDFIX;"""

Example expression to move a member

/*RTPS: {Employee} {NewParent}*/
rtps.Employee.member.dimension.saveMember(["Member" : rtps.Employee.member.name, "Parent" : rtps.NewParent.member.name] as Map<String, Object>)

Example expression to rename a member

/*RTPS: {Employee} {NewName}*/
rtps.Employee.member.dimension.saveMember(["Member" : rtps.Employee.member.name, "New Name" : rtps.NewName.enteredValue] as Map<String, Object>)

Example expression to rename and move a member

/*RTPS: {Employee} {NewName} {NewParent}*/
rtps.Employee.member.dimension.saveMember(["Member" : rtps.Employee.member.name, "New Name" : rtps.NewName.enteredValue, "Parent" : rtps.NewParent.member.name] as Map<String, Object>)

Example multi line script to demonstrate creation of metadata and data with validations

The following Groovy script validates the Rtp values and vetoes the operation if the values are invalid. If the Rtp values are valid, it adds an employee on the fly and returns a calc script to assign the email and phone number of the new employee:

/*RTPS: {EmployeeName} {EmployeePhone} {EmployeeEmail} {Scenario} {Year} {Period} {Entity} {Version}*/
def mbUs = messageBundle(["validation.invalidemail":"Email address is invalid: {0}", "validation.invalidphone":"Phone number is invalid: {0}",
"validation.memberexists":"The member you have specified already exists and cannot be created: {0}.", "validation.invalidnamelength":"Employee name must be 5 to 40 characters: {0}"])
def mbl = messageBundleLoader(["en" : mbUs]);

// Validate the Rtp values
Dimension employeeDim = operation.application.getDimension("Employee")
Member parentEmployee = employeeDim.getMember("Total Employees")
if(parentEmployee.hasChild(rtps.EmployeeName))
    throwVetoException(mbl, "validation.memberexists", rtps.EmployeeName)

validateRtp(rtps.EmployeeName, {(5..40).contains(it.enteredValue.size()) }, mbl, "validation.invalidnamelength", rtps.EmployeeName);
validateRtp(rtps.EmployeeEmail, /^.+@.+/, mbl, "validation.invalidemail", rtps.EmployeeEmail.enteredValue);
validateRtp(rtps.EmployeePhone, /^(?:\+?1[- ]?)?\(?([0-9]{3})\)?[- ]?([0-9]{3})[- ]?([0-9]{4})$/, mbl, "validation.invalidphone", rtps.EmployeePhone);

Map newEmployee = parentEmployee.newChildAsMap(rtps.EmployeeName)

// Save the new employee as a dynamic child member always
Member employee = employeeDim.saveMember(newEmployee, DynamicChildStrategy.ALWAYS_DYNAMIC)

// Generate the calc script to save employee email and phone property values
"""SET CREATENONMISSINGBLK ON;
FIX(${fixValues(rtps.Scenario, rtps.Year, rtps.Period, rtps.Entity, rtps.Version, employee)}, "Local", "HSP_InputValue")
	"Employee Phone" = $rtps.EmployeePhone;
    "Employee Email" = $rtps.EmployeeEmail;
ENDFIX;"""

Example multi line script to demonstrate advanced data validations

The following Groovy script loads the employee grade data (minimum/maximun allowed salary and minimum/maximum allowed bonus) from a driver cube and validates the salary and bonus for employees in the grid being saved against the allowed ranges.

class GradeData {
    Integer Grade
    DataGrid.DataCell minSalary
    DataGrid.DataCell maxSalary
    DataGrid.DataCell minBonus
    DataGrid.DataCell maxBonus

    public String toString() {
        return "minSalary: ${minSalary?.formattedValue}, maxSalary: ${maxSalary?.formattedValue}, minBonus: ${minBonus?.formattedValue}, maxBonus: ${maxBonus?.formattedValue}"
    }
}

// Create a resource bundle loader containing localized messages needed by this rule.
def mbUs = messageBundle( ["validation.missingmember.grade":"No Grades found on the Grid."] )
def mbl = messageBundleLoader(["en" : mbUs]);

Cube driverCube = operation.application.getCube("Plan1")

DataGridDefinitionBuilder builder = driverCube.dataGridDefinitionBuilder()
builder.setPov(['Years', 'Scenario', 'Currency', 'Period', 'Version', 'Entity'], [['FY16'], ['Current'], ['Local'], ['BegBalance'], ['BU Version_1'], ['No Entity']])
builder.addColumn(['Account'], [ ['Min Salary', 'Max Salary', 'Min Bonus', 'Max Bonus'] ])
builder.addRow(['Grades'], [ ['ILvl0Descendants("Grades")'] ])

DataGridDefinition gridDefinition = builder.build()

// Load the data grid from the driver cube
DataGrid dataGrid = driverCube.loadGrid(gridDefinition, false)

// Create a map of grade data (Min/Max Salary and Bonus) by grade name from the data grid.
def gradeDataMap = new HashMap()

StringBuilder scriptBldr = StringBuilder.newInstance()
if(dataGrid) {
    println("dataGrid is not null")
    GridIterator itr = dataGrid.dataCellIterator('Min Salary')
    itr.each {
        def gradeData = new GradeData()
        gradeData.minSalary = it
        gradeData.maxSalary = it.crossDimCell('Max Salary')
        gradeData.minBonus = it.crossDimCell('Min Bonus')
        gradeData.maxBonus = it.crossDimCell('Max Bonus')
        gradeDataMap[(it.getMemberName('Grades'))] = gradeData
        println(it.getMemberName('Grades') + ": " + gradeData)
    }
}

DataGrid grid = operation.grid

// Construct an iterator that iterates over all data cells containing the Grade member.
GridIterator itr = grid.dataCellIterator('Grade')

// Throw a veto exception if the grid has at least one cell but does not contain any cells containing the Grade member.
if(!grid.empty && !itr.hasNext()) {
    // Found 0 cells with Grade
    throwVetoException(mbl, "validation.missingmember.grade");
}

// Validate the values in the grid being saved against the values in gradeDataMap.
itr.each {
    GradeData gradeData = gradeDataMap[it.DataAsSmartListMemberName]
    if(gradeData == null) {
        println("Unable to locate grade data for grade: ${it.DataAsSmartListMemberName}, with data value: ${it.formattedValue}" )
    } else {
        println(it.getMemberName('Employee') + ": " + gradeData.toString())
        DataCell salary = it.crossDimCell('Salary')
        if(salary == null)
        println("Unable to locate Salary")
        else if(salary.data < gradeData.minSalary.data || salary.data > gradeData.maxSalary.data) {
            salary.addValidationError(0xFF0000, "Salary is outside of the allowed range.")
        }
        DataCell bonus = it.crossDimCell('Bonus')
        if(bonus == null) {
            println("Unable to locate Bonus")
        } else if(bonus.data < gradeData.minBonus.data || bonus.data > gradeData.maxBonus.data) {
            bonus.addValidationError(0xFF0000, "Bonus is outside of the allowed range.")
        }
    }
}