ASO Copy Actual to Forecast
In Planning a common requirement is to actualise the forecast with the latest actuals. This blog is a groovy script that will clear the Actual set of periods from the Forecast e.g. Jan to Jun for the current year and then copy the same periods from the Actual Scenario into the Forecast. It is an extension on this:
The difference here is I need to explicitly manage which periods are cleared from the forecast and reloaded from the actuals. Often this type of script will run part way through a month and it is not necessary to copy the part month of actuals into forecast.
The illustration below shows the data from January to June being updated into the forecast scenario, July has separate flash data being loaded (not part of this blog).
In the calculation below, the additions to my previous blog are the period filters. In clearpartialdata it is:
{MemberRange([Jan],[$sMthEndPeriod])}
In the createdataexporter I use the variable actPeriods. This is a list of periods
dimension.getEvaluatedMembers(“@LIST(Jan:$sMthEndPeriod)”,cube)
As this was always going to be from actual to forecast, I hard coded those members.
Completed Script
/****************************************************************************
Name: iEPM_Copy_Act_Fcst
Desc: Copy Actuals loaded into Forecast. Replaces data load "DLR_Enc_ERP_Rpt_Clos_Bal_Fcst"
Author: Nic Vos
Date:09/10/26
Modifications: Date:Who:What
****************************************************************************/
/* RTPS: {gRTPs_Year} {gRTPs_Period} */
// Get the inputs
String sYear = rtps.gRTPs_Year.toString().replaceAll('"', '')
String sMthEndPeriod = rtps.gRTPs_Period.toString().replaceAll('"', '')
Cube cube = operation.application.getCube('FinRpt')
Dimension dimension = operation.application.getDimension('Period', cube)
List<Member> actPeriods = dimension.getEvaluatedMembers("@LIST(Jan:$sMthEndPeriod)",cube)
// Claer the target data cohort
cube.clearPartialData("Crossjoin(Crossjoin(Crossjoin({[$sYear]},{[OEP_Forecast]}),\
{[OEP_Working]}),\
{MemberRange([Jan],[$sMthEndPeriod])})",true)
cube.createDataExporter()
.setDataExportFormat(CELL_FORMAT)
.setRowFilterCriteria("OEP_Working", "OEP_Actual", sYear,actPeriods)
.exportData()
// loop through every row output and create an import request with that data row
.withCloseable { exportIterator ->
cube.createDataImportRequest()
.importData([])
// step through, creating rows for the target data cohort
.withCloseable { dataImporter ->
// loop through the properties of the row data and update the TUPLE with the target Version & Scenario
exportIterator.each {RowData rowData ->
// update the TUPLE members to have the target member names
Tuple targetMembers = rowData.rowTuple + ["Version" : "OEP_Working"] + ["Scenario" : "OEP_Forecast"]
dataImporter.addRow(createRowData(targetMembers, rowData.columnMembers, rowData.getValuesMap() ))
}
}
}