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.

The Code

The following will provide a template to use to accomplish the interruption of the form save.  The messageBundle can be altered to be whatever is required and in as many languages as needed.  If this is new to you, read Part 13 of this series.

//Setup Message Bundle
def mbUs = messageBundle( ["validation.NoDataChanged":"No data was altered so no business logic was executed."])
def mbl = messageBundleLoader(["en" : mbUs])

//Create the grid and iterator objects
DataGrid curgrid = operation.getGrid()
GridIterator itr = curgrid.getDataCellIterator(PredicateUtils.invokerPredicate("isEdited"))
// Throw an exception if no cells are edited
if(!curgrid.empty && !itr.hasNext() ){throwVetoException(mbl, "validation.NoDataChanged")}

A second option would be to count the cells that have been edited.  One reason to show this, as it is not my first choice, is that it could be altered for another purpose.  If the number of cells edited is required, this could be used to count them.

def iCount = 0 

//Setup Message Bundle 
def mbUs = messageBundle( ["validation.NoDataChanged":"No data was altered so no business logic was executed."])
def mbl = messageBundleLoader(["en" : mbUs]) 

//Iterate through the edited cells
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each { iCount +=1 }

// Throw an exception if no cells are edited
if(iCount == 0) { throwVetoException(mbl, "validation.NoDataChanged") }

Implementing On A Form

Once the code is save as a business rule, add it to any form and run it BEFORE SAVE.  This example assumes the code above is saved as GP – IsEdited.

As long as it is set to run before save and there are no cells on the form that have been altered, the user will get an error that displays whatever the message bundle represents.

Summary

This isn’t going to change your life, or make a drastic improvement for your users.  But, it is a simple thing that is easy to implement that will add some polish to your application.  If you have found other ways to use Groovy Calculations to add some polish to your application, please share with a comment.

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.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, this 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.

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, it would include multiple messageBundles

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

Validate The Input

When a validation error exists, the prompt window will not close, so it won’t let a user continue unless all the data entered validates.  Validations are only limited to your knowledge of how to validate the input.  Let Google be your friend.  You will be hard pressed to have a sitiation where you can’t find an example of what you are trying to do.  If you aren’t familiar with “regex,” it will likely be included in just about any Google search you do.  The examples below all use a regex string to validate the inputs.

To use a run time prompt in Groovy, they must be initiated.  This looks like a comment, but it acts differently when prefaced by RTPS:

/*RTPS: {EmployeeName} {EmployeePhone} {EmployeeEmail} */

Next, we will create a messageBundle.  Although it is simplier than above, it is more than enough to demonstrate its use in the validateRtp method.  This creates an error for each of the three validations in English.

def mbUs = messageBundle(["validation.invalidemail":"Email address is invalid: {0}", "validation.invalidphone":"Phone number is invalid: {0}", "validation.invalidnamelength":"Employee name must be 5 to 40 characters: {0}"]) 
def mbl = messageBundleLoader(["en" : mbUs])

Now, the actionable stuff.  The next 3 lines will validate the 3 run time prompts.  If any of them fail, the RTP window will remain open and the user can’t continue until they fix the errors or cancel the action.

// Validate the Rtp values
validateRtp(rtps.EmployeeName, {(5..40).contains(it.enteredValue.size()) }, mbl, "validation.invalidnamelength", rtps.EmployeeName)
validateRtp(rtps.EmployeeEmail, /^.+@.+/, mbl, "validation.invalidemail", rtps.EmployeeEmail.enteredValue)
validateRtp(rtps.EmployeePhone, /^(?:\+?1[- ]?)?\(?([0-9]{3})\)?[- ]?([0-9]{3})[- ]?([0-9]{4})$/, mbl, "validation.invalidphone", rtps.EmployeePhone)

Putting it all together, we have the following.

/*RTPS: {EmployeeName} {EmployeePhone} {EmployeeEmail} {Scenario} {Year} {Period} {Entity} {Version}*/
def mbUs = messageBundle(["validation.invalidemail":"Email address is invalid: {0}", "validation.invalidphone":"Phone number is invalid: {0}",
"validation.memberexists":"The member you have specified already exists and cannot be created: {0}.", "validation.invalidnamelength":"Employee name must be 5 to 40 characters: {0}"])
def mbl = messageBundleLoader(["en" : mbUs])

