Conditional Data Export with Groovy

This post is around Oracle EPM Cloud Planning.

I’ve recently had this challenge to give to some key users the ability to generate export files with data that would be manually flagged by users.

This may be a similar case around you, so get ready for this new Groovy seed!

Get ready for the start

Let’s suppose I have an input data form like this:

I would then add 2 more rows:

• 1 in Read-only mode to check on the flag “Ready to be sent to TARGET”. This one would be automatically updated by a Groovy rule “Reset Ready to be sent to TARGET”
• 1 referring to a manual status to be updated by the users. Let’s assume here we have 3 possible statuses within a Smartlist: Complete/Review needed/Reviewed

Whenever the status cells are updated to Complete:

Then the “Ready to be sent to TARGET” flag would be updated thanks to a first Groovy rule “Reset Ready to be sent to TARGET”, right after hitting the Save button:

With the help of an action menu associated to a second Groovy rule “Export flagged data only”, we would manually trigger the conditional export of the data:

Hopefully, I am getting some good news:

Checking on the job console give us some more information (if too many information in the job console, kindly refer to the previous post related to the 20k characters limitation that is possible to work around ):

In my case, I had to clear the flags, so after having triggered the rule from the action menu, the form displays as below:

Bill Of Materials : what is needed

The BOM to implement this feature is the following:

• 2 dimension members “Ready to be sent to TARGET” and “Status” on the Accounts dimension
• 1 Data form:

“Ready to be sent to TARGET” in Read-only mode, pointing to a Yes/No smartlist, updated with a Groovy rule attached with option “Run After Save” “Status” in Write mode, pointing to a Complete/Review needed/Reviewed (or whatever you like), manually updated by users. Eventually, leverage on security to restrict this row to any group of users.

• 2 Groovy rules:

One that automatically updates flags on save, let’s call it “Reset Ready to be sent to TARGET” One that triggers the conditional export of data, let’s call it “Export flagged data only”

The Groovy rules are the following:

String sEntity
double dStatus
def lstYearForStatusComplete = []
def lstPeriodForStatusComplete = [] 
def lstYearForStatusDifferentFromComplete = [] 
def lstPeriodForStatusDifferentFromComplete = [] // If status is blank, then flag "Ready to be sent to TARGET" should be deleted

sEntity = operation.grid.dataCellIterator()[0].getMemberName("Entity")

boolean atLeastOneCellChangedStatusComplete = false
boolean atLeastOneCellChangedStatusEmpty = false

operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
  dStatus = it.crossDimCell("Status",it.getMemberName("Period")).data
  if( dStatus == [[SLStatus.Complete]] ) { // If status is Complete, then flag "Ready to be sent to TARGET" should be equal to Yes
    lstPeriodForStatusComplete.add(it.getMemberName("Period"))
    lstYearForStatusComplete.add(it.getMemberName("Years"))
    atLeastOneCellChangedStatusComplete = true
  } else{ // Else flag "Ready to be sent to TARGET" should be equal to No
    lstPeriodForStatusDifferentFromComplete.add(it.getMemberName("Period"))
    lstYearForStatusDifferentFromComplete.add(it.getMemberName("Years"))
    atLeastOneCellChangedStatusEmpty = true
  }
}
println "atLeastOneCellChangedStatusComplete=$atLeastOneCellChangedStatusComplete"
println "atLeastOneCellChangedStatusEmpty=$atLeastOneCellChangedStatusEmpty"

String scriptBldr
if(atLeastOneCellChangedStatusComplete || atLeastOneCellChangedStatusEmpty) {
  scriptBldr = "FIX(\"Working\",\"$sEntity\", \"Direct_Entry\", \"Ready to be sent to TARGET\")\n"
  if(atLeastOneCellChangedStatusComplete) {
    for (def i=0; i<lstPeriodForStatusComplete.size(); i++){
        scriptBldr += "\tFIX(\"${lstYearForStatusComplete[i].toString()}\", \"${lstPeriodForStatusComplete[i].toString()}\")\n"
        scriptBldr += "\t\t\"Forecast\"=1;\n"
        scriptBldr += "\tENDFIX\n"
      }
  }
  if(atLeastOneCellChangedStatusEmpty) {
    for (def i=0; i<lstPeriodForStatusDifferentFromComplete.size(); i++){
        scriptBldr += "\tFIX(\"${lstYearForStatusDifferentFromComplete[i].toString()}\", \"${lstPeriodForStatusDifferentFromComplete[i].toString()}\")\n"
        scriptBldr += "\t\t\"Forecast\"=#Missing;\n"
        scriptBldr += "\tENDFIX\n"
      }
  }
  scriptBldr += "ENDFIX\n"
  println "Following script will be executed :\n"
  println scriptBldr
  return scriptBldr
}

