Adventures in Groovy – Part 14: Returning Errors (Form Cells)


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.

  1. Units can’t ever be adjusted to a negative amount – they can be decreased, but never to a negative value.
  2. 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.crossDimCell('OEP_Working').data < 0.0)
   def change = + 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)
  if(CaseTotal != 0.0 && != 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.


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.


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.

Adventures in Groovy – Part 13: Returning Errors (Data Forms)


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.There are a number of functions for validation, and they can be categorized functionally. Although they all can be use somewhat interchangeably, the logical uses are

  • Data Form validation functions
    • addValidationError
  • RTP validation functions
    • validateRtp
  • Validation functions that are more open ended and can be used just about anywhere
    • messageBundle
    • messageBundleLoader
    • throwVetoException

In this post, we will discuss one aspect of this, and probably the simplest application, validating Run Time Prompts (RTP).

The MessageBundle

Before a few of the methods can be used, one must first understand the MessageBundle and MessageBundleLoader methods.  To look at documentation, they might seem very complex, and a maybe a little intimidating.  The reality is that these are just objects that hold the error messages.  That is pretty much the long of short of it.  The messageBundle holds a map (basically a lookup table that is two columns and n rows) of the error ID and the description of the error you want to display.  If the application is consumed by users with multiple languages, a messageBundle can be created for each language.  The messageBundleLoader allows you to identify which bundle to use based on the user’s local.  The example below should answer any questions you have.

The Message Bundle

Think of this method as an array, or a table in Excel.  It has 2 columns (ID and message).  It can have an infinite amount of rows.  The syntax of this is “[id:message]”.  For multiple errors, the id:message is duplicated, separated by a comma, like “[id,message,id:message]”.  Here is an example of a messageBundle with one error.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9)."] )

And with two errors.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9).",
"validation.Negative":"A positive number is required."])

And with two errors in Spanish.

def mbSpanish = messageBundle( ["validation.InvalidCharacters":"Sólo se pueden introducir caracteres alfanuméricos (a-z, 1-9)."],
["validation.Negative":"Se requiere un número positivo."])

This can be extended to hold all the error messages required for the scope of the calculation in all the locales required.

The Message Bundle Loader

The messageBundleLoader is the piece that pulls either a single, or multiple, messageBundles together to use in a call.  If only one language is required, it would look like this.

def mbl = messageBundleLoader(["en":mbUs])

For multiple languages, or multiple messageBundles, they would be concatenated together with commas.  View a valid list of locales to make sure the parameter in parenthesis is correctly linked to the correct locale.

