My 20+ years of using Essbase I was told, and had no reason not to believe, only stored numbers. We obviously have lists and with Planning it appears we have text. If you aren’t familiar with how this works, the strings are stored in the Planning repository and the index is stored in Essbase. If you thought the same thing, you were as wrong as I was.
Read moreTag Archive for: calculation
Most of us know that there is a button in the calc rule editor that allows us the ability to select a smart list and the smart list entry. It will add something [[smartlist name.smartlist entry]]. If this is new to you, what it does is replace reference the smart list and replaces it with the numeric value that exists in Essbase. The beauty of this is that it is dynamic, so if the smart list is changed in any way, you don’t have to go into your rules and replace change the index values for the smart list entries to match. Guess what, there are more!
Read moreIntroduction
Before we jump in, there are a number of questions you are going to have at the conclusion of this article. Please post comments and I will answer them, but keep in mind, this is an example. Are there different ways to accomplish this? You bet. Should the data sync directly to the rFin database? Probably not, as there are calculations in the fin database that likely need to happen. This could be so complicated that nobody would follow it, so some liberties have been taken to simplify the explanation. The hope is that you can take this, as it has all the pieces required, and modify, add pieces, alter others, and be able to create something that meets your needs. This is a continuation of Part 18. Please read that before you continue.
|
|
Introduction
Chris Hull has been kind enough to partner with us to present how the methods available in Groovy calculations have made a huge impact in their budgeting and reporting process using PBCS. Read more
|
|
Introduction
One of the challenges with Hyperion Planning is the ability to move data between applications in real time. A classic example of this is a P&L application with other modules that have greater detail. The following is an example. Read more
|
|
Introduction
One of the huge benefits that available in Groovy Calculations is the ability to interact with a user, validate data, and act on the validation. Now, we can interrupt form saves, stop Run Time Prompts from continuing, and communicate information back to the user.
This may sound repetitive if you have read part 13 and part 14, and you can skip to the code example to learn more about run time prompt validation. If not, you must have an understanding of the validation functions and the components of the messageBundle. Read more
|
|
Introduction
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. 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
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.
|
|
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
|
|