Prototype Using Groovy Rules for Entering Data to the FCCS_YTD Member

Entering and displaying Year-to-Date (YTD) data in a Financial Consolidation and Close form typically requires two View members to be included in the form design: YTD_Input and YTD. This requirement can make form design complex. The example below leverages Groovy script to eliminate this requirement and only include the YTD View member in the form, and the Groovy script then manages the input data correctly. Using this method allows for more manageable form design and display.

To create Groovy rules for entering data directly to the FCCS_YTD member:

1. Add an On-Demand rule in Calculation Manager named "PreFormLoad".

2. Change the Script Type of the On-Demand rule to "Groovy Script".

3. Add the following code to the "PreFormLoad" rule:

   operation.grid.dataCellIterator("FCCS_YTD").each { DataCell cell -> if (!cell.crossDimCell("FCCS_YTD_Input") && cell.isReadOnly())cell.forceEditable=true}

4. Save and validate the "PreFormLoad" rule.

5. Provide default Run-Time Prompt values to the "PreFormLoad" rule. as shown below:

   • Consolidation: "Entity Input"

   • Currency: "Entity Currency"

   • Entity: "FCCS_Global Assumptions"

   • Period: "Jul"

   • Scenario: "Actual"

   • Years "FY19"

6. Check the Is Hidden and Use as Override Value options for all six Run-Time prompts for the "PreFormLoad" rule.

7. Save, validate and deploy the "PreFormLoad" rule.

8. Add an On-Demand rule in Calculation Manager named "PreFormSave".

9. Change the Script Type of the On-Demand rule to "Groovy Script".

10. Add the following code to the "PreFormSave" rule:

    def app = operation.application
    Cube cube = operation.cube
    Member ytdInputMember =
    app.getDimension(DimensionType.VERSION).getMember("FCCS_YTD_Input")
    Collection<DataCell> pendingYtdCells =
    operation.grid.dataCellIterator("FCCS_YTD").findAll {DataCell ytd -> if (ytd.edited) {
    def ytdInput = ytd.crossDimCell(ytdInputMember.name)
    ytdInput?.formattedValue = ytd.formattedValue
    ytd.setEdited(false)
    return ytdInput == null
    }
    }
    if (pendingYtdCells) {
    DataGridBuilder builder = cube.dataGridBuilder("MM/DD/YYYY")
    Set<String> allDimNames = set(app.getDimensions(cube)*.name)
    List<String> rowDimNames = set((allDimNames -
    operation.grid.pov*.dimName) - [ytdInputMember.dimension.name]) as List
    builder.addPov(operation.grid.pov.findAll({it.dimName !=
    ytdInputMember.dimension.name})*.uniqueMbrName as String[])
    builder.addColumn(ytdInputMember.name)
    pendingYtdCells.each {DataCell cell ->
    builder.addRow(rowDimNames.collect
    {cell.getMemberName(it, MemberNameType.UNIQUE_NAME)},
    [cell.formattedValue])}
    DataGridBuilder.Status status = new DataGridBuilder.Status()
    builder.build(status).withCloseable {grid -> cube.saveGrid(grid)}
    }
    Set<String> set(Collection<String> names) {
    Set<String> set = new TreeSet<>(String.CASE_INSENSITIVE_ORDER)
    set.addAll(names)
    return set
    }
    

11. Save and validate the "PreFormSave" rule.

12. Provide default Run-Time Prompt values to the "PreFormSave" rule. as shown below:

    • Consolidation: "Entity Input"

    • Currency: "Entity Currency"

    • Entity: "FCCS_Global Assumptions"

    • Period: "Jul"

    • Scenario: "Actual"

    • Years "FY19"

13. Check the Is Hidden and Use as Override Value options for all six Run-Time prompts for the "PreFormSave" rule.

14. Save, validate and deploy the "PreFormSave" rule.

15. Add a form named "YTD_Test_1" with "FCCS_Periodic" and "FCCS_YTD" on the rows, as shown below:

    Sample YTD form

    
    
    

16. Click on the Business Rules tab and move "Consol – Rule – PreFormSave" and "Consol – Rule – PreFormLoad" to the right side. Check Run Before Save for the "Consol – Rule – PreFormSave" rule, and check Run After Load for the "Consol – Run – PreFormLoad" rule. The screen should display as below:

    Sample Business Rules

    
    
    

17. Save and click Finish.

    Note: These steps will mark the "Actual"/ "FY19" "Jul" "FCCS_Global Assumptions" data as Changed. Make sure the process unit is started and is in Unlocked state. This is applicable only for this prototype. The actual implementation will not have these restrictions.

18. Open the "YTD_Test_1 form" and add values to FCCS_YTD as shown below:

    Sample YTD Values

    
    
    

19. Save the form. FCCS_Periodic will be populated with the corresponding Periodic values, as shown below:

    Sample Populated YTD Form