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
- From the Planning Home page, click
(Navigator), then, under Create and Manage, click Rules to open Calculation Manager.
- On the System View tab, expand EPM Cloud, then HP4, then Plan1. Right-click Rules, then select New.

- For Name, enter
Groovy Set Background Color, then click OK.
- 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.
operation.grid.dataCellIterator.each {
if(it.missing) {
it.bgColor = 0x00BFFF
} else if(it.data <= 50000)
it.bgColor = 0xFF8C00
else if(it.data > 50000)
it.bgColor = 0x00FA9A
}
The script iterates over each data cell in the current data grid, operation.grid, returned bydataCellIterator.The implicit
itvariable refers to the current item being iterated over. In this case theitvariable refers to the current data cell returned bydataCellIterator.Tip:
You can also use an explicitly named variable such ascellas shown in the example below:
operation.grid.dataCellIterator.each { cell -> if(cell.missing) { cell.bgColor = 0x00BFFF } }
For the current data cell, the script checks for a missing value.Note:
Note thatcell.datareturns 0 (zero) for cells that are #MISSING or zero. To check a cell for #MISSING and not zero, you can use thecell.missingproperty. To check a cell for zero value and not #MISSING, you can use a condition such ascell.data == 0 && !cell.missing.
If TRUE, the script sets the cell's background color to 0x00BFFF( █ )
The script checks for the data value to be less than or equal to 50000.
If TRUE, the script sets the cell's background color to 0xFF8C00( █ )
If the cell's data value is more than 50000, then the script sets the cell's background color to 0x00FA9A ( █ ) - On the toolbar, click
(Save) to save the script. Click OK to close the information message.
- Click
(Validate and Deploy). Click OK to close the information message.
- Leave Calculation Manager open and return to the Planning browser window.
Adding the Rule to a Data Form
- On the Planning Home page, click Data. Click EmployeeDriverForm to open the data form.

- Click Actions, then select Edit.

- Select the Business Rules tab.
- Add Groovy Set Background Color to Selected Business Rules.

- Under Business Rule Properties, select Run After Load.

- Click Save to save the form definition. Click OK to close the information message.

- At the bottom of the page, close the Edit EmployeeDriverForm tab to return to the data form.
- When the form reloads, verify that missing cells are blue, values less than or equal to
50000are orange, and values greater than50000are green.
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
- Return to the Calculation Manager window.
- Select the System View tab. Under Plan1, right-click Rules, then select New.

- For Name, enter
Groovy Iterator Performance, then click OK.
- 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.
def benchmark(GridIterator itr) {
def start = currentTimeMillis()
itr.each { println "$it.memberNames, cell data: $it.data, $it.formattedValue" }
def elapsed = currentTimeMillis() - start
println "\nTime taken by iterator: $elapsed"
}
println "Iterate over all data cells in this data grid...\n"
benchmark operation.grid.dataCellIterator()
println "Iterate over edited data cells in this data grid...\n"
benchmark operation.grid.dataCellIterator({DataCell cell -> cell.edited})
println "Iterate over Min Salary and Min Bonus data cells in this data grid...\n"
benchmark operation.grid.dataCellIterator('Min Salary', 'Min Bonus')
Define a utility method called benchmark()that uses the specifiedGridIteratorto 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.
Start a timer.
Iterate over each cell using the specified GridIteratorand print the list of member names from all dimensions that the cell represents along with its data value and formatted value.$it.datareturns the numeric value for the cell and$it.formattedValuereturns non numeric values such as Text, Smart List, or Date as entered by the user.
Print the time taken by the iterator to the job console.
Log a debugging message to job console.
Call the benchmark()method with an iterator to iterate over each cell in the current data grid.
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.
Call the benchmark()method with an iterator to iterate over the Min Salary and Min Bonus data cells in the current data grid. - On the toolbar, click
(Save) to save the script. Click OK to close the information message.
- Click
(Validate and Deploy). Click OK to close the information message.
- Leave Calculation Manager open and return to the Planning browser window.
Adding the Rule to a Data Form
- In the EmployeeDriverForm form, click Actions, then select Edit.

