EPM on-the-go

Sharing my experience in Enterprise Performance Management (EPM) with fellow EPM consultants

By

Bosco Lam

Simple Groovy Part 1 – Collect edited cells

We are starting a five-part series on writing Groovy scripts in EPBCS.

Summary

Let’s start with something simple: reading what’s on the grid and collecting the edited cells so you can limit the blocks you’re looping through using a Calc Script.

This approach helps improve performance by avoiding unnecessary iterations. Instead of looping through every member in the FIX – for example, @RELATIVE(Mbr,0) – you can identify the cells that were actually edited on the grid and use those members to narrow down the FIX.

This means the calculation only processes the relevant blocks rather than scanning through potentially thousands of unnecessary blocks, resulting in a more efficient and targeted calculation.

Action

Start with println only. Use the operation object. 

//operation.grid.pov - a simple way to find the pov on a form
operation.grid.pov.each {
    println "POV: $it.dimName = $it.essbaseMbrName"
}
//operation.grid.dataCellIterator - Iterate through the grid and collect edited cells
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
    println "EDITED: " + it.memberNames.join("->") + " = " + it.data
}

Now that you know how to use the operation object, let’s move on to something a little more challenging.

Let’s turn it into a calculation. 

//Declare lists to collect the members 
def lstEnt = []
def lstCC = []

Now that we have declared the lists, let’s use the operation object again. 

//Collect edited cell by iterating a grid
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
    lstEnt << it.getMemberName("Entity")
    lstCC  << it.getMemberName("Cost Center")
}

After you collect the members, we need to make them unique before passing them to the Calc Script.

//Declare Strings to store unique members
String vEnt = lstEnt.unique().collect { '"' + it + '"' }.join(",")
String vCC  = lstCC.unique().collect { '"' + it + '"' }.join(",")
String vPov = operation.grid.pov.findAll { !(it.dimName in ["Entity", "Cost Center"]) }
                               .collect { '"' + it.essbaseMbrName + '"' }.join(",")

The last step – Execute the script. 

//Call calc script
String calcScript = """
FIX ($vPov, $vEnt, $vCC)
   "Total Cost" = "Headcount" * "Rate";
ENDFIX
"""
println "--- Print Calc ---"
println calcScript
return calcScript

There you have it. Very simple, but also very useful. By leveraging the edited cells from the grid, you can make your Calc Script more targeted and efficient. Instead of processing everything within the FIX, you’re only working with the data that actually changed. This can help reduce unnecessary block processing and improve overall calculation performance, especially as the application grows and the amount of data increases.

//Declare lists to collect the members 
def lstEnt = []
def lstCC = []
//Collect edited cell by iterating a grid
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
    lstEnt << it.getMemberName("Entity")
    lstCC  << it.getMemberName("Cost Center")
}
//Declare Strings to store unique members
String vEnt = lstEnt.unique().collect { '"' + it + '"' }.join(",")
String vCC  = lstCC.unique().collect { '"' + it + '"' }.join(",")
String vPov = operation.grid.pov.findAll { !(it.dimName in ["Entity", "Cost Center"]) }
                               .collect { '"' + it.essbaseMbrName + '"' }.join(",")
//Call calc script
String calcScript = """
FIX ($vPov, $vEnt, $vCC)
   "Total Cost" = "Headcount" * "Rate";
ENDFIX
"""
println "--- generated calc ---"
println calcScript
return calcScript

Leave a comment