Groovy and Data Validation in EPBCS - Quick Tip

This post is about simple data form validations, preventing users form entering negative amounts in certain members, we all familiar with that request, we used to achieve this pre Hyperion Planning 11.2.2 by changing the java script files. Now thanks to EPBCS's Groovy capabilities, it is a piece of cake in the cloud! No need to locate server files or any of the hassle we used to go through, we just write a simple script and that's it!

To keep it simple and lite, I'll stick to my usual approach and use a simple form (minimized version of Product Revenue form in EPBCS's Financials dodel). The same form I used for my previous post.

I'll start with my Groovy script, it's pretty simple and straightforward.

1 2 3 4

// Validate negative value operation.grid.dataCellIterator{DataCell cell -> cell.edited}.each { DataCell cell -> if ((cell.data < 0)) cell.addValidationError(0xFF6347, "Cannot enter negative numbers") }

Layout of my data form:

I have two business rules attached to the form (one business rule and one Groovy script to be precise)

GroovyValidate is my validation script and OnSave is a simple BR that calculates Product Revenue (Volume multiplied by Average Price), and OBVIOUSLY, I'll run both rules after save.

Let me enter some data and confirm it's working:

You can see above I was able to save the negative values which means there is something wrong either with my script or the form. I ruled out the script because it's a simple if condition that is definitely working, so what is wrong exactly? Ok see below the data form execution options for the business rules, we have Before Save and After Save!! Interesting!

By defaults and after a decade working on Hyperion Planning, almost 90% of the cases I had to run the rule on save, and that was my obvious and default choice, well I was wrong! I corrected this and selected Run Before Save

And now I can't enter negative values:

Notice the form did not save and the cell March->Average Selling Price is still in edit mode, so is Volume -> Mar but because I'm highlighting the cell you can't tell. And If I hover the mouse over the highlighted cell I can see the tool-tip.

All I have to do now is simply change the negative value and I can proceed with my work.

What happens if I enter data in all cells will it work and randomly enter negative values here and there?

The answer is yes, it works just fine!

Remove the negative values and that's it!

Voila!

Another reason to...💕 EPBCS 

Update

Celvin Kattookaran was kind enough to highlight I was using an alpha version of the API library, and he hooked me up with the Oracle Dev team responsible for Calculation Manager/Groovy, I had a call with Ujwala Maheshwari from Oracle and she was kind enough to go through my code and highlight the changes, and recommended another and better way to write the same code which I will elaborate in future posts. If you look again at the code in this post you'll notice some changes from the initial version (the changes and recommendations will be explained in a separate post in the very near future)

Clarification:

I got a comment from an anonymous  user in the previous post, presumably from Oracle, so I'm going to quote it, it clarifies an assumption I made earlier that Groovy is only available for EPBCS, which is not the case...

Quote Start

A point of clarification: Groovy is technically made available for EPBCS App type which is different from saying it is only for EPBCS. There are three SKUs that customers can buy: PBCS; PBCS + 1 module option; EPBCS. If you buy the PBCS +1 module option you can still deploy the application as EPBCS App Type or convert from PBCS App Type to EPBCS App Type. It is just that contractually you can't deploy more than 1 module when you buy PBCS + 1 module. To get Groovy all you need is to have EPBCS App Type. To use groovy you don't even have to deploy any modules once you are in this EPBCS App Type. So technically it is available for PBCS as long as at least the PBCS + 1 module option is bought by the customer. You could be using just the custom cubes in EPBCS App Type and still use Groovy. There is no current plans to make it available for standalone PBCS. Hope this can be clarified in your future posts

End Quote