EPM GROOVY – Capturing User Edited Rows for Dynamic and Focused Calculation Processing

This is one of the first ever features, and it’s frequently utilized in most Hybrid Business Rules ( Groovy + Business Rule ) to collect the edited members from the form and execute the calculation exclusively to them. We create a Set and capture all the changed cells as shown below to collect only one dimension member from the form:

Groovy
Set<String> editedProducts = []
//loop through edited cells
operation.grid.dataCellIterator({ DataCell cell -> cell.edited }).each { DataCell cell ->
    editedProducts << (cell.getMemberName('Products'))
}
assert '"Products"' == cscParams(editedProducts)

The same way if we have to get multiple members, one of the simplest way is to create a Set of List and follow the same process.

Groovy
Set<List<String>> editedProducts = []
//loop through edited cells
operation.grid.dataCellIterator({ DataCell cell -> cell.edited }).each { DataCell cell ->
    editedProducts << ([cell.accountName, cell.scenarioName, cell.getMemberName('Products')])
}
assert '"Sales Retail","Budget","Products"' == cscParams(editedProducts)

I prefer DataGrid.Row interface than dataCellIterator since the number of additional methods which helps when we write hybrid business rules. Let’s take a look at how to utilize “DataGrid.Row” in the example below.

Filter Edited Rows & Process Members

The idea is to find all the rows if any one of the cell is edited and get their header cells. The same can be translated to groovy codes as below:

Groovy
//find edited rows as below - Flag true if any one of the cell is edited in a row
List<DataGrid.Row> editedRows = operation.grid.rows.findAll{it.data.any{it.edited}}

I use “cscParams” a lot because it’s a strong function that can be inserted into any hybrid rules. It takes each member, puts double quotes around it, and returns comma separated values, which is exactly what we want.

Getting the headers from the row components helps us to utilize additional methods. I am going to demonstrate how easy is to filter the members using below conditions:

  • Filter dynamic calc members
  • Filter Sparse & Dense Members
  • Filter Parent & level zero members
Groovy
//loop through the edited rows and fetch all members
editedRows.each { DataGrid.Row row ->
    //get All Row Member Names
    List<DataGrid.HeaderCell> rowHeaders = row.headers
    assert '"Sales Retail","Budget","Products"' == cscParams(rowHeaders)
    //get only sparse Members from a row
    List<DataGrid.HeaderCell> sparseRowMembers = row.headers.findAll { it.sparse }.collect { it }
    assert '"Budget","Products"' == cscParams(sparseRowMembers)
    //exclude parent members from the list
    List<DataGrid.HeaderCell> level0RowMembers = row.headers.findAll { !it.zoomable }.collect { it }
    assert '"Sales Retail","Budget",' == cscParams(level0RowMembers)
    //exclude dynamic calc members, implicit shared members, attribute Members
    List<DataGrid.HeaderCell> calcableMembers = row.headers.findAll { it.calcableForPOV }.collect { it }
    assert '"Sales Retail","Budget","Products"' == cscParams(calcableMembers)
}

If we use ‘DataCellIterator,’ the conditions must be written separately. Why not make the most of the built-in capabilities of the ‘DataGrid.Row’?