Using Planning Calc Manager Variables in Essbase Calculation script with SET RUNTIMESUBVARS command

On a recent project, I wanted to execute an Essbase Calc script with substitution variables set to accept values passed by Planning business rule run time prompts. In this specific case, I wanted a user to run a business rule in a planning application that would use the run time prompt values and pass on to a calculation script in a separate standalone Essbase database. I used a combination of CDF in business rule that executed a MaxL which in turn passed the prompts values to runtime substitution variables in the Essbase Calculation script. I found this functionality to be handy as this opens up for a lot of use cases.

The process flow was:

Created variables (set up as run time prompts) in Calculation Manager for e.g. {Scenario} {Version} {Years}

Created a calculation script and defined runtime substitution variables corresponding to each calculation manager variable created in step1. Runtime substitution variables to be used in calculation scripts must be declared by using the SET RUNTIMESUBVARS command. A sample of the code in the calculation script is given below:

SET RUNTIMESUBVARS
  {
   Versionname;
   Scenarioname;
   Yearname;
  };

FIX (&Yearname, &Scenarioname, &Yearname, @Relative("Product",0),……)
…. ;
ENDFIX

Next, created a MaxL file with command to execute the calculation script as given below. Note the order of variables in the statement and pay special attention to the format.

execute calculation $4.$5.'CalcData' with runtimesubvars "'Scenarioname =$6; Versionname =$7; Yearname=$8;'";

Created a business rule with a CDF calling a MaxL file. The CDF I used was RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions. Sample code is given below. Note that {Scenario} {Version} {Years} are the variables I created in Calc Manager, value of which I want to pass on to a MaxL and then on to the Calc Script.

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions "false" "C:\MaxL\CalcData_CDF.mxl" "'admin@Native Directory'" “’password’” localhost appname dbname {Scenario} {Version} {Years};

When the business rule is executed, it prompts the user to assign values to the run time prompts. The values for these prompts are then passed by MaxL to the Essbase runtime substitution variables defined in the calculation script using the SET RUNTIMESUBVARS command. The Essbase calculation script is then executed based on the values of the Essbase runtime substitution variables.