// Validate the Rtp values
validateRtp(rtps.EmployeeName, {(5..40).contains(it.enteredValue.size()) }, mbl, "validation.invalidnamelength", rtps.EmployeeName)
validateRtp(rtps.EmployeeEmail, /^.+@.+/, mbl, "validation.invalidemail", rtps.EmployeeEmail.enteredValue)
validateRtp(rtps.EmployeePhone, /^(?:\+?1[- ]?)?\(?([0-9]{3})\)?[- ]?([0-9]{3})[- ]?([0-9]{4})$/, mbl, "validation.invalidphone", rtps.EmployeePhone)

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.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(it.data < 0)  
    throwVetoException(mbl, "validation.Negative")
  }

Introduction

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 groovy-lang.org.  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.

• IntelliJ IDEA
• Netbeans IDE
• SlickEdit
• Groovy/Grails Tool Suite

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 BestBuy.com 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.

Examples

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]
“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!

Introduction

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

Regular_Cases
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
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.

Introduction

One of the huge frustrations I have with Planning is the fact that you haven’t been able to stop a user from saving data that didn’t validate since Smart View was released and Data Forms could be opened in Excel.  Prior to that, the forms could be customized with JavaScript and the form save could be interrupted and cells flagged.  Well, thanks to Groovy Calculations, it is back, and every bit as flexible.

Example

The following Data Form allows users to add new products and enter financial information.  In this form, 3 rules exist.

1. The GP Level 2 % has to be between -10% and 30%.
2. The Regular Cases CANNOT have one month that is more than 50% of the total cases.
3. If Regular Cases is entered, a corresponding Average Price per Case is required.

When a user fills out the form and violates these rules, the cell background is changed and a tool tip is added.  If violations exists, the form does NOT save any changes to the database.  Before any changes can be committed, all errors have to be corrected.  In this example, all 3 validation rules are violated and noted.  If the user hovers the mouse over the cell with a violation, the tool tip is displayed with the row and column members, and an error message explains to the user what the issue is with the data that is entered.

The Code

The significance of this is huge, but the implementation is rather simple.  It is probably be one of the more basic things created with a Groovy Calculation.  Quite simply, to add a validation error and stop the form from saving, all that has to be done is to add a validation error to the cell.

cell.addValidationError(0xFF0000, “Customer Error Message“,false)

This method accepts 3 parameters.

1. The first is the color you want the background to change to.  This is the integer value of any color.  Most people are familiar with the RGB code, and this can be retrieved in almost any image editor (even Windows Paint).  There are many free options, like the free option at https://www.shodor.org/ to convert that to a value that can be interpreted in Groovy.
2. The second parameter is the error message to be displayed in the tool tip.
3. The third is optional, and defaults to false.  False that it will identify the cell as an error and stop the form from saving.

This will likely becused in a grid iterator, which is how this example was constructed to get the screenshot above.  If the grid iterator object is foreign to  you, read Adventures in Groovy – Part 3: Acting On Edited Cells.  The one function that is void from that article is the crossDimCell method.  This acts like a cross dim (->) in a calculation.  So, it references the POV of the cell and overrides the dimension of the member specified as a parameter.  If multiple differences exist, separate the names with a comma.

def BackErrColor = 16755370 // Light Red
//Loop through the cells on the Regular Cases row
operation.grid.dataCellIterator('Regular_Cases','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each { 
  // Set a variable equal to all 12 months
  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 
  // Check the cell value to see if it is larger than 50% of the total year
  if(it.data / CaseTotal > 0.5 ) 
    it.addValidationError(BackErrColor, "Cases for a single month can't be more than 50% of the total year cases.", false) 
  // If cases are entered, make sure there is a corresponding price
  if(it.data != 0 && (it.crossDimCell("Avg_Price/Case_Inp").data == 0 || it.crossDimCell("Avg_Price/Case_Inp").data == '#Missing'))
    it.crossDimCell("Avg_Price/Case_Inp").addValidationError(BackErrColor, "A price is required when cases are entered.", false) 
}

// Loop throught the GP input cells and validate the % is in the valid range
operation.grid.dataCellIterator('GP_2_%_Inp','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each { 
  println "$it.MemberNames $it.data"
  if(it.data > 0.3 || it.data < -0.1 ) {
    it.addValidationError(BackErrColor, "GP2 has to be between -10% and 30%.", false) 
 }
}

Form Settings

The one gotcha is that this needs to run BEFORE SAVE.  It makes sense, but I was expecting a similar result as validating a RTP when the Business Rule runs on save, so it took me a good night sleep to recognize that error in judgement.

Why This Is Significant

