We did see how to perform this check on an On-Premises version of Hyperion Planning. Now, let’s look at how this can be enhanced using Groovy on an Enterprise Planning instance.
I’m using the Sample Vision application, and the form in question is “Revenue Plan – Revenue Assumptions.”
We are trying to prevent users from entering non-integer units. Groovy Planning rules in Enterprise Planning allows you not to save the data if there is a validation error.
Groovy rule
If the user enters a non-integer value to Units, the script will capture those invalid intersections and show that in the job console.
I thought it is going to be a global application and wanted to show error messages based on the user’s region. You can tailor the exception messages based on the locale using GroovyMessageBundles and GroovyMessageBundleLoader.
// Messages for showing the user
def enMsgs = messageBundle(["validation.noninteger.units":"Units are entered as whole numbers"])
def frMsgs = messageBundle(["validation.noninteger.units":"Les unités sont entrées sous forme de nombres entiers"])
def deMsgs = messageBundle(["validation.noninteger.units":"Einheiten werden als ganze Zahlen eingegeben"])
MessageBundle is a Map, and it can accept multiple entries, if this is a rule where you are checking for multiple errors, then you can add more checks into the bundle as follows.
def enMsgs = messageBundle(["validation.noninteger.units":"Units are entered as whole numbers"], ["validation.multiplesOfFiveTarget":"Targets are entered as multiples of five."])
Once you have all the message bundles, it’s time to load them.
def msgBundler = messageBundleLoader(["en":enMsgs, "fr":frMsgs, "de_CH":deMsgs])
Now I’m going to loop through the edited cells and find out whether there are any non-integer numbers entered against Units.
def errorLines = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
if (! isValidInteger(it.crossDimCell("Units").data.toString())){
errorLines << "${it.memberNames.join(",")}"
it.setBgColor(0xE65656)
it.setTooltip("Units are entered as whole numbers.")
}
}
You might be wondering why I used a custom function for checking whether the number is an integer rather than using the isInteger function. If you look at the isInteger and the value that you are passing is 154.0 (which will be the case as Planning treats the numbers as Doubles), the check will fail because the string has a period in it.
Custom isInteger function
This is a good example of using try-catch in Groovy, what I’m trying to do is to make use of the toBigIntegerExact method of BigDecimal, if the decimal got a non-zero fractional part, an exception is thrown.
def isValidInteger(String numberToCheck) {
boolean isValidInteger = false
try
{
// try to find whether this is a real integer (even the ones with .0
new BigDecimal(numberToCheck).toBigIntegerExact()
// numberToCheck is a valid integer
isValidInteger = true
}
catch (ArithmeticException ex)
{
// numberToCheck is not an integer
}
return isValidInteger
}
Once the check is done, all non-integer cells are painted red, and a tooltip is added.
The last piece of this is to add the veto exception and return the localized message to the user.
if (errorLines.size() > 0){
println errorLines.join("\n")
throwVetoException(msgBundler, "validation.noninteger.units")
}
I’m checking whether the list has any entries. If there is an error, the list size will be greater than 0, use the message bundle, select the appropriate message, and voila.
Here is the script in action.
What happens if you move away from the form?
Yup, that data is not saved 🙂
If you want to look at the intersections, you can view that from the Job Console.
Now, what about a French user?
I didn’t do anything to get the above screens, that’s Planning doing what it is best at doing. 🙂
Here is where our French message bundle kicks in.
Full Code
// Messages for showing the user
def enMsgs = messageBundle(["validation.noninteger.units":"Units are entered as whole numbers"])
def frMsgs = messageBundle(["validation.noninteger.units":"Les unités sont entrées sous forme de nombres entiers"])
def deMsgs = messageBundle(["validation.noninteger.units":"Einheiten werden als ganze Zahlen eingegeben"])
def msgBundler = messageBundleLoader(["en":enMsgs, "fr":frMsgs, "de_CH":deMsgs])
def errorLines = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
if (! isValidInteger(it.crossDimCell("Units").data.toString())){
errorLines << "${it.memberNames.join(",")}"
it.setBgColor(0xE65656)
it.setTooltip("Units are entered as whole numbers.")
}
}
if (errorLines.size() > 0){
println errorLines.join("\n")
throwVetoException(msgBundler, "validation.noninteger.units")
}
def isValidInteger(String numberToCheck) {
boolean isValidInteger = false
try
{
// try to find whether this is a real integer (even the ones with .0
new BigDecimal(numberToCheck).toBigIntegerExact()
// numberToCheck is a valid integer
isValidInteger = true
}
catch (ArithmeticException ex)
{
// s is not an integer
}
return isValidInteger
}