Adventures in Groovy – Part 5: Accessing Substitution Variables
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.
Hi Kyle,
I followed similar approach, but still i get a prompt. I have selected hide prompts at rule and form level but still i get a launch window.
Regards,
Vishakha
Kyle,
I was trying to get the actual value of a subvar using this method (or any method for that matter), but this only seems to return the name of the subvar. I’ve trying to figure out a way to get the actual value for the subvar to use in the Groovy code. But i dont think that’s possible.
Cheers,
Pete
Hey, thanks for the question. I would have to test this to see if this still provides the entered text. I could have documented this incorrectly. I don’t know what the RTP holds, but try these. Some of these are great because they return the result of what was intended. So, level 0 desc of yeartotal with the member methods return Jan,Feb, etc, even though the string is the descendants(yeartotal). You can loop through the lists to create delimited strings if that is what is needed. There are also date and numeric methods not listed below.
getEnteredValue() does exactly that
getEssbaseValue() is similar but returns a value that can be used in a fix statement, so it has quotes and possibly some other differences
getFixValue() is similar to above
getMember() returns a member object
getMembers() returns a list of members
Hi Kyle,
Thanks for the response and the suggestions. The additional caveat in the case I was trying to work on is that the value entered for the subvar is not an actual member, it’s just a string. It was just going to represent a flag (True/False) so to speak to be used in Groovy. Although, thinking about it, i guess I could add the flags as say account members and then see if one of the functions above will return the value of the subvar and not just the subvar name. I’ll give that a try, thanks for bouncing ideas.
PS
You can use getEnteredValue() or getBooleanValue(). I have done this with a smartlist that has yes/no, or true/false for prompting users. The other thing you might want to consider if the flag is connected to members is have a uda or attribute dim connected to it (assuming attributes are valid for the dimension in question) and you can pull the metadata attributes with groovy. I have done this several times. I have done currency conversion based on the attribute. I have retrieved valid accounts based on whether it is a valid account for a specific plan type. I have also used properties like if it is stored then do something, if it is dynamic ignore (moving data to another plan type). I don’t known what the specific issue is, but with the ability to basically write a switch/case or if statement based on the metadata, you can get pretty creative. I just wrote a calc where it errors if the products that are valid for the product plan type don’t have have an attribute assigned to them so they know that the metadata load is missing something. That calc is executed from epmautomate and if it fails, it sends the admin an email. They love it!
Kyle,
Thanks for the feedback and conversation. It helped spark some ideas and I got the code working as I wanted. I had to set up the RTP (replacement variable) as a member instead of a string. I then created two members (True/False) in the in the Account dimension. Now using the rtp toString function returns the member name and not the default value specified in the rtp variable. So, the setup consists of a RTP variable like below
Name: rtp_RunFx
Type: Member
Dimension: Account
Default Value: &RunFX
RTP Text: N/A
And the code snippet below will extrapolate the value set for the RunFX subvar.
/*RTPS: {rtp_RunFx}*/
String varRunFx = rtps.rtp_RunFx.toString();
String cString;
if(varRunFx.equalsIgnoreCase(‘”True”‘)) {
cString = “Flag is true, execute logic”;
}else{
cString = “Flag is false, don’t execute logic”;
}
//output to check results
println cString;
println varRunFx;
I’m looking to use this in a calc rule that will run via EPMAutomate and will allow the calc to be turned “on/off” by setting the associated subvar to True or False.
Hi Kyle,
I am reading through these posts as I start my groovy journey and wanted to comment on this one to thank you for taking the time to write these!
I also wanted to add that you can get subvars into a groovy script easier now… not sure if this existed back at the time of writing, but the Cube class has a .getSubstitutionVariableValue() method which will get a subvar value and pull it into memory for use in the script.
So if I have a cube called “BUDCUBE” and a subvar called BudYr1 set to FY21, I could pull FY21 into the groovy rule by doing:
Cube cube = operation.application.getCube(“BUDCUBE”)
def var_BudYr1 = cube.getSubstitutionVariableValue(“BudYr1”)
You could put this in a header script that does this for every subvar in the cube, then you can use them freely in groovy!
Thanks for sharing the example. This was indeed added long after this post. I believe it was added mid FY20.