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. Read more
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
Data Map Error:
Push Data failed. Error: Exported data size of data map that is being executed from groovy is more than permissible amount: 100 MB.
If you are confused, join the club. The results are inconsistent as some data pushes are successful that are over the 100MB limit. So, why the following error? Read more
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. Read more
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.
- Read the Bug Report: Groovy SubstitutionVariable Class Not Functioning post on Jan 8, 2018 regarding the SubstitutionVariable class availability.
- Thanks to Abhi for providing a great alternative.
- 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
When an application is used to demonstrate sensitive information, a training class includes people that shouldn’t see live data, or security is being tested, often times using real data is not an option. I have written PowerShell scripts and .NET applications to randomize data from Essbase exports, as well as Custom Defined Functions to randomize with calculations. Read more
If you have jumped into Groovy Calculations, one of the things you likely would try to do is grab a value for a sub var. Hopefully, you haven’t spent too much time before reading this. I wasted a ton of time trying to get this to work before I opened a ticket with Oracle. This class is NOT available yet and was inadvertently included in the public docs at https://docs.oracle.com/cloud/latest/epm-common/GROOV/. The development team told me they are going to remove it from the API docs. Read more
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.
A bug with EPM Automate has been identified. This is not replicated on every version or client. Please pay attention to any EPM Automate updates installed. In the past, I was able to install the latest version without any issues. Currently, the install prompts users to uninstall the older version. In the past, this worked as expected, but now, when selected, this has no effect and the new EPM Automate is NOT installed, leaving you with the existing version. Read more
Introduction
With the introduction of Groovy Calculations this summer, one of the things I use most, especially for applications with data forms that include a large sparse dimension in the rows with suppression on, is the option to loop through cells and identify only the POV on the cells that have changed. Read more