Introduction

This tutorial shows you how to implement Groovy scripts to work with data grids and data grid iterators. You use conditional logic to set the background colors in a data grid, implement performance benchmarking, and set default data values in a grid. Complete the sections in order.

Background

A data grid interface provides access to the POV/Row/Column header cells and provides various iterators to iterate over the data cells.

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into a Cloud EPM Enterprise Service instance. A snapshot cannot be imported over an existing application or business process. Before importing the snapshot, use a separate Cloud EPM Enterprise Service instance or remove the existing application or business process from the instance you plan to use. Importing the snapshot does not automatically replace or restore existing content.

Before starting this tutorial, you must:

  • Have Service Administrator access to a Cloud EPM Enterprise Service instance. The instance should not have a business process created.
  • Upload and import this snapshot into your instance. This snapshot requires the Planning business process. If you have already imported the snapshot for another Groovy tutorial, you can continue using that application without importing the snapshot again.

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.

Setting Background Color by Using Conditional Logic

In this section, you implement a Groovy script to set the background color for cells with data value of #MISSING to color █ (0x00BFFF), cells with data value less than or equal 50000 to color █ (0xFF8C00), and cells with data values more than 50000 to color █ (0x00FA9A).

Note:

After you define a Groovy script, it can be associated with any form, unlike Data Validation rules, which need to be defined in the context of each form.

Creating a Groovy Script

  1. From the Planning Home page, click Navigator (Navigator), then, under Create and Manage, click Rules to open Calculation Manager.
    Navigator with Rules under Create and Manage
  2. On the System View tab, expand EPM Cloud, then HP4, then Plan1. Right-click Rules, then select New.
    System View with the Rules context menu and New selected
  3. For Name, enter Groovy Set Background Color, then click OK.
    New Rule dialog with the rule name, application, and cube
  4. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor with Edit Script and Groovy Script selected
  5. Copy this script and paste it into the editor.
    Code reference 1operation.grid.dataCellIterator.each {
    Code reference 2    if(it.missing) {
    Code reference 3        it.bgColor = 0x00BFFF
    Code reference 4    } else if(it.data <= 50000)
    Code reference 5        it.bgColor = 0xFF8C00
        else if(it.data > 50000)
    Code reference 6        it.bgColor = 0x00FA9A
    }
    

    Code reference 1 The script iterates over each data cell in the current data grid, operation.grid, returned by dataCellIterator.

    The implicit it variable refers to the current item being iterated over. In this case the it variable refers to the current data cell returned by dataCellIterator.

    Tip:

    You can also use an explicitly named variable such as cell as shown in the example below:
     operation.grid.dataCellIterator.each { cell ->
        if(cell.missing) {
          cell.bgColor = 0x00BFFF
        }
    }

    Code reference 2 For the current data cell, the script checks for a missing value.

    Note:

    Note that cell.data returns 0 (zero) for cells that are #MISSING or zero. To check a cell for #MISSING and not zero, you can use the cell.missing property. To check a cell for zero value and not #MISSING, you can use a condition such as cell.data == 0 && !cell.missing.

    Code reference 3 If TRUE, the script sets the cell's background color to 0x00BFFF( █ )

    Code reference 4 The script checks for the data value to be less than or equal to 50000.

    Code reference 5 If TRUE, the script sets the cell's background color to 0xFF8C00 ( █ )

    Code reference 6 If the cell's data value is more than 50000, then the script sets the cell's background color to 0x00FA9A ( █ )

  6. On the toolbar, click Save (Save) to save the script. Click OK to close the information message.
    Information message confirming that the rule is saved
  7. Click Validate and Deploy (Validate and Deploy). Click OK to close the information message.
    Deploy message confirming successful deployment
  8. Leave Calculation Manager open and return to the Planning browser window.

Adding the Rule to a Data Form

  1. On the Planning Home page, click Data. Click EmployeeDriverForm to open the data form.
    EmployeeDriverForm open with its initial data
  2. Click Actions, then select Edit.
    Actions menu with Edit selected
  3. Select the Business Rules tab.
  4. Add Groovy Set Background Color to Selected Business Rules.
    Business Rules tab with the Groovy rule in Selected Business Rules
  5. Under Business Rule Properties, select Run After Load.
    Business Rule Properties with Run After Load selected
  6. Click Save to save the form definition. Click OK to close the information message.
    Information message confirming that the form has been saved
  7. At the bottom of the page, close the Edit EmployeeDriverForm tab to return to the data form.
  8. When the form reloads, verify that missing cells are blue, values less than or equal to 50000 are orange, and values greater than 50000 are green.
    Reloaded form with blue missing cells, orange values at or below the threshold, and green values above the threshold

Profiling and Debugging the Script

