An alternative to using a substitution variable in a calc script IF statement. If you have not read it, start there. This post covers the same requirement written as a Groovy rule.

The Problem

Copy Actual into the Result account for Jul through Jun. The source year depends on the current period:

• When &CurrentPeriod is Jul, take Actual from &PriorYr.
• For any other period, take Actual from &CurrentYr.

Why the calc script version needs special handling

The natural first attempt looks like this:

FIX("Jul":"Jun")   
  "Result"(      
      IF(&CurrentPeriod == "Jul")         
        "Result" = "Actual"->&PriorYr;      
      ELSE         
        "Result" = "Actual"->&CurrentYr;      
    ENDIF)
ENDFIX

Essbase replaces &CurrentPeriod with the member name before the script runs. So the IF does not compare two names. It compares the values at two member intersections.

When both are #Missing, they are equal, and the rule takes the first IF every time.

The original post solves this inside the calc script with @CalcMgrCompare and @NAME, which compares the names as text. It works and it is the right answer if you need to stay in a calc script.

The Groovy version

Same problem but different approach.

//Define Cube
Cube cube = operation.application.getCube("Plan1")
//Read subvar as String
String sCurPeriod = operation.application.getSubstitutionVariableValue("CurrentPeriod")?.replaceAll('"', '')?.trim()
if (!sCurPeriod) {
    throwVetoException("Substitution variable CurrentPeriod is missing or empty.")
}
//Define String to store sSourceYr
String sSourceYr
if (sCurPeriod.equalsIgnoreCase("Jul")) {
    sSourceYr = '&PriorYr'
} else {
    sSourceYr = '&CurrentYr'
}
//Read PriorYr and CurrentYr subvar for the println; this is not needed
String sPriorYr = operation.application.getSubstitutionVariableValue("PriorYr")?.replaceAll('"', '')
String sCurYr = operation.application.getSubstitutionVariableValue("CurrentYr")?.replaceAll('"', '')
println " CurrentPeriod=$sCurPeriod | Source Year=$sSourceYr | PriorYr=$sPriorYr | CurrentYr=$sCurYr"
//Same CalcScript as the original example
String calcScript = """
FIX("Jul": "Jun")
  "Result" = "Actual" - > $sSourceYr;
ENDFIX
"""
println calcScript
return calcScript

How it works

1. operation.application.getSubstitutionVariableValue returns the value of CurrentPeriod as text, for example Jul or Apr, or null if it is missing.
2. equalsIgnoreCase ignores the case and compare the name similarly to @_true in @CalcMgrCompare.
3. IF statement stays in Groovy.
4. &PriorYr and &CurrentYr stay as substitution variables in the script, and Essbase looks for it when it runs.
5. Returns calcScript to run the rule.

This way you don’t have to use CDFs if you run into performance issues.