EPM GROOVY – WRITING A GRID-SPREAD GROOVY SCRIPT THAT WORKS WITH ANY PLANNING APP 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.

SCRIPT

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.

CONCLUSION

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.

Share this post: on LinkedIn