In this section, you learn how to use iterators provided by the DataGrid interface, profile the code, and add debugging statements to the job console.

Creating a Groovy Script

  1. Return to the Calculation Manager window.
  2. Select the System View tab. Under Plan1, right-click Rules, then select New.
    System View with New selected on the Rules context menu
  3. For Name, enter Groovy Iterator Performance, then click OK.
    New Rule dialog with Groovy Iterator Performance entered
  4. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor with Edit Script and Groovy Script selected
  5. Copy this script and paste it into the editor.
    Code reference 1def benchmark(GridIterator itr) {
    Code reference 2    def start = currentTimeMillis()
    Code reference 3    itr.each { println "$it.memberNames, cell data: $it.data, $it.formattedValue" }
        def elapsed = currentTimeMillis() - start
    Code reference 4    println "\nTime taken by iterator: $elapsed"
    }
    
    Code reference 5println "Iterate over all data cells in this data grid...\n"
    Code reference 6benchmark operation.grid.dataCellIterator()
    
    println "Iterate over edited data cells in this data grid...\n"
    Code reference 7benchmark operation.grid.dataCellIterator({DataCell cell -> cell.edited})
    
    println "Iterate over Min Salary and Min Bonus data cells in this data grid...\n"
    Code reference 8benchmark operation.grid.dataCellIterator('Min Salary', 'Min Bonus')
    

    Code reference 1 Define a utility method called benchmark() that uses the specified GridIterator to iterate over its cells and prints the list of member names from all dimensions that the cell represents along with its data. It also prints the time taken by the iterator.

    Code reference 2 Start a timer.

    Code reference 3 Iterate over each cell using the specified GridIterator and print the list of member names from all dimensions that the cell represents along with its data value and formatted value. $it.data returns the numeric value for the cell and $it.formattedValue returns non numeric values such as Text, Smart List, or Date as entered by the user.

    Code reference 4 Print the time taken by the iterator to the job console.

    Code reference 5 Log a debugging message to job console.

    Code reference 6 Call the benchmark() method with an iterator to iterate over each cell in the current data grid.

    Code reference 7 Call the benchmark() method with an iterator to iterate over the edited cells in the current data grid.

    Tip:

    This iterator can be used with any property on the DataCell object that returns a boolean such as locked, missing, valid, readOnly, validIntersection, and so on.

    Code reference 8 Call the benchmark() method with an iterator to iterate over the Min Salary and Min Bonus data cells in the current data grid.

  6. On the toolbar, click Save (Save) to save the script. Click OK to close the information message.
    Information message confirming that Groovy Iterator Performance is saved
  7. Click Validate and Deploy (Validate and Deploy). Click OK to close the information message.
    Message confirming successful deployment
  8. Leave Calculation Manager open and return to the Planning browser window.

Adding the Rule to a Data Form

  1. In the EmployeeDriverForm form, click Actions, then select Edit.
    Actions menu with Edit selected
  2. Select the Business Rules tab, then add Groovy Iterator Performance to Selected Business Rules.
    Business Rules tab with Groovy Iterator Performance added to Selected Business Rules
  3. Under Business Rule Properties, for the Groovy Iterator Performance rule, select Run After Save.
    Groovy Iterator Performance configured to Run After Save
  4. Click Save to save the form definition. Click OK to close the information message.
    Information message confirming that the form has been saved
  5. At the bottom of the page, close the Edit EmployeeDriverForm tab to return to the data form.

Testing the Groovy Script

  1. In EmployeeDriverForm, change Min Salary for Grade 1 to 35000 and Min Bonus for Grade 2 to 2000.
    EmployeeDriverForm with two edited cells for FY26
  2. Click Save. The Groovy Iterator Performance rule runs after the data is saved. Click OK to close the information message.
    Information message confirming that data was saved and rules ran successfully
  3. Click Navigator (Navigator), then, under Application, click Jobs to open the Job Console.
    Job Console with the completed Groovy Iterator Performance job
  4. Click Groovy Iterator Performance to display the job details. Under Run Status, expand Completed to view the log messages printed by the Groovy script.
    Completed job details showing iterator log messages

    The first iterator prints all cells and the data values, the second iterator prints only the two cells that were edited, and the third iterator prints only the cells for Min Salary and Min Bonus columns.

  5. Close the Job Details.
  6. Click Navigator (Navigator), then select Data to return to the EmployeeDriverForm.

Setting Default Data Values

In this section, you learn how to set the default data values in the grid using the DataGridBuilder interface.