You may not thing this is a big deal because you can check this in a Business Rule after the data is saved and return an error message requesting the user to change it.  However, the users are as busy, if not more busy, than you are.  There are last minute changes that get slammed in at the end of a forecast or budget cycle.  There is no design doc to go back to and say it is going to take longer and we need a change order.  The CFO won’t accept that as an answer, so things get forgotten or missed.  This example forces valid data (not necessarily accurate) to be entered, and all kinds of things can be checked to make sure human errors don’t turn into huge issues for financial reporting.  Think if you had a product and forgot to put a price.  You could be missing millions, and this type of proactive validation can prevent such things from happening.  Little things like this reduce, or eliminate, fire drills later on in the budget cycle.

Conclusion

There is an infinite number of things that can be accomplished.  Simple things like the above, to extremely complex validation can be added.  Think about ensuring allocated dollars are fully allocated(100%), forcing salaries to be in predefined pay bands for titles, and forcing the results of driver based planning to be within a logical margin.

If you have some examples, please share with the community by posting a comment below.

Join Us On March 22, 2018

On March 22, 2018, I am hosting a webinar featuring the work delivered at Breakthru Beverage Group in Chicago, Illinois.  Breakthru had the traditional challenges, but it had some additional obstacles other don’t.  With requirements of entering budget at any level, complex allocation and seeding logic, and the need for consolidated reporting in real time, we had to get creative.  Welcome, Groovy Calculations!  Groovy calculations were released in June of 2017, just in time to be a key resource to solve the previously stated problems.   This application will highlight solutions like changing a product price at a consolidated level, and seeing it allocated down to delivery channel, material group, and company.  It will show how we consolidated and pushed to results to the P&L applications in seconds.

It is easy to participate, you can RSVP TODAY.

I will discuss the architecture, the challenges, and how we used Groovy to do things never before possible in Hyperion Planning.

Although we will be discussing the technology used, this is not a technical discussion on how to write Groovy calculations.  If you are an administrator, user, or owner of PBCS, we will highlight challenges you are likely facing, and how to overcome them using Groovy at a more functional level.  If you are looking to purchase or move to the cloud, this presentation will educate you on the possibilities now available with the new functionality of Groovy calculations.

Agenda

• Introduction: Setting the expectations and introducing the speakers
• Application Overview: Application, purpose, top down planning, and seeding
• Performance Challenges: Product updates, allocations, long wait times for consolidated reporting
• Real Time Reporting: How Groovy allowed us to overcome performance issues and enable real time consolidated reporting
• The Groovy 411: Live demo showing how Groovy Calculations solved performance issues
• More Than Performance: Live demo showing other enhancements Groovy provides, like user input validation
• Finishing Up: Q/A, review, and opportunities for next steps to setup an optimization assessment

The Official Invitation

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.

Join this webcast and hear from the VP of Financial Planning & Analysis at Breakthru Beverage Group on how they are using Oracle Planning and Budgeting Cloud Service (PBCS) integrated with game changing technology, Groovy, to improve speed and performance across planning processes.

Leave this session with an understanding on how Breakthru Beverage:

1. Attained strategic benefits of building a driver based budget and forecasting application with the ability to seed product level data and apply growth rates consolidated levels to effectively build a bottoms up plan.
2. Leveraged work force planning to include the ability to allocate people over multiple cost centers and companies.
3. Developed a technical architecture and strategy to allow this to happen and integrate with the higher level P&L in real time.

RSVP today and learn how you can take advantage of Groovy.

Introduction

If your environment is a cloud product, whether it be PBCS or ePBCS, one thing that is critical to understand is the backups produced in the Migration area, may not be what you think.  Learning this after the fact may have negative consequences on your ability to restore data.  In the migration, the Essbase Data section is a copy of the pag, ind, and otl files.  When this is used to restore data, it restored the entire database.  This includes data and metadata.  This may be OK for many situation, but it won’t help you if

• only specific data is required to be restored
• specific data has changed and needs to be excluded from the restore
• corruption exists in the database and all data is required to be restored
• The pag files that hold the data are not readable
• The size of the backup is quite large as it includes all data, and upper level data is normally exponentially larger than just level 0 data

Text Data Export

Business Rules can be written to export data to the Inbox/Outbox that is delimited with a few formatting options.  The entire database can be included.  With fix statements, specific data can be isolated.  So, forecast could be exported to a file, plan another, and actuals a third.  Specific accounts, entities, and/or products can be isolated in cases when specific data was inadvertently changed or deleted.  This file is a text file that can be opened in any text editor, Microsoft Excel, a database, or any other application that you open text files to view or manipulate.

