EPM GROOVY – How To Create Re-usable Custom Month and Year Ranges in Groovy

Month and Year dimensions play a major role in most of the calculations. If it is a single-year app, it is easier to handle the month and year ranges inside our fix statements, but when it comes to multiple-year planning or forecasting, it is required to have multiple IF conditions inside FIX statements.

These additional IF checks for the valid months and year can be performed in the Groovy layer, and we should be able to create separate FIXes depending on the number of years that we are planning to execute the calculations.

There are two major advantages to this approach:

1. We can see the years and periods for which the calculation is being executed
2. Can get rid of additional Essbase member/function check

In short, we are going to hardcode the valid year and month ranges in the calculation scripts dynamically.

The summary of the steps I follow is:

1. Create a Month and Year class implementing Comparable interface
2. Get the current scenario user variable value
3. Fetch start and end period/year values from scenario members
4. Create the static fix for future calculations

Create the Custom Range Class

As we are implementing the Comparable interface, we need to define next(), previous(), and compareTo() methods in a class.

We take month as a String, hence it is required to create the custom constructor instead of using the Groovy’ s default constructor. For now, I have hardcoded the months in the “MONTHS” range, but they can be fetched from the outline too. I don’t want to overcomplicate things here. Similar way you can create a class for Year too.

Note: One should be careful when choosing class names; it would be best to choose something distinctive. If Oracle creates their own Month class, I’m not sure how the system will respond.

class Month implements Comparable<Month> {
    static final List<String> MONTHS = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul','Aug','Sep','Oct','Nov','Dec']
    private int index = 0

    Month (String month) {
        index = MONTHS.indexOf(month)
    }

    Month next() {
        new Month(MONTHS[(index+1) % MONTHS.size()])
    }

    Month previous() {
        new Month(MONTHS[index-1])
    }

    int compareTo(Month other) {
        this.index <=> other.index
    }
    String getName(){
        MONTHS[index]
    }
}

Let us create Month objects and Range<Month> object:

Month startMonth = new Month('Jan')
Month endMonth = new Month('Mar')
Range<Month> monthRange = startMonth..endMonth
monthRange.each {println it.name }

Output:

The compareTo method creates a reverse range if the start and end months are messed up. The Java Development Kit (JDK provides a method to check if the range is reversed or not. Let’s check if we are able to throw an exception. Also, it provides the previous and next members since we have already defined them.

Month startMonth = new Month('Mar')
Month endMonth = new Month('Jan')
Range<Month> monthRange = startMonth..endMonth

monthRange.each {println it.name }

if(monthRange.isReverse()) {
    throw new Exception('ERROR: Start month comes after end month')
}

Output:

Finally, it is time to create the calculation manager template.

Adding Final Pieces

In the beginning of this post, I mentioned we would be fetching the values from scenario members. Please visit one of my previous post to get the start and end period range. For this example, I am going to use DTPs to get the start and end periods.

Defining exceptions and the full script:

//Define Start and End Period objects
def(Month periodStart, Month periodEnd) = [new Month(startMonthUserInput), new Month(endMonthUserInput)]
def(Year yearStart, Year yearEnd) = [new Year(startYearUserInput), new Year(endYearUserInput)]

//Generate month and year Range objects
Range<Month> monthsRange = periodStart..periodEnd
Range<Year> yearsRange = yearStart..yearEnd

//Throw error if start year is reversed
if(yearsRange.isReverse()){
	throwVetoException("ERROR: Start Year : $yearStart.name cannot be greater than End Year : $yearEnd.name ")
}

Map<Year, List<Month>> validYearMonths = [:]
yearsRange.each { year ->
	if(yearsRange.size() == 1) {
    	if(monthsRange.isReverse()) {
        	throwVetoException("ERROR: Start Month : $periodStart.name cannot be greater than End Month : $periodEnd.name")
        } else {
        	validYearMonths[(year)] = monthsRange
        }
    } else {
    	if(year == yearsRange.first()) {
        	validYearMonths[(year)] = periodStart..(new Month('Dec'))
        } else if ( year == yearsRange.last()) {
        	validYearMonths[(year)] = (new Month('Jan'))..(periodEnd)
        } else {
        	validYearMonths[(year)] = (new Month('Jan'))..(new Month('Dec'))
        }
    }
}


validYearMonths.each {Year year, List<Month> months ->
	println ("""
    	FIX(${cscPrams(year.name)}, ${cscParams(months*.name)})
        	/*Your Code here*/
		 ENDFIX
	""")    
}

Execution

Case 1: Single Year and Valid Month Range

Log:

Case 2: Single Year and Invalid Month Range

Log:

Case 3: Valid Multi-Year Range

Log:

Case 4: Invalid Multi-Year Range

Log:

Conclusion:

With the use of collections, iterators, and indexes, the aforementioned scripts can be created in a matter of lines. The purpose is not to really care about handling null pointer exceptions, but rather to establish a standard template that can be used across applications. I hope this information is useful.

Share this post: on LinkedIn