Creating members on the fly and Planning functions are no longer a new gig to boast about. In case you still need some hint, please click on above hyperlinks to navigate to Oracle guide to throw some light on them.
How about we try to combine the two and come up with a unique requirement where we create a member of the fly and then use it with planning function ? Today I would like to talk about a similar scenario which was more of an attempt to use the planning function to get the start period of a scenario member and drive calculations by scenario start period rather than using substitution variables. This would enable same calculations being executed against multiple scenarios which may have different start periods.
We can either have pre-created scenarios or give an option to create one on the fly. Let’s go with second option and be able to run some calculations on a newly created member via form.
I created a scenario and enabled it for dynamic children.
Created a form which essentially allows user to create a new scenario on the fly and then use that scenario as POV. The user also has an option to change values and then run the calculation. When performing the calculations, the calculation should run for the start and end period of the scenario only.
Created a form which shows the dynamic scenario parent and user can trigger the rule to create a new scenario.
The associated rule looks like this. Dynamic_Scen is the Calc Manager variable which captures the name of scenario and rule is enabled for creation of dynamic member. This creates the new member and assigns a value 1 to the month of July.
After execution, change the POV to the newly created scenario member and we can see value 1 for the month of Jul.
At this point we like to run the second rule - Calc Scen to assign a value of 2 to selected scenario in the form. On running this rule, Jul month should be assigned a value of 2. Before that lets review the start and end period for the newly created scenario. Since the member was dynamically created, it inherits the properties of the parent member which was enabled for dynamic member creation.
Execute the rule and select the newly created scenario as RTP.
But this doesn’t work as expected and we get an error. On checking the job, it says there was an issue with FIX Statement. Note the name of the scenario, which gets passed a the RTP.
Apparently, the Planning function [[getStartMonth(“Scenario”)]] is unable to work with the dynamically created scenario and cannot return the start month. Since the application is not yet refreshed, the scenario name returned to the Essbase for calculation is the temporary named assigned to the member, in this case the parent member name with a suffix. This in turn causes issue as Calc Manager is unable to pull the start month from Planning on basis of this temporary name and thus we get null returned as the start month. However, if we refresh the application and the temporary member gets converted into the member with name "New_Scenario_5" in Essbase, we will not receive this error.
Solution
How can we overcome this issue ? There are two ways - the traditional Calc Manager Script and other Groovy. I would talk about the Groovy way in next post, but lets look at the traditional way first.
The planning function [[getStartMonth({Scenario})]] can accept either a RTP or text, it cannot accept any calc manager function or Essbase function. Moreover, the member created dynamically inherits the start and end period of its parent when created dynamically. If we pick the start and end month of the parent which was enabled for dynamic member creation, it would align with the start and end period of newly created member too. Since the parent was created and Essbase refreshed, Calc manager should not throw error when attempting to pull the start period. But this would require creating two separate rules, one which is executed for other members and the one which is executed for dynamically created members. Not a neat solution, but a workaround if you do not have access to Groovy.
Hope this helps !!!
