Groovy – Use Force to Edit Read-Only Datacells

In this simplified scenario, suppose you have following form where you have products in the rows and Measures in the columns.

Revenue is a dynamic calc member calculated by multiplying Unit and Price members with outline aggregation. Revenue is instantly visible when we enter Unit and price at level 0 members of product dimension. Actually trouble is it would be still instantly visible if I aggregate Unit and Price members to All Product level. In other word, All Product Unit would be 400 + 500 + 800 = 1700, All Product Price would be 5 + 7 + 7 = 19, All Product Revenue would be 1700 X 19 = 32300 which is not true as it should be 2000 + 3500 + 5600 = 11100. Well, this is an artificial scenario and there would be multiple different ways to resolve similar situation, please take this example to see this specific option where we can manipulate the read-only cell.

operation.grid.dataCellIterator('OEP_All Product').each{
   it.setForceEditable(true)
}

Above code opens up All Product level and makes it writeable. Just deployed the rule and tagged as run on load for the form.

Now, as we have writeable cells, we can calculate sum of Unit and Revenue and enter to the cells.

def Total_Unit = 0;
def Total_Revenue = 0;
operation.grid.dataCellIterator('Unit').each{Total_Unit+=it.data}
operation.grid.dataCellIterator('Revenue').each{Total_Revenue+=it.data}

This script will calculate Total Unit and Total Revenue numbers. Rest of the script is to write those at All Product level.

operation.grid.dataCellIterator('OEP_All Product').each{
  it.setForceEditable(true)
  if(it.getMemberName('Measures')=="Unit"){
    it.setData(Total_Unit)
  }else if(it.getMemberName('Measures')=="Revenue"){
    it.setData(Total_Revenue)
  }else if(it.getMemberName('Measures')=="Price"){
    it.setData(Total_Revenue/Total_Unit)
  }
  it.setForceEditable(false)
}

.setData() method does the trick to push the value we want to present in a particular cell. .setFroceEditable(false) at the end of the script makes the cells back to read only so users may not attempt to change again. If you notice Price is now calculated as a blended Price at All Product level with the Formula (Total_Revenue/Total_Unit) (11100/1700=6.53)

You can manipulate Read-only cells in the forms. Cells could be upper level members, dynamic calc members as in this example or even formula cells could be manipulated and used in the scripts. There would be use cases like you can allow an entry at an upper level and allocate the numbers below in ASO cubes for instance, this can be another post for future.

May be the force with you…