Creating a Groovy Script

  1. Return to the Calculation Manager window.
  2. Select the System View tab. Under Plan1, right-click Rules, then select New.
    System View with New selected on the Rules context menu
  3. For Name, enter Groovy Reset Grade Data, then click OK.
    New Rule dialog with Groovy Reset Grade Data entered
  4. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor with Edit Script and Groovy Script selected
  5. Copy this script and paste it into the editor.
    Code reference 1Cube cube = operation.application.getCube("Plan1")
    String currentYear = operation.application.getSubstitutionVariableValue("OEP_CurYr")
    
    Code reference 2DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")
    Code reference 3builder.addPov(currentYear, 'Current', 'USD', 'BegBalance', 'BU Version_1', 'No Entity')
    builder.addColumn('Min Salary', 'Max Salary', 'Min Bonus', 'Max Bonus')
    builder.addRow(['Grade 1'], [30000, 50000, '#missing', '#missing'])
    builder.addRow(['Grade 2'], [40000, 60000, '#missing', 5000])
    builder.addRow(['Grade 3'], [70000, 90000, '#missing', 10000])
    
    Code reference 4DataGridBuilder.Status status = new DataGridBuilder.Status()
    Code reference 5builder.build(status).withCloseable { DataGrid grid ->
    Code reference 6    println("Total number of cells accepted: $status.numAcceptedCells")
        println("Total number of cells rejected: $status.numRejectedCells")
        println("First 100 rejected cells: $status.cellsRejected")
    Code reference 7    cube.saveGrid(grid)
    Code reference 8}
    

    Code reference 1 Build a cube object for the Plan1 cube and read the current year from the application-level substitution variable. getSubstitutionVariableValue("OEP_CurYr") reads the application-level substitution variable without an ampersand. In the supplied snapshot, its value is FY26.

    Code reference 2 Get a DataGridBuilder for the cube; define the format in which the script provides the date values.

    Note:

    The DataGridBuilder also supports saving data on behalf of a system user by accepting ExecuteAsUser as the second parameter. Saving data as a system user allows data to be saved without applying the current user's security. Valid values for ExecuteAsUser are CURRENT_USER (the default) and SYSTEM_USER, as in the following example:

    DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY", SYSTEM_USER)

    Code reference 3 Construct a data grid by adding the POV members, column members and rows with data values. Passing currentYear to addPov keeps the reset rule aligned with the form without hard-coding a year.

    Code reference 4 Build a status object for the DataGrid object created in the next step.

    Code reference 5 Build the DataGrid object with the withCloseable construct. It closes the grid when the block completes, so the rule does not leave the grid open in memory.

    Code reference 6 Print the number of cells accepted or rejected by the builder to the job console. Any read-only cells are rejected.

    Code reference 7 Save the data grid.

    Code reference 8 The grid is automatically closed.

  6. On the toolbar, click Save (Save) to save the script. Click OK to close the information message.
    Information message confirming that Groovy Reset Grade Data is saved
  7. Click Validate and Deploy (Validate and Deploy). Click OK to close the information message.
    Message confirming successful deployment
  8. Close Calculation Manager and return to the Planning browser window.

Adding the Rule to a Data Form

  1. In EmployeeDriverForm, click Actions, then select Edit.
    Actions menu with Edit selected
  2. Select the Business Rules tab.
  3. Add Groovy Reset Grade Data to Selected Business Rules.
    Business Rules tab with Groovy Reset Grade Data in Selected Business Rules
  4. Under Business Rule Properties, for the Groovy Reset Grade Data rule, leave Run Before Load, Run After Load, Run Before Save, and Run After Save cleared so that you can launch it manually.
    Groovy Reset Grade Data with all automatic load and save options cleared
  5. Click Save to save the form definition, then click OK to close the information message.
    Information message confirming that the form has been saved
  6. At the bottom of the page, close the Edit EmployeeDriverForm tab to return to the data form.

Testing the Groovy Script

  1. Click Actions, then select Rules & Smart Push.
    Actions menu with Rules and Smart Push selected
  2. Under Rules, select Groovy Reset Grade Data to launch the rule.
    Rules list with Groovy Reset Grade Data selected
  3. When the rule completes successfully, click OK to close the information message.
    Information message confirming that Groovy Reset Grade Data ran successfully
  4. Verify that the grid data has been reset to the following values.
    FY26 EmployeeDriverForm with the default salary and bonus values restored
    GradeMin SalaryMax SalaryMin BonusMax Bonus
    Grade 13000050000MissingMissing
    Grade 24000060000Missing5000
    Grade 37000090000Missing10000
  5. Click Navigator (Navigator), then, under Application, click Jobs to open the Job Console.
    Job Console with the completed Groovy Reset Grade Data job
  6. Click Groovy Reset Grade Data to display the job details. Under Run Status, expand Completed to view the numbers of accepted and rejected cells.
    Completed Groovy Reset Grade Data job details with accepted and rejected cell counts

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit Oracle University to view training resources available.

For product documentation, visit Oracle Help Center.