There are a lot of reasons one might loop through children in a Groovy Calculation. On my journeys with Groovy, I have run into a few roadblocks where this has been helpful. Some of these were related to limits in PBCS. Looping through sets of members allowed us to get around some of the limitations. Read more
Introduction
Before we jump in, there are a number of questions you are going to have at the conclusion of this article. Please post comments and I will answer them, but keep in mind, this is an example. Are there different ways to accomplish this? You bet. Should the data sync directly to the rFin database? Probably not, as there are calculations in the fin database that likely need to happen. This could be so complicated that nobody would follow it, so some liberties have been taken to simplify the explanation. The hope is that you can take this, as it has all the pieces required, and modify, add pieces, alter others, and be able to create something that meets your needs. This is a continuation of Part 18. Please read that before you continue.
Introduction
Yes, it is true that Groovy is available in on-premise and cloud (PBCS) versions of Hyperion Planning. No, it is not true that the same flavor of Groovy exists in both. Both have their advantages, and both have their drawbacks. The likelihood that they will ever be the same is extremely low, and here is why.
The Difference Is
On-Premise gives developers the ability to write and use independent Groovy compiled applications. These can be used in Business Rules as CDFs (custom defined functions). Developers have complete functionality to make this do whatever they want. It can return results to save to Essbase/Planning, it can interact with SQL, can run other programs, pretty much anything you can access that has a JAVA API.
PBCS doesn’t have the same flexibility. Custom defined functions can’t be compiled and stored on the server. PBCS, rather, has “Groovy Calculations.” This gives developers the flexibility to interact with the Data Forms that on-premise doesn’t have. Developers can iterate through the cells and act accordingly. It can stop the form from saving, calculate and override data entered, color code cells, customize Data Maps, Smart Pushes, dynamically generate calculations, move data between databases, all with access to much of the Groovy functionality.
PBCS also supports the REST API, so Groovy can be used to access that and do everything, even more, that EPM Automate can do.
Why They Will Never Be The Same
This is just an opinion. Technology changes so rapidly that this may change. Corporate strategy changes almost as rapidly.
If PBCS had to ability to do what on-premise does, the ability for Oracle to support the instance would be a challenge. CDFs can delete all the files on a server, for instance, and I don’t see a cloud provider giving developers this much control in a shared environment.
I also don’t see on-premise to have the same proactive interaction that PBCS has with Groovy Calculations purely because Oracle is pushing the cloud, and they want the most current functionality to exist in the platform they are pushing clients to use.
My Two Cents
I understand why there is a difference, and I don’t expect it to change in the near future. 3 years ago I didn’t expect that I would tell you that I would rather do a cloud implementation than on prem, either. I do think as people get more comfortable with the cloud, and security improves, there will be advances. I think there will be a future state where the cloud offerings will be closer to having the flexibility to the on-premise implementations.
Introduction
Chris Hull has been kind enough to partner with us to present how the methods available in Groovy calculations have made a huge impact in their budgeting and reporting process using PBCS. Read more
Introduction
One of the challenges with Hyperion Planning is the ability to move data between applications in real time. A classic example of this is a P&L application with other modules that have greater detail. The following is an example. Read more
Challenge Accepted
When I asked visitors to try to come up with a situation that Groovy Calculation might be able to solve, this was a good one. One visitor asked if they could require a cell comment if certain parameters were not met. It is actually relatively easy.
The following requirement exist in this example. If any month holds more than 30% of the full year, that cell requires the user to enter a cell comment. If no comment exists, the user won’t be able to save the form.
The User Experience
If any month is more than 30% of the full year, and the user doesn’t add a comment to a cell, the form will not save. The following shows what happens when the above fails, and what happens after the user enters a comment into the cell.
The Code
def backErrColor = 16755370 //Red
def caseTotal = 0
def accountName = ""
// Loop through the months
operation.grid.dataCellIterator('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each {
// Get a total for all 12 months every time the row changes
if(it.getAccountName() != accountName) {
accountName = it.getAccountName();
caseTotal = it.data + it.crossDimCell('Feb').data + it.crossDimCell('Mar').data + it.crossDimCell('Apr').data + it.crossDimCell('May').data + it.crossDimCell('Jun').data + it.crossDimCell('Jul').data + it.crossDimCell('Aug').data + it.crossDimCell('Sep').data + it.crossDimCell('Oct').data + it.crossDimCell('Nov').data + it.crossDimCell('Dec').data
}
// If the value is greater than 30% of the total and the cell does NOT have a cell comment, interrupt the form save
if(it.data > 0 && it.data / caseTotal > 0.3 && !it.hasCellNote() ) {
it.addValidationError(backErrColor, "Cases for a single month can't be more than 30% of the total year without an assumption.", false)
}
}
Conclusion
Challenge accepted. This one goes in the win column for Groovy Calculations!
Introduction
I know you can argue this is a user issue and a training issue, but the fact is, sometimes people will save a form without editing any data. There are at least three negative issues as a result. One, the business rules and smart pushes execute, consuming unnecessary resources. Two, users may think they made changes and expect changes in the results. Three, if the processes are time consuming (like applying allocations or currency rates globally), the user will have to wait to correct the issue. There is a very simple way to stop all the processes from executing and inform the user that they haven’t made any changes. Read more
Introduction
One of the huge benefits that available in Groovy Calculations is the ability to interact with a user, validate data, and act on the validation. Now, we can interrupt form saves, stop Run Time Prompts from continuing, and communicate information back to the user.
This may sound repetitive if you have read part 13 and part 14, and you can skip to the code example to learn more about run time prompt validation. If not, you must have an understanding of the validation functions and the components of the messageBundle. Read more
Introduction
To expand on Part 13 of this series, which covers stopping a form from saving when there are validation errors, is identifying the errors by cell and communicating with the user the problems at a cell level. This does NOT stop at the first error and throw an exception. This will iterate through all the errors and explain each one at a cell level for the user to correct. The following example will use similar code and concepts, but will apply validations to each cell by changing the color and setting a tool-tip with the explanation of what the validation error is.
Before we continue, the methods to do this do not make use of the MessageBundle. I think this is a miss because one bundle can be reused for similar validation, and the current methods assume a single language. There is a way to use it indirectly. There is a bug that is causing issues with the method, so we will assume basic functionality and come back to the use of a MessageBundle when the bug is fixed
Throw an Exception (Interrupt Form Save)
The basic inclusion of cell validation is very simple. As the code iterates and validates the cells, the following will change the background color, add a tool-tip, and invalidate the form and stop it from saving any data to Planning.
def BackErrColor = 16755370 //Red it.addValidationError(BackErrColor, "error message here",false)
The color can be different for different errors and it completely customizable. The error message can be anything necessary.
Consolidated Example
The form associated to this rule has the ability to adjust a number by either increasing or decreasing the units by month.
To illustrate this, here is an example of looping through cells and validating two things.
- Units can’t ever be adjusted to a negative amount – they can be decreased, but never to a negative value.
- Any change to units must be offset to have a full year impact of zero.
def BackErrColor = 16755370 //Red
def CaseTotal = it.crossDimCell('Jan').data + it.crossDimCell('Feb').data + it.crossDimCell('Mar').data + it.crossDimCell('Apr').data + it.crossDimCell('May').data + it.crossDimCell('Jun').data + it.crossDimCell('Jul').data + it.crossDimCell('Aug').data + it.crossDimCell('Sep').data + it.crossDimCell('Oct').data + it.crossDimCell('Nov').data + it.crossDimCell('Dec').data
operation.grid.dataCellIterator('Working_Inp','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each {
if(it.data + it.crossDimCell('OEP_Working').data < 0.0)
{
def change = it.data + it.crossDimCell('OEP_Working').data
it.addValidationError(BackErrColor, "Your adjustment forces the new cases to be a negative volume. Increase your adjustment by $change", false)
}
else
{
if(CaseTotal != 0.0 && it.data != 0.0)
it.addValidationError(BackErrColor, "Adjustments must not have a full year impact. Currently, the data would change by $CaseTotal.", false)
}
}
Enhancement Request
One thing you might notice is the lack of inclusion of the messageBundle object. I have requested an enhancement, as it only makes sense that it be used here, and they have added it to the enhancement list. So, look for this be added in the future. It can be identified internally by the following.
Enh 27656951 – EPBCS – GROOVY FUNCTION ERRORING
I don’t know why, but Oracle has no way of getting the message based on the local from the messageBundle. Many of the methods, like getMessage, are not made available to us as developers, that would likely circumvent this issue.
Summary
As with the other validation methods, this introduces a huge benefit in both usability and budget accuracy. Any time data validation can be performed proactively, everybody wins. There is less of a burden on administrators and users get instant feedback they can easily and quickly fix.
Introduction
One of the huge benefits that available in Groovy Calculations is the ability to interact with a user, validate data, and act on the validation. Now, we can interrupt form saves, stop Run Time Prompts from continuing, and communicate information back to the user. Read more