EPM GROOVY – How To Convert SubVar / Scenario Month and Year Values To Date Objects and Compare

Java and Groovy both offer a wide range of options for creating, parsing, converting, and comparing date objects, which helps to address a number of use cases. The issue in EPM is that we either directly access the month and year values from the Scenario start end end period objects or save them in the substitution variables but not as Date objects.

One requirement that came to mind was that users must be able to enter a specific business’s Open and Close dates within the time frame of the current scenario. For instance, if Jan and FY22 are the start month and year subvar values, users shouldn’t be able to enter an open date prior to Jan and FY22. To solve the problem, we had used a number of the built-in date functions in the calc manager. We were unable to stop data entry prior to the groovy era because we didn’t have the ‘Run Before Save’ option.

The same actions would be done in Groovy by following these steps:

Get the year and period start and end values from the scenario or the values of the subvariables
Create Date objects from the Start and End Year values
Read the DataCell’s Date date type as a Date from the webform
To evaluate the input, compare the value of the user-entered date object with the scenario’s date objects.

Reading Scenario’s Start and End Period Values

Using the toMap() function, we retrieve the member attributes and read the contents.

Cube cube = operation.application.getCube('planType')
Member scenarioMbr = operation.application.getDimension('Scenario', cube).getMember('OEP_Forecast', cube)
Map scenarioProp =  scenarioMbr.toMap()
Closure getValue = { String key -> scenarioProp.find {it.key == key}?.value as String}
String fcstStartYear = getValue('Start Year')
String fcstStartMonth = getValue('Start Period')
String fcstEndYear = getValue('End Year')
String fcstEndMonth  = getValue('End Period')

Reading SubVar Values for Start and End Period

We wouldn’t use the SubVar values as well as the year and period variables from the Scenario. This is only a demonstration of how to get information from both things.I appreciate how Closures may help us avoid duplicating the scripts, which saves us a lot of time.

Closure subVarValue = { String subVar -> operation.application.getSubstitutionVariable(subVar).value }
String fcstStartYear = subVarValue('FCSTStartYr')
String fcstStartMonth = subVarValue('StartMonth')
String fcstEndYear = subVarValue('FcstEndYr')
String fcstEndMonth  = subVarValue('EndMonth')

Here is the printed log from job console:

Convert String To Date Objects

We usually would be having FY in the year values, I would use the below Closure to convert the EPM year value to calendar year string value.

Let us create Date objects:

Closure finYearToCal = { String it -> '20' + it.drop(2)}
Date scenarioStartDate = Date.parse("yyyy-MMM", "${finYearToCal(fcstStartYear)}-$fcstStartMonth")
Date scenarioEndDate = Date.parse("yyyy-MMM", "${finYearToCal(fcstEndYear)}-$fcstEndMonth")

Here is the printed log from job console:

Validate The Date Data Type User Input

The goal is to see if the user has entered an open and closing date that falls within the Start and End Date range. The closing date shouldn’t be earlier than the open date either.

Closure itrFilter = { DataCell cell -> cell.accountName == 'Open Date' && cell.edited && !cell.missing }
operation.grid.dataCellIterator(itrFilter).each { DataCell cell ->	
	Date editedDate = cell.dataAsDate      
	//check if the dates are between scenario open year & period range
	if (editedDate.after(scenarioStartDate) && editedDate.before(scenarioEndDate)){
		Date closeDate = !cell.crossDimCell('Close Date').missing ? cell.crossDimCell('Close Date').dataAsDate : null
		//check if the open date is before close date
		if(closeDate && editedDate.after(closeDate)){
			throwVetoException("Open Date cannot be greater than the close date")
		} 
	} else {
		throwVetoException("$cell.accountName input is allowed only between ${scenarioStartDate.format('YYYY-MMM')} and ${scenarioEndDate.format('YYYY-MMM')}")
	} 
}

The rule has been attached as ‘Run Before Save’. Below are the validation.

Case 1: Inputting Open Date before the Start Year and Period range