“Reset Ready to be sent to TARGET” Groovy rule

// 0 - Initialisation
Cube cube = operation.application.getCube("VISION")
String MeasureReadyToBeSentToTARGET = "Ready to be sent to TARGET"
String MeasureStatus = "Status"
String povDataElement = "Direct_Entry"
String povScenario = "Forecast"
String povVersion = "Working"

String CurrentYear = operation.application.getSubstitutionVariable("CurrentYear").getValue()
String NextYear = operation.application.getSubstitutionVariable("NextYear").getValue()
def listOfYears = [CurrentYear,NextYear]

def listOfPeriods = 'Lvl0Descendants("YearTotalM")'

def listOfEntities = 'Lvl0Descendants("Total_Organisation")'

def listOfAccounts = 'Lvl0Descendants("GP")'

def List<String> listOfEntitiesToExport = []
def List<String> listOfMonthsToExport = []
def List<String> listOfYearsToExport = []

// 1 - Preparation of the grid to reset the flags. This grid will be effectively A) filled in step 2 and B) built in step 4 
DataGridBuilder readyToBeSentToTargetGrid = cube.dataGridBuilder("MM/DD/YYYY")
readyToBeSentToTargetGrid.addPov(povDataElement, povScenario, povVersion)
readyToBeSentToTargetGrid.addColumn(MeasureReadyToBeSentToTARGET)

// 2 - Read intersection with flags, and store triplets Year/Month/Entity in lists where flags = Complete
DataGridDefinitionBuilder DGDB_IntersectionWithFlags = cube.dataGridDefinitionBuilder()
DGDB_IntersectionWithFlags.addPov(['Data Element','Scenario','Version'],[[povDataElement],[povScenario],[povVersion]])
DGDB_IntersectionWithFlags.addColumn(['Measure'], [[MeasureReadyToBeSentToTARGET, MeasureStatus]])
DGDB_IntersectionWithFlags.addRow(['Years','Period','Entity'], [listOfYears,[listOfPeriods],[listOfEntities]])

DGDB_IntersectionWithFlags.setSuppressMissingRows(true)
DataGridDefinition DGD_IntersectionWithFlags = DGDB_IntersectionWithFlags.build()
DataGrid DG_IntersectionWithFlags = cube.loadGrid(DGD_IntersectionWithFlags, false)

def flagStatus, flagReadyToBeSentToTarget
String entityCode, periodCode, yearCode, measureCode, previousEntityCode = "*", previousPeriodCode = "*"
int numberOfRowsToExport = 0

DataGridBuilder DGB_readyToBeSentToTargetGrid = cube.dataGridBuilder("MM/DD/YYYY")
DGB_readyToBeSentToTargetGrid.addPov(povDataElement, povScenario, povVersion)
DGB_readyToBeSentToTargetGrid.addColumn(MeasureReadyToBeSentToTARGET)