Example Business Rule

/* Set the export options */
 SET DATAEXPORTOPTIONS
 {
 DataExportLevel LEVEL0;
 DataExportDynamicCalc OFF;
 DataExportNonExistingBlocks OFF;
 DataExportDecimal 4;
 DataExportPrecision 16;
 DataExportColFormat ON;
 DataExportColHeader Period;
 DataExportDimHeader ON;
 DataExportRelationalFile ON;
 DataExportOverwriteFile ON;
 DataExportDryRun OFF;
 };
 
 FIX(@Relative("Account", 0),
     @Relative("Years", 0),
     @Relative("Scenario", 0),
     @Relative("Version", 0),
     @Relative("Entity", 0),
     @Relative("Period", 0), 
     @Relative("custom_dim_name_1", 0),
     @Relative("custom_dim_name_1", 0),
     @Relative("custom_dim_name_1", 0))

  DATAEXPORT "File" "," "/u03/lcm/filename_xyz.txt" "";

 ENDFIX

Some Hints

There are a few things that you may encounter and be a little confused about, so the following are a few things that might help.

1. To see the data export, it must be exported to /u03/lcm/, which is the equivalent of your inbox.  Any file name can be used.
2. Setting DataExportLevel to 0 will export the level 0 blocks, not the level 0 members.  If there are any stored members in any of your dense dimensions, they will be exported unless the dimension is also in the fix to include ONLY level 0 members.
3. The fix statement works the same as a fix statement in any business rule, so the data to be exported can be easily defined.
4. My experience exporting dynamic calculated members drastically increases the time of the export.
5. The export options are all pretty logical.  Some work in conjunction with each other and others are ignored depending on dependent setting values.  These are documented for version 11.1.2.4 here.
6. This process can be automated with EPM Automate and include the download and time stamp of the backup for later use.

Conclusion

There are benefits to both types of backups.  My preference is to either run both nightly, or run just the Business Rule.  By having both, the administrator has the option of restoring the data as needed, in the way that is most effective.  Having both provides the ultimate flexibility.  If space is an issue, exclude the data option in the Migration and just run the business rule.

From Oracle’s Documentation

DataExportLevel ALL | LEVEL0 | INPUT

In specifying the value for the DataExportLevel option, use these guidelines:

Description

Specifies the amount of data to export.

DataExportDynamicCalc ON | OFF

Description

Specifies whether a text data export excludes dynamically calculated data.

Notes:

DataExportNonExistingBlocks ON | OFF

Description

Specifies whether to export data from all possible data blocks. For large outlines with a large number of members in sparse dimensions, the number of potential data blocks can be very high. Exporting Dynamic Calc members from all possible blocks can significantly impact performance.

DataExportPrecision n

n (Optional; default 16)—A value that specifies the number of positions in exported numeric data. If n < 0, 16-position precision is used.

Description

Specifies that the DATAEXPORT calculation command will output numeric data with emphasis on precision (accuracy). Depending on the size of a data value and number of decimal positions, some numeric fields may be written in exponential format; for example, 678123e+008. You may consider using DataExportPrecision for export files intended as backup or when data ranges from very large to very small values. The output files typically are smaller and data values more accurate. For output data to be read by people or some external programs, you may consider specifying the DataExportDecimal option instead.

Notes:

Example

SET DATAEXPORTOPTIONS
    { 
    DataExportPrecision 6;
    DataExportLevel ALL;
    DataExportColHeader "Measures";
    DataExportDynamicCalc ON;
    };
    DATAEXPORT "File" "," "output1.out";

Initial Data Load Values

"Sales" "COGS" "Margin" "Marketing" "Payroll" "Misc" "Total Expenses" "Profit" "Opening Inventory" "Additions" "Ending Inventory" "Margin %" "Profit %" 
"100-10" "New York"
"Jan" "Actual" 678123456.0 271123456.0 407123456.0 941234567890123456.0 51123456.0 0 145123456.0 262123456.0 2101123456.0 644123456.0 2067123456.0 60123456.029 38123456.6430
"Feb" "Actual" 645123 258123 3871234 9012345 5112345 112345678 14212345 24512345 2067123456 61912345 20411234 601234 37123456.98 
"Mar" "Actual" 675 270 405 94 51 1 146 259 2041 742 2108 60 38.37037037037037 
"Qtr1" "Actual" 1998 799 1199 278 153 2 433 766 2101 2005 2108 60.01001001001001 38.33833833833834

