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.

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.



Introduction

Groovy has the ability to execute Data Maps and Smart Pushes.  Data Maps are objects that define the data movement between applications and are accessible globally.  Smart Pushes are Data Maps that are attached to a Data Form and can override the dimensions settings.  They are largely the same thing, but defined separately inside of PBCS.

So, why execute these in Groovy rather than assign to a form and call it a day?

1. The data push can be customized to push only data that has changed, improving performance.
2. When a form is saved, all calculations are executed first, and the Smart pushes are executed after all calculations are finished.  If Groovy is used and the data push is done inside a Business Rule, the order of operation can be a
   1. calculation
   2. data push
   3. calculation
   4. data push
   5. etc
3. Since the Smart Push has a global limit of memory that can be used, and multiple people are running them through form saves, it is critical to make it as small as possible to reduce the probability of hitting that limit and increasing the odds of the Smart Pushes failing.

To date, I see the advantage of running a Smart Push (a Data Map on a form) in that most of the dimensions are already overridden and less overrides are required in the Groovy logic.  There is no difference in performance or the size of what can be pushed between the two when executed from a Groovy calculation.  The advantage of using a generic Data Map is that there is less effort in defining the form level Smart Pushes, and once one Groovy calculation is written to override all dimensions from a Data Map, it can be replicated easily to all required forms.

To understand the memory issues and explanation of how it differs from Data Maps and Smart Pushes, see PBCS Data Map / Smart Push Has Data volume Limits.

Data Map

Executing a Data Map is very simple and can be done in one line of code.

operation.application.getDataMap("Data Map Name").execute(true)

Calling execute() on a DataMap would execute the named Data Map (with no customization) and clearing the target location before pushing data.  Changing the true to false, or removing it, would remove the clear and leave the data as is.

To override the the dimension settings and increase or decrease the scope of what is used, the member names need to be further defined.  Every dimension’s scope can be changed, or just the ones that require a change in scope can be altered.  The following changes the scope for the account, scenario, version, and Entity dimensions.

operation.application.getDataMap("Data Map Name").execute(["Account":"Net Income, Income, Expense", "Scenario":"Budget", "Version":"Working", "Entity":"East Region"], true)

Smart Push

The Smart Push works exactly the same, except the object referenced is a Smart Push, and is obtained through the grid object, not the application.  Again, the likelihood that the Smart Push is further scoped is high, so it is reasonable that the dimensional parameters would be fewer as many of them change based on the POV selected.

operation.grid.getSmartPush("Smart Push Name").execute(["Account":"Min Bonus, Min Salary"])

One additional option is to define a Smart Push from a Data Map in the Groovy script.

operation.application.getDataMap("Data Map Name").createSmartPush().execute(["Account":"Min Bonus, Min Salary"])

Error Trapping

When these are written, it is likely that the Smart Pushes and Data Maps exist.  One extra step to ensure the calculation doesn’t fail is to verify their existence.  For Smart Pushes, verify that it is attached to the form.

//Data Map Example
if(operation.application.hasDataMap("Data Map Name"))
  operation.application.getDataMap("Data Map Name").execute(true)
//Smart Push Example
if(operation.grid.hasSmartPush("Smart Push Name"))
  operation.grid.getSmartPush("Smart Push Name").execute()

Conclusion

Creating variables to use in these functions to change the scope to only the rows and columns that have been edited, the calculation would look like this.  This is where I see the biggest bang for  your buck.  To understand more about using the grid iterator, read Adventures in Groovy Part 3: Acting On Edited Cells.  When a grid has hundreds of rows, only pushing the data that has been edited can make a huge difference in performance.