def mbl = messageBundleLoader(["en":mbUs", "es":mbSpanish])

Throw an Exception (Interrupt Form Save)

Here is where the cool stuff happens.  see post about looping through cells

If a validation error exists, an exception can be generated to stop the form from saving.  To do this, simply use the throwVetoException method.  This accepts 2 parameters.  The first is the messageBundlerLoader, and the second is the id associated to the to be displayed.  Using the example above, and assuming the local is US, the following would stop the form from saving and display a message of  “Only alphanumeric characters can be entered (a-z, 1-9).”

throwVetoException(mbl, "validation.InvalidCharacters")

Consolidated Example

The following example creates two error messages in two languages.  On form save, this will loop through all the cells and throw an error if any value is negative.

def mbUs = messageBundle( ["validation.InvalidCharacters":"Only alphanumeric characters can be entered (a-z, 1-9).",
"validation.Negative":"A positive number is required."])

def mbSpanish = messageBundle( ["validation.InvalidCharacters":"Sólo se pueden introducir caracteres alfanuméricos (a-z, 1-9).",
"validation.Negative":"Se requiere un número positivo."])

def mbl = messageBundleLoader(["en" : mbUs,"es" : mbSpanish])

operation.grid.dataCellIterator.each {  
  if( < 0)  
    throwVetoException(mbl, "validation.Negative")

Wrap Up

It has been a long time since developers have had this kind of control.  The possibilities are only limited by your imagination and business requirements, but there isn’t any validation that can’t be done.  Future posts will tackle validating Run Time Prompts, and taking form validation one step further by adding cell level tool-tips and color coding.

The last thing with these validation calculations is the importance of when they are executed.  The documentation I have from Oracle states something slightly different, so I don’t know if this is the way it is supposed to work, but in my experience, where the rule runs is critical.  Here is what I am experiencing.

  • When the rule is set to Run Before Save, and there is a validation error, the user can’t save the form and an error messages is displayed in the correct locale.  To me, this is the experience that is expected.
  • When the rule is set to Run After Save (which is the way it is documented), and there is a validation error, the user receives an error, but the data is saved.

The difference in the above does provide some interesting options.  Let’s say that we have a form and users are required to allocate an expense.  If the expense is not allocated at 100%, the form can’t be saved.  Assume that there is a rule that the expense shouldn’t be allocated to more than 3 places, but users should be warned if it is.  In this case, if the rule is set to run AFTER save, the user gets the message, but the data is saved.

Either way, if the rule is executed before other rules on the form, no subsequent form will fire if there is a validation error.


Adventures in Groovy – Part 12: Learning and Testing Groovy Outside of PBCS


For people that are new to Groovy/Java, testing functions that Groovy provides can be a tedious and time consuming process.  Learning anything is.  Trying to do this with the wrong tools compounds it.  I have seen some people give up and walk away from trying to improve applications because they struggle with the Groovy Calculations and the complexity it introduces to go beyond some of the basics, just because they are using a hammer when they need a screwdriver.  For example, it is simple to use a documented example and loop through the cells on a form, but to utilize the Groovy/Java objects and methods is the difference between using the default logic and taking Planning to a whole new level.  For those of us who are learning, testing simple functions can be very painful inside a Groovy Calculation.

I will by preface saying I am not a Groovy developer.  I am learning as I need functionality and I am trying to build a foundation to be as productive as possible.  Although Groovy in PBCS doesn’t give developers full access to all the Java libraries, much of the logic that is needed to develop new functionality can be tested outside of PBCS.  I have found that as I learn more and require more non PBCS related functionality, it is easier to test in the Groovy Console rather than in a PBCS calculation.  Some examples are

  • string functions like replace, regex, concatenate
  • mathematical functions
  • other manipulation that require the use of collections and hash tables

These can be used in looping through grid cells or building evaluation rules on data entered.  Hopefully, this is helpful to those learning Groovy.

How To Get Started

Download Java SDK

Before Groovy can be used, Java has to be installed.  Most systems already have it.  If not, the Java Development Kit can be downloaded and installed.  There is information about which version of Groovy and Java are compatible at  The Java SDK can be downloaded from Oracle.

Download An Editor

Groovy can be edited in many free and paid programs.  Some of them are more robust than others and provide things like automatic code completion, color coding, and more advanced features that aren’t likely required at this novice level.  They also increase the complexity for those that are completely new to writing JAVA or Groovy.  If you are interested in this or need a longer term solution, check out these editors.

For those who want to just get started with a simple and supported editor to test some basic code, try the Apache Groovy Console.  The Windows Installer, the documentation, and the SDK are available to download and install.  Once installed, you are ready to go!  If you go to your Start menu in Windows, you will see a folder for the version of Groovy installed.  In that folder click on Start GroovyConsole to open the editor.

Using The Groovy Console

Much of what is done in the Groovy calculations can’t be accessed here.  We don’t have grids, cells, or any of the PBCS  methods that we interact with in a Groovy Calculation.  Groovy can also access the REST API (outside of Groovy Calculations), which opens up the ability to manage PBCS like EPM Automate.  I recently looped through the product catalogue at and built a hierarchy!  This is a whole other beast, but it is worth mentioning.

Before we jump into testing a script, here are a few things that will be helpful using the Groovy Console.

  • The editor has two panes.  The top pane is where the script is developed and edited.  The bottom pane is where the results of the script are displayed when it is executed.
  • The toolbar has some common functions.  You can open and save your scripts, redo/undo, and execute from icons in this area.


I find it very helpful as I am learning, to test the logic and the results in this console.  Once validated, it will be moved to the PBCS calculation and used appropriately.  Here are some examples where it might be useful, and hopefully the separation of where to test what is highlighted.

Regex Example

There was a requirement on a form at a recent client where they wanted to accept input.  They used this to setup properties in the HR system.  The HR system could not accept some characters, so the ask was to only allow alphanumeric characters, a space, an underscore, and a dash.  We had to add validation to the run time prompt, as well as when the data was updated in a form.  Not being an expert with regex, I didn’t want to test this in a calculation (update calc, run calc, open job console, expand status, toggle between windows, etc).

So, I opened the Groovy Console and tested there.  The end result is below, but it was much easier to tweak the regex syntax directly in the console, running it, and seeing the result immediately, in one step.  This was easy to see and verify the output was void of any characters that were not allowed.  The length could be compared, pre and post character removal, and was used to stop the save of the data.

String text = "This - text ! has \\ /allot # of % special % characters"
println text
println text.length()
println text.replaceAll("[^a-zA-Z0-9 _-]", "")
println text.replaceAll("[^a-zA-Z0-9 _-]", "").toString().length()
println text.length() == text.replaceAll("[^a-zA-Z0-9 _-]", "").toString().length()

At this point, I proved out the regex functionality.  I can now go back to the Groovy Calculation and use this logic on the variable returned from the PBCS function (whether it be an RTP or a cell value) and remove the invalid characters or test to see if there are any, and act accordingly.  This is what it would look like

String enteredValue = rtps.RTP_NewEmployee.getEnteredValue();
if(enteredValueAdj.length() == enteredValue.length())
def mbUs = messageBundle(["validation.InvalidChars":"You have entered invalid characters.  Only alphanumeric characters, spaces, dashes, and underscores are accepted."])
def mbl = messageBundleLoader(["en" : mbUs])
throwVetoException(mbl, "validation.InvalidChars", rtps.RTP_NewEmployee)
Converting Nested Collections

I was building a Data Map override from a POV, and it wasn’t validating because some of the variables were collections that included a nested collection.  This whole concept was completely new to me, and again, I didn’t want to have to go through 3-5 steps to see if the result was returning a delimited list of members that the Data Map would accept.  Since I had no initial idea how to accomplish this, I searched and found examples that might accomplish what I wanted to achieve.  It took 5 to 10 iterations of examples to get to what I wanted and understand how this worked.  Updating a script in the Groovy Console, running it, and seeing the results in the same window proved much quicker to find a solution.

In the solution below, I created a variable that replicated the variable that PBCS that was returning (a list).  I was able to build out a few lines to eliminate the nested collections and ported this over to my Groovy Calculation.

This proved out that the simple loop below would give me a list I could pass to the Data Map, and was much quicker to solve than trying to do this in PBCS.

def orig_list = [10, 20, [1, 2, [25, 50]], ['Groovy']]
def usable_list = []

orig_list.collectNested([]) { 
 usable_list << it
println usable_list 
println '"' + usable_list.join('","') + '"'

The result of the executed script created two lines.  At this point, I could use this function in the Groovy Calculation by replacing the orig_list with the object returned from the PBCS function.  I used the usable_list in the Data Map.

[10, 20, 1, 2, 25, 50, Groovy]

Wrapping Up

These examples are great examples of how we can use a pair of tools to create business logic efficiently.  If you are a seasoned java developer, much of this might seem ridiculous to you and question why one would ever use something outside of PBCS.  I get it.  Now that I know how these two function work, I likely will not use the Groovy Console to write and test this.  But, as I continue to learn more and more, being able to do this in something outside of PBCS has proven invaluable, increased my productivity, and significantly reduced my frustration.

If you are learning, or are an experienced Groovy developer, please share your insights with the community and post a comment!

Kscope 18 at Walt Disney World in Florida

The Mouse Or Bust

I am really excited to be speaking and attending Kscope this year. It is very difficult to get selected to participate, so hopefully I don’t disappoint.  The last few years I was on the outside looking in, but this year I have 2 presentations.

Why Groovy is Game Changing

Groovy is a game changer.  It improves performance, allows for proactive user validation, can interact with the user in ways we haven’t been able to since the introduction of SmartView.

This session will provide an overview of Groovy functionality, including mind blowing performance improvements, how to interact with the planners, pre form save validation, and changing the form visuals.  The session will take the attendees from start to finish on the basics of Groovy with interactive code reviews and live demos.

Attendees will leave this session with the knowledge and examples that will enable them to go back to their environments and implement Groovy calculations to take advantage of ground breaking advancements in the technology.  These scripts improved calculations, data pushes, and consolidations by 98% in a real client application.

Top Down and Bottom Up Planning at Breakthru Beverage Group

Planners are always looking for real time reporting and faster feedback.  They are looking to make the forecasting and planning process faster by using historical trends and the ability to enter data at any level, enter growth factors, and drive the results down to the lowest level of the business.  They want instant feedback on consolidated results.

This session will highlight a customer success story using PBCS.  This session will walk through the strategic benefits building a driver based budget and forecasting application with the ability to seed SKU level data and apply growth rates at any level of product or organization to effectively build a bottom up plan.  The session will also discuss the work force planning application that includes the ability to allocate people over multiple cost centers and companies.  It will also discuss the technical architecture and strategy to allow this to happen and integrate with the higher level P$L in real time.  The session will be supported with live demonstrations.

Attendees will leave this session with an understanding of the business solutions and the strategic benefits of building a plan from historic trends.  They will also leave with a complete understanding of the technical architecture, including the benefits of Groovy calculations, and how to implement a similar solution.

Other Activities

Huron has a host of activities to compliment the events Kscope will be hosting.  If you would like to meet me there to discuss an opportunity, or just have a conversation about the cloud landscape, please drop me a email and we can schedule some time.

I will share more information about my sessions, and some other speakers I would recommend, as the event gets closer.

If you haven’t attended this event, it really is a great experience. You will come away from the event with new knowledge and relationships.  If you are interested in attending there is great information at  There is still a $300 discount for early signup, but it doesn’t last more than another few weeks.

Bug Report: GroovyDynamicLocalizedException Validation Error

The GroovyDynamicLocalizedException Groovy method should return the message in the user selected local (language) from a messageBundle.  This can be used to share the bundles in other situations.  Currently, when used, it returns a validation error.

A validation error was received from the Planning server.

‘Error:The Groovy script failed to compile with internal error: Compile Error: [Static type checking] – Cannot find matching method Script29#GroovyDynamicLocalizedException(oracle.epm.api.GroovyResourceBundleLoader, java.lang.String). Please check if the declared type is right and if the method exists.

Oracle has created in internal bug to this issue.


An example of the use of this function that produces this error is below.

def mbUs = messageBundle(["validation.InvalidVendorDesc":"The Product name cannot exceed 20 characters in length. The description that you entered is characters too long.", "validation.InvalidCharacter":"You cannot use commas [ , ] or pipes [ | ] in Product names"])
def mbl = messageBundleLoader(["en" : mbUs])
println GroovyDynamicLocalizedException(mbl,"validation.InvalidVendorDesc").getMessage()

There is currently no time table on a resolution.

Adventures In Groovy – Part 11: Accessing Metadata Properties


Groovy opens up a lot of things above and beyond performance improvements and improving the user experience.  One example is the possibility to interact with the metadata.  Dimensions and members can be queried for all types of things which can be useful in many situations.  Is the POV at a level 0?  What is the parent of the current POV member?  Does the member exist in another application?  What about pushing data for specific UDAs and dynamically generating the Data Map?  How about dynamically generating the Data Map to ignore dynamic calculated members?  These are just some examples to get you thinking about where this could be useful.

Code Example

This article won’t get into the logic to accomplish the above examples once the property is identified but will explain how to extract properties for its use.  Below is an example of retrieving every property of an account named Regular_Cases.  This iterates through every metadata property and writes it to the log.

// Get the dimension of the member in question
Dimension AccountDim = operation.application.getDimension("Account")
// Get the member 
Member AccountMbr = AccountDim.getMember("Regular_Cases")
// Print the map to the log
println AccountMbr.toMap()
def memberProps = AccountMbr.toMap()
// Print the member name
println AccountMbr.toString()
// Print every property and corresponding property value
for ( e in memberProps ) {
  println "${e.key} = ${e.value}"

When this is executed, the following is sent to the log.

println AccountMbr.toMap() produces

{Formula (rGP)=<none>, Plan Type (GP)=true, Solve Order (rGP)=0, Formula (Fin)=<none>, Data Storage (OEP_WFSC)=never share, Time Balance=flow, Formula=<none>, UDA=HSP_NOLINK, Skip Value=none, Variance Reporting=non-expense, Data Storage (GP)=never share, Essbase Name=Regular_Cases, UUID=c842d186-6d83-4b90-8d1e-49474a6a8a1d, Member=Regular_Cases, Data Storage=never share, Data Storage (rFin)=never share, Formula (rFin)=<none>, Aggregation (rWFP)=+, Formula (GP)=<none>, Data Storage (rWFP)=never share, Data Storage (OEP_REP)=never share, Data Storage (rGP)=never share, Data Type=currency, Formula (OEP_WFP)=<none>, Plan Type (rFin)=true, Aggregation (OEP_WFP)=+, Data Storage (OEP_WFP)=never share, Parent=GP_Accts, Two Pass Calculation=false, Aggregation (GP)=+, Plan Type (rGP)=true, Process Management Enabled=true, Plan Type (rWFP)=false, Source Plan Type=GP, Aggregation (OEP_WFSC)=+, Exchange Rate Type=none, Plan Type (Fin)=true, Alias: English=Regular Cases, Plan Type (OEP_WFP)=false, Aggregation (OEP_REP)=+, Solve Order (rWFP)=0, Data Storage (Fin)=never share, Hierarchy Type=dynamic, Allow Upper Level Entity Input=false, Account Type=revenue, Formula (OEP_REP)=<none>, Aggregation (Fin)=+, Aggregation (rGP)=+, Plan Type (OEP_WFSC)=false, Formula (rWFP)=<none>, Formula Description=<none>, Aggregation (rFin)=+, Solve Order (rFin)=0, Formula (OEP_WFSC)=<none>, Solve Order (OEP_REP)=0, Valid For Consolidations=false, Plan Type (OEP_REP)=false}

for ( e in memberProps ) {println “${e.key} = ${e.value}”} produces

Formula (rGP) = <none>
Plan Type (GP) = true
Solve Order (rGP) = 0
Formula (Fin) = <none>
Data Storage (OEP_WFSC) = never share
Time Balance = flow
Formula = <none>
Skip Value = none
Variance Reporting = non-expense
Data Storage (GP) = never share
Essbase Name = Regular_Cases
UUID = c842d186-6d83-4b90-8d1e-49474a6a8a1d
Member = Regular_Cases
Data Storage = never share
Data Storage (rFin) = never share
Formula (rFin) = <none>
Aggregation (rWFP) = +
Formula (GP) = <none>
Data Storage (rWFP) = never share
Data Storage (OEP_REP) = never share
Data Storage (rGP) = never share
Data Type = currency
Formula (OEP_WFP) = <none>
Plan Type (rFin) = true
Aggregation (OEP_WFP) = +
Data Storage (OEP_WFP) = never share
Parent = GP_Accts
Two Pass Calculation = false
Aggregation (GP) = +
Plan Type (rGP) = true
Process Management Enabled = true
Plan Type (rWFP) = false
Source Plan Type = GP
Aggregation (OEP_WFSC) = +
Exchange Rate Type = none
Plan Type (Fin) = true
Alias: English = Regular Cases
Plan Type (OEP_WFP) = false
Aggregation (OEP_REP) = +
Solve Order (rWFP) = 0
Data Storage (Fin) = never share
Hierarchy Type = dynamic
Allow Upper Level Entity Input = false
Account Type = revenue
Formula (OEP_REP) = <none>
Aggregation (Fin) = +
Aggregation (rGP) = +
Plan Type (OEP_WFSC) = false
Formula (rWFP) = <none>
Formula Description = <none>
Aggregation (rFin) = +
Solve Order (rFin) = 0
Formula (OEP_WFSC) = <none>
Solve Order (OEP_REP) = 0
Valid For Consolidations = false
Plan Type (OEP_REP) = false
Data Storage (GP) = never share

Getting A Specific Property

Typically, there would not be a need to pull every property.  There might be times when having access to these, however, is useful in calculations.  If a currency calculation is being executed, for example, the rate applied is different if the member is a balance sheet account.  Getting one value can be retrieved by building on the above script.

def keyProp = "Account Type"
if(memberProps[keyProp] = "Revenue"
  {do something}
elseif(memberProps[keyProp] = "Balance Sheet"
  {do something}

Wrap Up

This may seem a little worthless at first, but if you think about all the BSO functions (getting UDAs, Account types for VAR functions, and member relation functions) that require this information, mimicking them in Groovy requires access to the metadata properties.  So, don’t underestimate its use for things like variance, currency, and other calculations, that are done outside of Essbase/Planning calculations and member formulas.