EPM GROOVY – How to Perform On The Fly Webform Level Custom Calculations Before Saving the Form

•

Raja Stalvin

•

The majority of us executes groovy rules in ‘Run Before Save’ operation to check if user-entered data fits all of the validation requirements. If the data does not meet the conditions, we have the option to cancel the ‘Save’ action. We’ll utilize the same “Run Before Save” groovy business rules and perform simple calculations on the form itself by modifying the cell values. The system will save the data to Essbase assuming it has been changed by the users, but we are modifying the cells using Groovy formulae.

High Level Steps:

  1. Identify the member to be calculated and make it read only using ‘Run After Load’ business Rule
  2. Perform calculations on the form before the ‘Save’ operation

FORM LAYOUT

I have created a simple form with ‘Units’, ‘Average Price’ and ‘Hardware Revenue’ members. The idea is to calculate the ‘Hardware Revenue’ without using Essbase calc scripts or custom dataGridBuilder.

MAKE ROWS READ-ONLY USING GROOVY

As demonstrated in the form template screenshot above, the Hardware Revenue metric is made ‘Read Only’ using a Groovy business rule executed as ‘Run After Load.’ We’ll make the cells editable while we calculate ‘Hardware Revenue’ in the form using another business rule.

The code that turns ‘Hardware Revenue’ into Read-Only Cells is as follows:

operation.grid.dataCellIterator('Hardware Revenue').each { DataCell cell ->
	cell.setForceReadOnly(true)           
}

CALCULATE DATA ON THE FORM

If any of the dependent cells are modified, the code below will populate the data for ‘Hardware Revenue.’ There are two things to remember when using dataCellIterator.

  1. Am I processing more cells than I need to?
  2. Is the grid built with all of the members I require?

Using the ‘crossDimCell’ function, we will traverse only the ‘Hardware Revenue’ cells and retrieve the references of dependent cells. If the provided member is not available in the grid, the crossDimCell method fails. If you don’t want the dependent members to be visible in the form for users to update, you can add them as ‘Hidden Rows.’, dataCellIterator traverses hidden cells and gets the data.

Groovy code is as follows:

operation.grid.dataCellIterator('Hardware Revenue').each { DataCell cell ->	
    
    //capture dependendant member cells using cross dim cel function
    DataCell units = cell.crossDimCell('Units')
	DataCell averagePrice = cell.crossDimCell('Avg Price')    
	
    //check if one or more dependendant member cells edited by users
    if(units.edited || averagePrice.edited) {  
    	cell.forceEditable = true
        Double calculated = averagePrice.data * units.data  
        
        //make sure the calculated values are valid numbers
        //make cell to numeric Zero since #Missing is not a valid option
		if(calculated.isNaN() || calculated.isInfinite()){
        	cell.data = 0
        } else {
        	cell.data = calculated
        }                          
    }		
}
Validation:

Before Update:

Modify Units and Avg Price:

After ‘Save’:

Exception:

When dealing with divisions, Nan & Infinite occurs when the provided value contains zeros. It’s critical to double-check and make exceptions. otherwise your form looks like this. I modified the above formula to ‘Units / Avg Price’ to replicate the issue.

This feature has not been tested with security, valid intersections, or cell level security enabled cells. Due to the lack of documentation on the subject, please proceed at your own risk. I hope you find this content useful.

«
»
, , , , , , , ,