// Setup the variables to store the list of edited vendors and periods
def lstVendors = []
def lstPeriods = []
// Iterate through only the cells that were changed and create a list of changed vendors and periods
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{ 
 lstVendors.add(it.getMemberName("Vendor"))
 lstPeriods.add(it.getMemberName("Period"))
}
// Convert the lists to a comma delimited string with quotes surrounding the members
String strVendors = """\"${lstVendors.unique().join('","')}\""""
String strPeriods = """\"${lstPeriods.unique().join('","')}\""""
// Execute the Smart Push with a change in scope to Vendor and Period
if(operation.grid.hasSmartPush("GP_SmartPush") && lstVendors)
 operation.grid.getSmartPush("GP_SmartPush").execute(["Vendor":strVendors,"Period":strPeriods]

With any luck, you will see significant improvement, unless they change every month of every row!

I have been selected to speak at the OVOAUG on February 16, 2018.  I have been there before, and it is a very nice group of people to engage with.  If you are in the area, or would like to hear more about how Groovy in PBCS can change the landscape of performance, user interaction, improvement of data input, and reduced user frustration, please go to http://ohio.communities.oaug.org/ and register.  I would love to see as many of you there as possible.

Here is the agenda.  It is going to be a functional overview, but we will touch on how to start writing Groovy, and if you show up, I will be more than happy to talk before and/or after the session.

Introduction

When moving data in PBCS with Data Maps or Smart Pushes, they have limits on how much data can be moved.  The amount of data can be seen in the logs, and look something like this.

Failure

Exporting data…
Exported data file(s) size is: 207.1 MB.
Push Data failed. Error: Exported data size of data map that is being executed from groovy is more than permissible amount: 100 MB.

Success

Exported data file(s) size is: 464.7 MB.
EXPORT elapsed time: 39584
IMPORTING – AppName: AreakFin
TRANSFORM elapsed time: 63634
IMPORTING elapsed time: 21166
TOTAL elapsed time: 124553

Prior to the Feb, 2018 release, the following did not always hold true.  If you are/were seeing inconsistencies, see Bug Report: Push Data failed.  It also includes information about how the data cap works, as it is different between Data Maps and Smart Pushes, which is worth reading.

Data Movement Limits Identified

I got the following information from Oracle, and it is useful if you are using the data movement functionality.  When these are developed, it is a good idea to evaluate the size and plan for growth.  If the production data movements are nearing the thresholds, it is recommended to be proactive and try to reduce the POV that is used to move the data.  If it can’t be reduced, one option is to split it into multiple pushes which can be done with Smart Pushes on the Data Form save, or with Groovy.  Groovy also allows you to further condense the POV by dynamically changing the POV based on the cells edited, which is the most productive and efficient way to handle these.

So, here is what was documented.  The data limits imposed on the movement methods are below.

• There is not a cap when running a Data Map
• When executing the following, there is a cap of 100MB
  • Smart Push on a Data Form
  • Smart Push via a Groovy Calculation
  • Data Map via a Groovy Calculation

Summary

If you are not seeing this, I would recommend opening a ticket with Oracle to resolve.  I will be writing a post explaining how to execute and override POVs in Smart Pushes and Data Maps with a Groovy Calculation in the near future, so look for an article in my Adventures in Groovy series.

Introduction

When developing applications in PBCS, there are times when we need to request information from users.  This is especially useful in workforce application, but certainly not limited to them.  With Groovy, we can take validation to the next level.  We can compare inputs to each other, we can limit input, and we can now stop the execution of the calculation until the inputs are validated.  The difference now is that we have the ability to keep the prompts open, and force the user to either enter valid data, or cancel the request.

Validating Hire Date / Termination Date Use Case

Let’s start with a very simple example.  Let us assume we are adding a new employee, and we are asking for a hire and termination date.  A real-life example would require more options, like a name, title, union, and other required inputs.  To simplify this tutorial, we are only entering the hire and termination dates to prove out the validation and show functionality of the Groovy RTP validation logic.

When a user enters a termination date after a hire date and launches the rule, it validates and executes the rule.

When the job is opened in the job console, we see the RTPs and the values entered, and the Job Status is selected, the log shows the values returned to Essbase.

When a user enters a termination date prior to a hire date and launches the rule, it an error is returned and the RTP window doesn’t go away.  At this point, the user has to correct the error, or cancel the execution of the business rule.

In this case, the log shows the business rule failed.

Code

There are multiple objects that are used to accomplish RTP Validation.  The code that processed the above action is the following.

/*RTPS: {RTP_HireDate} {RTP_TermDate}*/
def mbUs = messageBundle(["validation.InvalidDate":"The termination date must be after the hire date."])
def mbl = messageBundleLoader(["en" : mbUs])

// Validate the Rtp values
if(rtps.RTP_HireDate.getEssbaseValue() > rtps.RTP_TermDate.getEssbaseValue())
   throwVetoException(mbl, "validation.InvalidDate", rtps.RTP_HireDate)

// Print the results to the log
println "Hire Date: " + rtps.RTP_HireDate.getEssbaseValue()
println "Term Date: " + rtps.RTP_TermDate.getEssbaseValue()

rtps object

Creating RTPs in Groovy was covered in the previous article.  If you haven’t read that, it would be a good time to take a look, as it explains the basic of this object.  Expanding on the use of the object, we are using some additional methods.  This object has many, including returning the input as boolean, double, strings, dates, members, and smart lists, to name a few.  In this example, we are using getEssbaseValue, which returns the value sent to Essbase and stored.  If there was a need to compare date ranges, we could have used the getDate, and expanded on this with the Groovy date functions to get the days, months, or years between the entered values.  In this simple example, we just want to make sure the term date is greater than the hire date.

messageBundle

The first thing that is requires is to create a messageBundle and messageBundleLoader.  These two objects work together to hold the errors, the error messages, and multiple languages, if required.

The messageBundle is a map that holds all the errors (name and description).  In this example, we only have one error, but more can be added and separated by commas.  The messageBundleLoader holds all the messageBundle objects that represent the different languages.

throwVetoException

When an exception is found, executing this method will initiate an error and cause the RTP validations to fail.  This method requires the messageBundleLoader, and the error to be returned to the user.

Other Use Cases

By now you are probably already thinking of other uses of this.  I can see limiting growth rates, confirming combinations of RTPs (like not allowing salaried people in a union), ensuring that a new employee doesn’t have a hire date prior to the current date, and probably hundreds of other ways to use this.

If you would like to share an idea, please post a comment!

Conclusion

Being able to validate user input prior to executing a calculation and returning the input request to the user is  huge step forward, and just another benefit of Groovy calculations.  We can reduce the number of user errors and improve the user experience.

Introduction

One of the fundamental features of a Groovy calculation is the ability to dynamically grab aspects of a grid, and getting the POV is something that is required to dynamically generate an Essbase calculation.  There are times when the entire POV is required, times when only members from specific dimensions are needed, and situations where only the rows and columns of the edited cells are used to construct effective fix statements.  All 3 of these situations will be discussed.

Use Case (Pulling POV Members)

Many times, the Fix statement of a calculation being built includes all members in a data grid POV.

Code

List<String> povMemberNames = operation.grid.pov*.essbaseMbrName 
String calcScript = """   
Fix("${povMemberNames.join('", "')}")
 [Calculation]
EndFix;"""

Breaking It Down

The operation.grid.pov provides access to all the dimensions in the POV.  From that, dimension names, member names, and other properties that are useful, can be accessed.  When followed by a *, it returns all the dimensions in the POV to a list.  Using the essbaseMbrName instructs the function to return every member in the POV.  The povMemberNames variable stores a list  of all those values.

When building the calcScript variable, ${povMemberNames.join(‘”, “‘)} will return the list, delineated with “,”.  This would return something like membername1″,”membername2″,”membername3.  This obviously is missing surrounding quotes, which is why it is embedded inside the quotes.

Use Case (Pulling Selective Dimension Members From The POV)

Pulling one, or all of the dimension values in a POV, in individual variables for later use, provides the ultimate flexibility.  The following pulls all the members and stores them in a unique variable.  Then, any dimension in the POV can be accessed.  An easy way to accomplish this is to use the find method of a List object to filter the elements.

Code

List<String> povmbrs = operation.grid.pov
String curYear = povmbrs.find {it.dimName =='Years'}.essbaseMbrName
String curCompany = povmbrs.find {it.dimName =='Company'}.essbaseMbrName
Fix((${fixValues(curYear, curCompany))
 [Calculation]
EndFix;"""

Breaking It Down

The first line stores the entire POV collection.  That variable can be accessed and used like any Groovy collection.  By using the find method, items in the collection can be filtered.  povmbrs.find {it.dimName ==’Years’} will return the Years dimension object and one of the properties of that object is the essbaseMbrName.  Using it will return an Essbase friendly member name.

The “fixValues” method converts strings to “Fix friendly” strings that can be used in an Essbase calc script.  Any of the following objects can be inserted into this method.

• AttributeDimension
• AttributeMember
• Dimension
• Member
• PeriodDimension
• RtpValue
• YearDimension

The result is the dimension member name inside a fix statement.

Rows/Columns

Some of the real efficiencies with Groovy stem from the fact that the edited cells can now be determined and a Fix statement can dynamically be generated to isolate longer running calculation on only the rows and columns that have changed.  In the following example, there are two methods to get the members.

Code

Set<String> periods = []  
Set<String> products = [] 
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each { DataCell cell ->         
  periods << cell.periodName       
  products << cell.getMemberName("Product")     
  } 

String calcScript = """   
Fix("${periods.join('", "')}", "${products.join('", "')}")
 [Calculation]
EndFix;"""

Breaking It Down

If you are unclear about how to iterate through a grid, read Adventures in Groovy Part 3: Acting On Edited Cells.  Inside the dataCellIterator, the example assigns two list objects with the respective members.  Since this only touches the cells that have been edited, only the periods and products that have been updated will be stored in the respective variables.

You may be wondering if this is truly the most isolated combination of data.  Theoretically, the same product may not have the same edited months.  You could further condense changes in the Fix statement by looping through the rows and creating a fix for every row.  In this example, Period is dense, so doing this would not change the number of blocks.  Depending on the application, taking this extra step might prove more efficient.  Rather than complicate this further, we are assuming this isn’t required.

Every required dimension (Account, Entity, Period, Scenario, Version, and Year) has its own method. cell.periodName returns the Period member of the cell.  All the dimensions have the same naming convention.

For custom dimensions, getMemberName can be used with the required dimension passed as a parameter.  If you want consistency, this method can also retrieve the 6 required dimensions.  cell.getmemberName(“DimensionName“), where DimensionName is an actual dimension, returns the respective member in that dimension.

Just like the previous example, add the variable to the Fix statement with a join and it returns the delimited list of members that have been edited.

• “${periods.join(‘”, “‘)}” returns the list of Periods that have edited cells, and
• “${products.join(‘”, “‘)}” returns the rows (or products) that have been edited.

Wrapping Up

One last step that can be added, and should be, is the check to see if any cells have been modified by evaluating the size of one of the lists that is created during the grid iteration.  After the iteration is completed, the following can be added to accomplish this.  If there are no cells edited, the calculation is stopped at the return line and nothing is sent back to Planning/Essbase to execute.

if(products.size() == 0) {
    println("No edited cells found!")
    return
}

Joining the examples above, the fix would look like this.

String calcScript = """  
 Fix("${povMemberNames.join('", "')}", "${periods.join('", "')}", "${products.join('", "')}", ${fixValues(curYear, curCompany))
   [Calculation]
EndFix;"""
return calcScript

Conclusion

If you implement this, you will likely see huge performance improvements.  In the client production applications, I have implemented this in, I see at least a 95% improvement in performance.  This same logic can be used for Data Maps and data movements from ASO to BSO (which we will cover later)

Introduction

Accessing Substitution Variables is critical in most calculations, and accessing them in Groovy is a little more complex than it needs to be with not having an API to get them.  Since the SubstitutionVariable is not available, there are a couple ways to get them.  The precursor to this post is three-fold.

1. Read the Bug Report: Groovy SubstitutionVariable Class Not Functioning  post on Jan 8, 2018 regarding the SubstitutionVariable class availability.
2. Thanks to Abhi for providing a great alternative.
3. It may be helpful to read Adventures in Groovy Part 4: Run Time Prompts to understand how to access RTPs in a Groovy calculation.

In my bug report above, I suggested grabbing them via a hidden column or row from a form.  A reader suggested a another way to do this, and I think it is a better way to accomplish it.  Rather than grabbing the substitution variable by adding it to the form and hiding the column/row from the user, Abhi provided a much cleaner approach to working around not having access to the SubstitutionVariable class by using hidden RTPs.

Create Run Time Prompts to Access Substitution Variables

Assume the following 3 variables are required in business rules.  Create a new RTP for each.  The naming convention is irrelevant, but should be considered and be consistent for easy reference in the business rules.  In this read, I have assumed there isn’t an existing RTP with the defaults set to a substitution variable.  Even if there is, it might be beneficial to create ones specifically for this need so future changes don’t impact the values.

Name: subVar_CurMonth
Type: Member
Dimension: Period
Default Value: &v_CurMonth
RTP Text: N/A

Name: subVar_CurYear
Type: Member
Dimension: Period
Default Value: &v_CurYear
RTP Text: N/A

Name: subVar_BudYear
Type: Member
Dimension: Period
Default Value: &v_BudYear
RTP Text: N/A

Business Rule Inclusion

Inside the business rule, the following convention is required to add the variables.

/*RTPS: {subVar_CurMonth subVar_CurYear subVar_BudYear}*/

Set all the RTPs in the Variables tab to set to hidden so the user isn’t prompted for these.  Now, the substitution variables can be referenced.

def varCurMonth = rtps.subVar_CurMonth.toString()
def varCurYear = rtps.subVar_CurYear.toString()
def varBudYear = rtps.subVar_BudYear.toString()

Conclusion

Since these are likely to be used in many rules, it would be beneficial to add these to a script and embed that script into the rules that need to access these.  Any new variable that needs to be included can be added to the script, and all the business rules would then have access to them.  There are a number of ways to do this with Groovy calculations, but the simplest way is to embed it like a non Groovy business rule.  This can be dragged from the left pane, or entered manually.  The syntax is

%Script(name:="script name",application:="application Name",plantype:="plantype name"

If and when Oracle releases the class that provides direct access to sub vars, expect it to be documented here.

Introduction

Groovy provides a very easy way to interact with the user via run time prompts, or RTPs.  These can be linked to dimensions, members, and input.  One of the huge benefits of getting RTPs in Groovy is that the result can be validated, and the calculation can be cancelled if they don’t validate (we will touch on this in a future post).

The Solution

This is one of the easier things to do with a Groovy calculation.  There are two things required.  First, the Groovy calculation must prompt the user to select a value.  This is done by doing the following.

/*RTPS: {RTP_Consolidate_Data}*/

At any point in the script after the above, the value can be used.  If it is going to be used multiple times, it might be easier to set a variable.  Regardless of the approach, the value can be referenced using the rtps object as follows.

 String sRTP
 sRTP = rtps.RTP_Consolidate_Data.toString()

That is all that is required!

Conclusion

Beyond the obvious uses of an RTP, I have started using these for a number of other reasons.

• On global forms where multiple values may be changed throughout a short period of time and execute long running calculations, like allocations, I have seen benefits of prompting a user with a yes/no smartlist RTP.  If the user has more changes, they may not need to execute the calculation after every save.  This gives them the option.
• If there is a requirement where some prompts are dependent on other prompts, using RTPs in Groovy gives you the flexibility to validate the combination.  For example, if an employee is set to hourly with a VP title, the prompts can be validated and returned to the user as invalid combinations before the prompts are removed from user view.