Exported Data Format

"Sales","COGS","Margin","Marketing","Payroll","Misc","Total Expenses","Profit","Opening Inventory","Additions","Ending Inventory","Margin %","Profit %","Profit per Ounce","100-10","New York"
"Jan","Actual",6.78123e+008,2.71123e+008,4.07e+008,9.41235e+017,5.11235e+007,0,9.41235e+017,-9.41235e+017,2.10112e+009,6.44123e+008,2.06712e+009,60.0186,-1.388e+011,-7.84362e+016
"Feb","Actual",645123,258123,387000,9.01235e+006,5.11235e+006,1.12346e+008,1.2647e+008,-1.26083e+008,2.06712e+009,6.19123e+007,2.04112e+007,59.9886,-19544.1,-1.05069e+007
"Mar","Actual",675,270,405,94,51,1,146,259,2041,742,2108,60,38.3704,21.5833

DataExportDecimal n

Where n is a value between 0 and 16.

If no value is provided, the number of decimal positions of the data to be exported is used, up to 16 positions, or a value determined by the DataExportPrecision option if that is specified.

Description

Specifies that the DATAEXPORT calculation command will output numeric data with emphasis on legibility; output data is in straight text format. Regardless of the number of decimal positions in the data, the specified number is output. It is possible the data can lose accuracy, particularly if the data ranges from very large values to very small values, above and below the decimal point.

Notes:

Example

SET DATAEXPORTOPTIONS
    {DataExportDecimal 4;
    DataExportLevel "ALL";
    DataExportColHeader "Measures";
    DataExportDynamicCalc ON;
    };
    DATAEXPORT "File" "," "output1.out";

Initial Data Load Values

"Sales" "COGS" "Margin" "Marketing" "Payroll" "Misc" "Total Expenses" "Profit" "Opening Inventory" "Additions" "Ending Inventory" "Margin %" "Profit %" 
"100-10" "New York"
"Jan" "Actual" 678123456.0 271123456.0 407123456.0 941234567890123456.0 51123456.0 0 145123456.0 262123456.0 2101123456.0 644123456.0 2067123456.0 60123456.029 38123456.6430
"Feb" "Actual" 645123 258123 3871234 9012345 5112345 112345678 14212345 24512345 2067123456 61912345 20411234 601234 37123456.98 
"Mar" "Actual" 675 270 405 94 51 1 146 259 2041 742 2108 60 38.37037037037037 
"Qtr1" "Actual" 1998 799 1199 278 153 2 433 766 2101 2005 2108 60.01001001001001 38.33833833833834

Exported Data Format

"Sales","COGS","Margin","Marketing","Payroll","Misc","Total Expenses","Profit","Opening Inventory","Additions","Ending Inventory","Margin %","Profit %","Profit per Ounce"
"100-10","New York"
"Jan","Actual",678123456.0000,271123456.0000,407000000.0000,941234567890123520.0000,51123456.0000,0.0000,941234567941246980.0000,-941234567534246910.0000,2101123456.0000,644123456.0000,2067123456.0000,60.0186,-138799883591.4395,-78436213961187248.0000
"Feb","Actual",645123.0000,258123.0000,387000.0000,9012345.0000,5112345.0000,112345678.0000,126470368.0000,-126083368.0000,2067123456.0000,61912345.0000,20411234.0000,59.9886,-19544.0820,-10506947.3333
"Mar","Actual",675.0000,270.0000,405.0000,94.0000,51.0000,1.0000,146.0000,259.0000,2041.0000,742.0000,2108.0000,60.0000,38.3704,21.5833

Output Format Options

DataExportColFormat ON | OFF

Description

Specifies if data is output in columnar format. Columnar format displays a member name from every dimension; names can be repeated from row to row, enabling use by applications other than Essbase tools. In non-columnar format, sparse members identifying a data block are included only once for the block. Non-columnar export files are smaller, enabling faster loading to an Essbase database.

Notes

Do not use the DataExportColFormat option in combination with the DataExportRelationalFile option, which already assumes columnar format for files destined as input files to relational databases.

Example

SET DATAEXPORTOPTIONS
 {
 DATAEXPORTCOLFORMAT ON;
 };
 FIX("100-10", Sales, COGS, Jan, Feb, Mar, Actual, Budget)
 DATAEXPORT "File" "," "d:\temp\test2.txt" ;
