EPM GROOVY – How to Replace Form Level Formula Rows/Columns With Groovy’s ‘Run After Load’ Rules

When calculating simple formulas at the form level, formula rows and columns prove to be very helpful. I haven’t implemented formula rows or columns in my previous implementation in a few years. Using groovy rules, we can modify “any” data fields after the form has loaded thanks to the “Run After Load” functionality. Whether it is a label-only member or a dynamic calc member doesn’t really matter. Any sort of modification can be performed to the grid. Using groovy, we may create any custom formulae and edit the fields however we like, in contrast to formula rows/columns features which come with just limited functions (Average, Sum, Product, etc.).

Let’s see how to replicate the rows/columns feature using groovy.

Create a dynamic calc member without any formula
Access dynamic calc member in the form using groovy and update the values based on the requirements.

SAMPLE FORM & DYNAMIC CALC MEMBER

Webform

Product Sum – Member Property

Product Sum row appears as empty in the webform because I don’t have any Groovy rules running and also it’s a dynamic calc member without a formula.

RUN AFTER LOAD GROOVY RULE

While the form is processing, I’d want to show you how to add all the product data row by row, calculate the sum of all the rows, and display it in the “Product Sum” member. I need to exclude ‘Product Sum’ rows as it is a calculated row.

String calculatedMember = 'Product Sum'
List<List<Double>> referenceRowData = operation.grid.rows.findResults { !(calculatedMember in it.headers*.mbrName) ? it.data.collect{it.data} : null} as List<List<Double> >

I would receive the following log if I were to print each list from referenceRowData:

referenceRowData.each {
	println it
}

Log:

The issue here is that we need to add row 1->cell 1, row 2->cell 1, row 3->cell 1, etc. The transpose function comes in handy in this situation. By combining every list according to its index, it builds new lists.

List<List<Double>> transposedRow = referenceRowData.transpose()

transposedRow.each {
	println it
}

Log:

As you can see from the sample above, the data has been grouped as we wish. I purposefully entered 7M in North Channel. From here is easier to sum it up all the elements in a list.

List<Double> sum = transposedRow*.sum() as List<Double>
println sum

Log:

CHANGE VALUES IN THE FORM:

Once the total is calculated, its time to fetch the calculated row and update the values. Here are the steps I follow as its a dynamic calc member:

Enable setForceEditable – This will enable the cell for input at grid level ( data storage properties have no power here)
Update the value with the total sum – iterate with index function and updated the values accordingly
Disable setForceEditable – if not done, it will show as editable field when the form is open

Row calculatedRow = operation.grid.rows.findAll { (calculatedMember in it.headers*.mbrName) }?.getAt(0)
calculatedRow.data.eachWithIndex { DataCell cell, idx ->	    
	cell.setForceEditable(true)
	cell.data = sum[idx]     
	cell.setForceEditable(false)
}

Processing the rule doesn’t take too long. Takes <1 second for this example.

Output on the form:

Full code:

/* RTPS: */
String calculatedMember = 'Product Sum'
List<List<Double>> referenceRowData = operation.grid.rows.findResults { !(calculatedMember in it.headers*.mbrName) ? it.data.collect{it.data} : null } as List<List<Double> >
List<List<Double>> transposedRow = referenceRowData.transpose()
List<Double> sum = transposedRow*.sum() as List<Double>

Row calculatedRow = operation.grid.rows.findAll { (calculatedMember in it.headers*.mbrName) }?.getAt(0)
calculatedRow.data.eachWithIndex { DataCell cell, idx ->	    
	cell.setForceEditable(true)
	cell.data = sum[idx]     
	cell.setForceEditable(false)
}

The one that was shown is a fairly straightforward example. This allows us to display data from various cubes in a single form. For instance, we can utilize the grid builder to bring ASO data and display it in the form in a matter of seconds without the end users realizing that the data comes from a different cube or instance. I hope this information is useful.