Groovy is a powerful scripting language that can extend any EPM deployment in different areas, such as data validation, to running REST jobs. This post is the first in a series focussing on how we can create reusable Groovy components in Oracle’s EPM Cloud applications.
In this blog, I will be demonstrating a simple data validation scenario preventing Planning users from entering values that are negative or more than one percentile in data forms.
In this example, we have two account members, “Gross Margin %” and “Employee Benefits %,” both used to capture assumptions and to calculate product revenue and employee expenses.
Let’s begin with how a typical validation Groovy script will look. We will call a Data Cell Iterator for the account member and check if the value is within the accepted range.
This script will need to run before saving the data form where the member is used and will trigger a validation error if the condition is not met and prevent the user from saving the values.
So, how can we make this script a bit more efficient and reusable?
The answer is the Calculation Manager Templates.
We can create a template to insert the same code with small changes. We will use a Design Time Prompt variable instead of the member name. This way, we can reuse the template in different rules without replicating the main body of the script itself. This approach is more efficient even with relatively simple scripts because it means we maintain one template regardless of the number of business rules using the template.
Let’s proceed and create the template. To do this, we need to create a text-based template as opposed to a graphical one. Insert the Groovy script, create a “Member” type Design Time Prompt (DTP) and insert it into the script. (Note: you can also use different types of DTPs such as minimum and maximum if you wish to define the validation range per business rule etc.)
As shown in the next screenshot, I have created a Member type DTP and selected the “Prompt It?” and “Mandatory?” properties, specified the Dimension and left the remaining fields blank.
Next, we need to create a step and add the DTP so we can use it in our template.
The final part is the script itself; the only difference here is we are using the DTP instead of a hardcoded member name.
And that’s it – we now we have our Groovy validation template.
We can reuse this template for as many business rules as required.
Back to our example, we have a “Gross Margin %” and “Employee Benefits %” account members that will be validated before saving the values in Revenue Planning and Employee Assumptions data forms.
To create a business rule, create a rule and drag and drop the template and specify the member name.
The next two screenshots show the business rules and the Groovy templates.
Validate Gross Margin Business Rule
Validate Employee Benefits
The next step is to assign the business rules to data forms and verify they are working as expected. Make sure the rule has the “Run Before Save” property.
And to show everything is working as expected, here is the Revenue Plan by Products data form where we are validating Gross Margin assumptions per product. We can test the validation rule by trying to save values below 0 or more than 1.
In this blog, we showed a relatively simple Groovy script and how it can be effective and reusable by using templates. The combination of Groovy scripts and Calculation Manager templates can be of great value to any application.
Be sure to check out the rest of our Groovy series as we help you optimise and gain the most out of your Oracle EPM Cloud solution.
In our next blog, we will demonstrate how to create a Groovy Template for User Variables.
Want to know more about maximising your Oracle EPM solution? Contact us.