EPM GROOVY – Writing a Grid-Spread Groovy Script That Works With Any Planning Application Without Any Modification

A few years ago, I worked in an ASO cube that allowed user input at the parent level. That was probably one of my first blog articles. I recently worked on an application where we had to allocate values to lower levels after they were entered in a form at the parent level.

What if Oracle, like the other market players, offers grid spread capabilities for all dimensions that functions in any application? Currently the grid spread works only on Period dimension. I am sure they might have been working towards extending the feature to all dimensions. Meanwhile, using Groovy, I considered developing a rule that would work with any application , sort of like a “out of the box rule.”

After evaluating it for a while, I decided towards going back and writing new code from scratch (It is easier to write a new script than to read and debug the old one)

Major points considered:

  1. Any application can run the script without needing to be updated.
  2. ASO and BSO plan types must be supported.
  3. Work with all form layouts, regardless of the dimensions or members levels in the rows and columns.
  4. Not necessarily all of the descendants of a particular parent member must exist in the form.

I was wrong about how simple it was. The ‘DataGrid.HeaderCell’ interface made the task a lot simpler.

HeaderCell provides the below details of a member:

  1. Sparse, Dense, Dynamic Calc or Parent member
  2. Access to member Name and dimension Name
  3. Access to dimension Type

Script Flow

  1. Filter every edited row
  2. Iterate each edited row and each edited cell.
  3. Individually process each edited Cell
    • Verify that the input is at the parent level.
    • If so, build a grid with level 0 members to determine the allocation pattern.
  4. To allocate values to the lowest level, create a write builder.
  5. Save the writing grid, then repeat the procedure for each cell.


operation.grid.rows.findAll{it.data.any{it.edited}}.each { DataGrid.Row row ->		
	row.data.indexed().findAll{colIdx, DataCell dataCell -> dataCell.edited}.each { colIdx, DataCell dataCell ->    	
		List<HeaderCell> headers = (row.headers + operation.grid.pov + operation.grid.columns*.getAt(colIdx)).flatten() as List<HeaderCell> 
		if( headers.any{it.zoomable} ) {
			FlexibleDataGridDefinitionBuilder readGridBuilder = operation.cube.flexibleDataGridDefinitionBuilder()
			readGridBuilder.with {
				setColumnDimensions(headers.findResults{!it.zoomable ? it.dimName : null } as String[]) 
				addColumn(headers.findResults{!it.zoomable ? it.mbrName : null } as String[])
				setRowDimensions(headers.findResults{it.zoomable ? it.dimName : null} as String[]) 
				addRow(headers.findResults {it.zoomable ? """Lvl0Descendants("$it.mbrName")""" : null } as String[])
			DataGridBuilder writeGridBuilder = operation.cube.dataGridBuilder("MM/DD/YYYY") 
			operation.cube.loadGrid(readGridBuilder.build(),false).withCloseable { readGrid ->            	
			def( double newTotal, double previousTotal ) = [ dataCell.data, readGrid.dataCellIterator().collect{DataCell cell-> cell.data}.sum() as Double ]                 
				readGrid.columns.each { List<HeaderCell> column -> writeGridBuilder.addColumn(column.mbrName as String[]) }                 
				readGrid.rows.each { DataGrid.Row readRow -> writeGridBuilder.addRow((readRow.headers*.mbrName).flatten() as List<String>, getNewValue(readRow, dataCell.data ) as List )  }                                    				                                                                                      								                				
				writeGridBuilder.build().withCloseable{writeGrid -> operation.cube.saveGrid(writeGrid)}

getNewValue is a custom function that calculates and passes the value to the write builder.


It processes each cell separately despite being extremely dynamic in nature. Consequently, it must produce a Grid Definition builder and a Grid builder for each cell. There must be more ways of achieving this task, I’m sure of it. I sincerely hope that this information is useful.

Note: The script serves as an example of how HeaderCells can be used to create dynamic operations without having to hard-code any dimension or member names. The script could still be flawed. Explore / try it at your own risk, please.


2 responses to “EPM GROOVY – Writing a Grid-Spread Groovy Script That Works With Any Planning Application Without Any Modification”

  1. Peter Nitschke avatar

    That’s fantastic! I do like the dynamic opening of the intersections. Have you had much opportunity to performance test it? I suspect opening and closing grids at volume might be problematic, but it’d only really matter if data was input at multiple different levels (and therefore it needed to trigger multiple new grids). My reading of it is that it would create a new grid for each period though right?

    Is it possible to provide an example of your getNewValue function? Is that where you’re dealing with the possible options of no data \ zero data \ pro rata’d data?

    1. Raja Stalvin avatar

      Thanks Peter! I just thought of processing it by each cell individually which creates multiple grids for three reasons:
      1. I don’t really have to handle dimension details
      2. 500K limit in write option may not be a threat in most of the time
      3. If two cells from the same dimension are edited, the last edited from the iterator is the final value. If Jan and YearTotal is edited together, first, Jan data will be allocated to all other dimensions and then YearTotal will be allocated to all dimensions including Jan. So YearTotal is the final value that would be preserved.

      Each grids get closed as soon as the edited cell is processed.

      getNewValue function is to determine the allocation method to figure out Share or Spread option required. If needed, the below code can be used to replace getNewValue() function:
      row.data.collect{DataCell cell -> previousSum == 0 ? currentSum/ readGrid.size() : cell.data * (currentSum/previousSum) } as List

      I hope this answers your queries. Thank you!