DG_IntersectionWithFlags.dataCellIterator.each{
  yearCode = it.getMemberName("Years")
  periodCode = it.getMemberName("Period")
  entityCode = it.getMemberName("Entity")
  measureCode = it.getMemberName("Measure")
  if( (previousEntityCode == entityCode) && (previousPeriodCode != periodCode) )
  {
    flagStatus = null
    flagReadyToBeSentToTarget = null
  }
  if( (it.getMemberName("Measure") == MeasureReadyToBeSentToTARGET))
    flagReadyToBeSentToTarget = it.crossDimCell(entityCode,MeasureReadyToBeSentToTARGET,periodCode,yearCode).data
  if( (it.getMemberName("Measure") == MeasureStatus) ) 
    flagStatus = it.crossDimCell(entityCode,MeasureStatus,periodCode,yearCode).data
  if(previousEntityCode == entityCode) {
    if(flagStatus == [[SLStatus.Complete]]) {
      if( (flagReadyToBeSentToTarget == [[SLYesNo.Yes]]) ) {
        listOfYearsToExport.add(yearCode)
        listOfMonthsToExport.add(periodCode)
        listOfEntitiesToExport.add(entityCode)
        numberOfRowsToExport += 1
        readyToBeSentToTargetGrid.addRow([ entityCode, yearCode, periodCode ], ["#Missing"])
      }
    }
  }
  previousEntityCode = entityCode
  previousPeriodCode = periodCode
}

println "numberOfRowsToExport=$numberOfRowsToExport"

// 3 - Export Gross Profit data in CSV file
DataGridDefinitionBuilder DGDB_ExportCSV = cube.dataGridDefinitionBuilder()
DGDB_ExportCSV.addPov(['Data Element','Scenario', 'Version'],[[povDataElement],[povScenario],[povVersion]])
DGDB_ExportCSV.addColumn(['Measure'], [ ['GP'] ])
DGDB_ExportCSV.addRow(['Years','Period','Entity'], [listOfYearsToExport,listOfMonthsToExport,listOfEntitiesToExport])
DGDB_ExportCSV.setSuppressMissingRows(true)
DataGridDefinition DGD_ExportCSV = DGDB_ExportCSV.build()
DataGrid DG_ExportCSV = cube.loadGrid(DGD_ExportCSV, false)

String ExportCSVFileName = "ConditionalDataExport.csv"

println "[see in Application > Overview > Inbox/Outbox Explorer > $ExportCSVFileName]\nCSV File (begin)"
csvWriter("$ExportCSVFileName",',').withCloseable() { out ->
  DG_ExportCSV.dataCellIterator.each{
    yearCode = it.getMemberName("Years")
    periodCode = it.getMemberName("Period")
    entityCode = it.getMemberName("Entity")
    out.writeNext yearCode,periodCode,entityCode,it.getFormattedValue()
    println "$yearCode,$periodCode,$entityCode,${it.getFormattedValue()}"
  }
}
println "CSV File (end)\n"

// 4 - Reset flags
DataGridBuilder.Status statusReadyToBeSentToTargetGrid = new DataGridBuilder.Status()
String readyToBesentToTargetGridValidationMessage
readyToBeSentToTargetGrid.build(statusReadyToBeSentToTargetGrid).withCloseable { grid ->
  println("Total number of cells accepted: $statusReadyToBeSentToTargetGrid.numAcceptedCells/$numberOfRowsToExport")
  println("Total number of cells rejected: $statusReadyToBeSentToTargetGrid.numRejectedCells/$numberOfRowsToExport")
  println("First 100 rejected ($statusReadyToBeSentToTargetGrid.numRejectedCells/${statusReadyToBeSentToTargetGrid.numAcceptedCells+statusReadyToBeSentToTargetGrid.numRejectedCells}) cells: $statusReadyToBeSentToTargetGrid.cellsRejected")
  readyToBesentToTargetGridValidationMessage = (statusReadyToBeSentToTargetGrid.numRejectedCells == 0) ? "$numberOfRowsToExport/$numberOfRowsToExport flags reset properly !": "$statusReadyToBeSentToTargetGrid.numRejectedCells/$numberOfRowsToExport flags NOT reset properly"
  cube.saveGrid(grid)
}
println readyToBesentToTargetGridValidationMessage

“Export flagged data only” Groovy rule

By the way, quick tip on Calculation Manager whenever you would like to write the same piece of code in multiple places (consolation prize for missing Ctrl+H option!):

This can be adapted in many other ways: the CSV file could be sent by email (REST API can be your friend), there could be additional functional requirements to have more conditions to check around the flags, the format of the CSV file could be adapted to almost anything thanks to the csvWriter class…

Well. Yet another seed that may grow your EPM dreams…