Oracle Documentation

Validating data entry values with Groovy

Before you begin

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.

What do you need?

An EPM Enterprise Cloud Service instance allows you to deploy and use one of the supported business processes. To deploy another business process, you must request another EPM Enterprise Cloud Service instance or remove the current business process.

  • Have Service Administrator access to EPM Enterprise Cloud Service. The instance should not have a business process created.
  • If you haven't already, register for a free Oracle Cloud Customer Connect account so you can access the required files. Upload and import this Planning snapshot into your environment. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.

For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

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

Creating the script

  1. Open Calculation Manager and create a rule named Groovy Validate Employee Data in the Plan1 cube.
    New Object
  2. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor Options
  3. Copy this script and paste it into the editor.
    Pasted script.
  4. On the toolbar, click Save (Save) to save the script, then click Validate and Deploy (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.

  1. 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.

  2. 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.

  3. Load the data grid from the lookup cube for the region defined by the DataGridDefinition object.
    DataGrid dataGrid = lookupCube.loadGrid(gridDefinition, false)  
  4. 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.

  5. 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')
  6. 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.

  7. 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

  1. 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).
  2. The ManageEmployees form with the associated rule.
  3. Save the form and close the Form Manager window.
  4. 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 (Save).
    The Employees dashboard.
  5. In the Error message dialog box, click OK.
    The Employees dashboard.
  6. Hover the mouse over the red cells to see the detailed error messages as tooltips.
    The error message.
    The error message.
  7. Close the dashboard and open the Jobs console.
    The Recent Activity pane of the Job Console.
  8. 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.
    The log messages.

Want to learn more?



Copyright © 1995, 2020, Oracle and/or its affiliates.