ENDFIX;

DataExportColHeader dimensionName

Description

Specifies the name of the dense dimension that is the column header (the focus) around which other data is referenced in the export file. Use the DataExportColHeader option only when you export data to a text file. For example, if from Sample Basic the Year dimension is specified, the output data starts with data associated with the first member of the Year dimension: Year. After all data for Year is output, it continues with the second member: Qtr1, and so on.

Notes

MaxL, ESSCMD, and Essbase exports do not provide a similar capability. With these methods, Essbase determines the focal point of the output data.

Exporting through Report Writer enables you to specify the header in the report script.

Example

SET DATAEXPORTOPTIONS {DATAEXPORTCOLHEADER Scenario;};

Specifies Scenario as the page header in the export file. The Scenario dimension contains three members: Scenario, Actual, and Budget. All Scenario data is shown first, followed by all Actual data, then all Budget data.

DataExportDimHeader ON | OFF

Description

Use the DataExportDimHeader option to insert the optional header record at the beginning of the export data file. The header record contains all dimension names in the order as they are used in the file. Specifying this command always writes the data in “column format”.

Example

SET DATAEXPORTOPTIONS 
 {
 DATAEXPORTLEVEL "ALL"; 
 DATAEXPORTDIMHEADER ON; 
 };
FIX("100-10", "New York", "Actual")
 DATAEXPORT "File" "," "E:\temp\2222.txt" ;
ENDFIX;

Specifying the DataExporttDimHeader ON option while exporting Sample Basic writes the data in column format, with common members repeated in each row. The data begins with a dimension header, as shown in the first two rows of the example file below:

"Product","Market","Year","Scenario","Measures"
"Sales","COGS","Marketing","Payroll","Misc","Opening Inventory","Additions","Ending Inventory"
"100-10","New York","Jan","Actual",678,271,94,51,0,2101,644,2067
"100-10","New York","Feb","Actual",645,258,90,51,1,2067,619,2041
"100-10","New York","Mar","Actual",675,270,94,51,1,2041,742,2108
"100-10","New York","Apr","Actual",712,284,99,53,0,2108,854,2250
"100-10","New York","May","Actual",756,302,105,53,1,2250,982,2476
"100-10","New York","Jun","Actual",890,356,124,53,0,2476,1068,2654
"100-10","New York","Jul","Actual",912,364,127,51,0,2654,875,2617
"100-10","New York","Aug","Actual",910,364,127,51,0,2617,873,2580
"100-10","New York","Sep","Actual",790,316,110,51,1,2580,758,2548
"100-10","New York","Oct","Actual",650,260,91,51,1,2548,682,2580
"100-10","New York","Nov","Actual",623,249,87,51,0,2580,685,2642
"100-10","New York","Dec","Actual",699,279,97,51,1,2642,671,2614

DataExportRelationalFile ON | OFF

Description

Using the DataExportRelationalFile option with DATAEXPORT enables you to format the text export file to be used directly as an input file for a relational database.

Example

SET DATAEXPORTOPTIONS {
 DataExportLevel "ALL";
 DataExportRelationalFile ON;
};

FIX (Jan)
 DATAEXPORT "File" "," c:\monthly\jan.txt
ENDFIX;

Processing Options

DataExportOverwriteFile ON | OFF

Description

Manages whether an existing file with the same name and location is replaced.

DataExportDryRun ON | OFF

Description

Enables running the calculation script data export commands to see information about the coded export, without exporting the data. When the DataExportDryRun option value is ON, the following information is written to the output file specified in the DATAEXPORT command:

Notes

Example

SET DATAEXPORTOPTIONS 
 {
 DataExportLevel "ALL";
 DataExportColHeader "Measures";
 DataExportColFormat ON;
 DataExportDimHeader ON;
 DataExportDynamicCalc OFF;
 DataExportDecimal 0;
 DataExportDryRun ON;
 DataExportOverwriteFile ON;
 };

FIX("Qtr1")
 DATAEXPORT "File" "," "E:\temp\log.txt" ;
ENDFIX;

I was lucky enough to be invited to talk about the new Groovy Calculation in PBCS and ePBCS at the Ohio Valley OAUG meeting today.  If you have read the Groovy series, you know how strongly I feel about the advancements in Hyperion Planning with the addition of Groovy Calculations.  I want to share the presentation with a wider audience.  This is a functional overview for those who are new to the concepts.  This also introduces readers how to develop their first Groovy Calculation, and provides some examples.