- Select the Business Rules tab, then add Groovy Iterator Performance to Selected Business Rules.

- Under Business Rule Properties, for the Groovy Iterator Performance rule, select Run After Save.

- Click Save to save the form definition. Click OK to close the information message.

- At the bottom of the page, close the Edit EmployeeDriverForm tab to return to the data form.
Testing the Groovy Script
- In EmployeeDriverForm, change Min Salary for Grade 1 to
35000and Min Bonus for Grade 2 to2000.
- Click Save. The Groovy Iterator Performance rule runs after the data is saved. Click OK to close the information message.

- Click
(Navigator), then, under Application, click Jobs to open the Job Console.
- Click Groovy Iterator Performance to display the job details. Under Run Status, expand Completed to view the log messages printed by the Groovy script.

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.
- Close the Job Details.
- Click
(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
- Return to the Calculation Manager window.
- Select the System View tab. Under Plan1, right-click Rules, then select New.

- For Name, enter
Groovy Reset Grade Data, then click OK.
- 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.
Cube cube = operation.application.getCube("Plan1")
String currentYear = operation.application.getSubstitutionVariableValue("OEP_CurYr")
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")
builder.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])
DataGridBuilder.Status status = new DataGridBuilder.Status()
builder.build(status).withCloseable { DataGrid grid ->
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First 100 rejected cells: $status.cellsRejected")
cube.saveGrid(grid)
}
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 isFY26.
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 acceptingExecuteAsUseras the second parameter. Saving data as a system user allows data to be saved without applying the current user's security. Valid values forExecuteAsUserareCURRENT_USER(the default) andSYSTEM_USER, as in the following example:
DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY", SYSTEM_USER)
Construct a data grid by adding the POV members, column members and rows with data values. Passing currentYeartoaddPovkeeps the reset rule aligned with the form without hard-coding a year.
Build a status object for the DataGrid object created in the next step.
Build the DataGrid object with the withCloseableconstruct. It closes the grid when the block completes, so the rule does not leave the grid open in memory.
Print the number of cells accepted or rejected by the builder to the job console. Any read-only cells are rejected.
Save the data grid.
The grid is automatically closed. - On the toolbar, click
(Save) to save the script. Click OK to close the information message.
- Click
(Validate and Deploy). Click OK to close the information message.
- Close Calculation Manager and return to the Planning browser window.
Adding the Rule to a Data Form
- In EmployeeDriverForm, click Actions, then select Edit.

- Select the Business Rules tab.
- Add Groovy Reset Grade Data to Selected Business Rules.

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

- Click Save to save the form definition, then click OK to close the information message.

- At the bottom of the page, close the Edit EmployeeDriverForm tab to return to the data form.
Testing the Groovy Script
- Click Actions, then select Rules & Smart Push.

- Under Rules, select Groovy Reset Grade Data to launch the rule.

- When the rule completes successfully, click OK to close the information message.

- Verify that the grid data has been reset to the following values.

Grade Min Salary Max Salary Min Bonus Max Bonus Grade 1 30000 50000 Missing Missing Grade 2 40000 60000 Missing 5000 Grade 3 70000 90000 Missing 10000 - Click
(Navigator), then, under Application, click Jobs to open the Job Console.
- Click Groovy Reset Grade Data to display the job details. Under Run Status, expand Completed to view the numbers of accepted and rejected cells.

Related Links
- Planning Tutorials — Select Implementing Groovy from the area-of-interest list to explore additional Groovy tutorials.
- Java API Reference for Groovy Rules — Explore the Groovy APIs available for developing Groovy rules and templates.
- REST APIs for Oracle Fusion Cloud EPM — Explore the REST APIs available for managing and automating Cloud EPM tasks.
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.
Working with Data Grids and Iterators in Groovy
F87156-03
November 2026
Copyright © 2026, Oracle and/or its affiliates.