Validating Data Entry Values with Groovy

Introduction

This 15-minute tutorial shows you how to implement a Groovy script that validates data entry against allowed ranges stored in a driver cube.

Background

In this example, you'll create a script that runs when users save updates to employee salaries and bonuses from a data entry form. The script loads employee grade data (minimum/maximum allowed salary and minimum/maximum allowed bonus) from a driver cube into a temporary grid in memory, and then validates the entered salary and bonus values against the allowed ranges.

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into your Cloud EPM Enterprise Service instance. Before you can import a tutorial snapshot, you must request another Cloud EPM Enterprise Service instance or remove your current application and business process. The tutorial snapshot will not import over your existing application or business process, nor will it automatically replace or restore the application or business process you are currently working with.

Before starting this tutorial, you must:

Have Service Administrator access to a Cloud EPM Enterprise Service instance.
Upload and import this snapshot into your Planning instance. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.

Note:

If you run into migration errors importing the snapshot, re-run the migration excluding the HSS-Shared Services component, as well as the Security and User Preferences artifacts in the Core component. For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Tip:

The scripts you need for this tutorial are linked as text files within each section.

Creating the script

Enter a brief description here if needed. Begin each step with a verb.

Open Calculation Manager and create a rule named Groovy Validate Employee Data in the Plan1 cube.

In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.

Copy this script and paste it into the editor.

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 lookupCube = operation.application.getCube("Plan1")
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()
builder.addPov(['Years', 'Scenario', 'Currency', 'Period', 'Version', 'Entity'], [['FY16'], ['Current'], ['USD'], ['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 lookup cube 
DataGrid dataGrid = lookupCube.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()
if(dataGrid) {
    println("dataGrid is not null")
    dataGrid.dataCellIterator('Min Salary').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.")  
        }
    }
}

On the toolbar, click (Save) to save the script, then click (Validate and Deploy) to validate and deploy the script. Click OK when prompted. Close Calculation Manager.

Explaining the script logic

In this section, we'll describe the script logic one section at a time.

Create a class.
```
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 class, GradeData, to store the employee grade data (minimum/maximum allowed salary and minimum/maximum allowed bonus) from a driver cube.

The toString() method returns a string representation of the GradeData object. The method uses the null safe operator (?.) provided by Groovy. If the variable before the question mark is null it will not proceed and returns NULL. For example, {minSalary?.formattedValue} evaluates to NULL value instead of throwing a NullPointerException exception if minSalary is null.
Create a bundle loader.

// 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 lookupCube = operation.application.getCube("Plan1") 
DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder() 
builder.addPov(['Years', 'Scenario', 'Currency', 'Period', 'Version', 'Entity'], [['FY16'], 
	['Current'], ['USD'], ['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()

Create a resource bundle loader containing localized messages needed by this rule.

Get the driver/lookup cube.

Get a DataGridDefinitionBuilder for the cube. Use the builder to construct a region from which to load the data by adding the POV members, column members and rows.

Build the DataGridDefinition object.

Load the data grid from the lookup cube for the region defined by the DataGridDefinition object.

DataGrid dataGrid = lookupCube.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<String, GradeData>() 
if(dataGrid) {
    println("dataGrid is not null")
    dataGrid.dataCellIterator('Min Salary').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)
    }
}

Create a map called gradeDataMap of grade data (Min/Max Salary and Bonus) by grade name.

Populate the gradeDataMap map with the grade data for each Grade. Grade is the key and the GradeData object populated with the Min/Max Salary and Bonus is the value.

Construct an iterator that iterates over all data cells containing the Grade member in the current input grid.

DataGrid grid = operation.grid
GridIterator itr = grid.dataCellIterator('Grade')

Throw a veto exception.

// 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 that the input grid has at least one cell containing the Grade member. If not, throw a veto exception with the localized message.

Validate the grid values.

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

Validate the values in the input grid being saved against the values in gradeDataMap. Grade in the input grid is an account of type smart list which is derived from the Grade hierarchy defined in the lookup cube. Calling it.DataAsSmartListMemberName gets the name of the member from which the current cell’s smart list value is derived, which can then be used as the key in the map to get the grade data.

Cross dim into the Salary cell and validate the Salary against the allowed Salary in the grade data.

Cross dim into the Bonus cell and validate the Bonus against the allowed Bonus in the grade data. If the Salary or Bonus is not within the allowed limits, set the background color of the cell to red and add a validation error on the cell by calling the addValidationError() API. The error message will be set as the tooltip of the cell and will also show up in the Grid Validation Messages. Also, if this rule is being run After Save, the system will veto the save operation.

Testing the script

In the Form Manager, edit the ManageEmployees form and associate the Groovy Validate Employee Data rule to be run After Load and Before Save (you may need to select Plan1 from the Cube list first).

The ManageEmployees form with the associated rule.

Save the form and close the Form Manager window.
Open the Employees dashboard. Add a Bonus of 1000 for Employee 1, increase the Salary for Employee 2 to 65000. On the form toolbar, click (Save).

In the Error message dialog box, click OK.

Hover the mouse over the red cells to see the detailed error messages as tooltips.

Close the dashboard and open the Jobs console.

The Recent Activity pane of the Job Console.

Click Groovy Validate Employee Data to display the job details. Click the Completed status to see the grade data loaded from the lookup cube and the grade data per each employee's grade.