Set Next Month, Previous Month using Current Month Substitution Variable


This is an interesting one, not directly related to EPM however a useful one.
I know many of you have used different techniques for setting Substitution variables.
  1. Getting them from a database
  2. Updating using an EXCEL macro
  3. Using System date
I have done couple of those and there was one interesting one where I gave the user the capability to load any month they wish using a batch file.
So this batch file, a user can define the month they want to run and then using that parameter it’ll set a data in the datastore and views will be automatically build according this to month-year combination.
It then sets Essbase substitution variables using the variable set in the datastore.
While doing that I had to derive the number for the month that I’m loading so that I can populate a field. I’m not going to talk about that method – I’m talking about a similar one.
Business would like to set up a variable (not a bunch of them) which will tell them that Hyperion Actuals are closed. They will control this, and then build rest of the substitution variables based on that one.
So here is the batch script which does that.

%ARBORPATH%\bin\essmsh.exe displaysubvar.msh %ESSSERVER% %APPNAME% FinalHypActMoYr %ESSUSER% %ESSPASSWORD% %SPOOL_FILE%


FOR /F "usebackq tokens=3 skip=1" %%a IN (`"findstr /c:"FinalHypActMoYr" %SPOOL_FILE%"`) DO SET HYPACTMOYR=%%a

 REM # Below code will convert the month from essbase to a corresponding month number
 REM # Month is mapped to a number, so that we can get the month number from Essbase
 REM # first 3 characters is mon %HYPACTMOYR:~0,3%
 SET map=Jan-1;Feb-2;Mar-3;Apr-4;May-5;Jun-6;Jul-7;Aug-8;Sep-9;Oct-10;Nov-11;Dec-12
 CALL SET HYPACTMOYRNUM=%%map:*%HYPACTMOYR:~0,3%-=%%
 SET HYPACTMOYRNUM=%HYPACTMOYRNUM:;=&rem.%

 REM # Here we are adding a month, so that we can get the next month
 set /a RPTGMOYRNUM=%HYPACTMOYRNUM% +1

 REM # Below code will convert the number to a corresponding month
 REM # Number is mapped to Month and will be converted back to MON
 SET map=1-Jan;2-Feb;3-Mar;4-Apr;5-May;6-Jun;7-Jul;8-Aug;9-Sep;10-Oct;11-Nov;12-Dec 
 CALL SET RPTGMOYR=%%map:*%RPTGMOYRNUM%-=%%
 SET RPTGMOYR=%RPTGMOYR:;=&rem.%
 REM # add year to the month
 SET RPTGMOYR='%RPTGMOYR%%HYPACTMOYR:~3,3%'

REM ####################### Extract Sub Var from TestApp ################################
 
%ARBORPATH%\bin\essmsh.exe setsubvar.msh %ESSSERVER% %APPNAME% RPTgMoYr %ESSUSER% %ESSPASSWORD% %SPOOL_FILE% %RPTGMOYR%

How does it work

Run a MaxL which will display a Application substitution variable and spool the result to a file.

Use a for loop to get the value of the Sub Var. 3rd token is the value, since mine is an application sub var.
application         database            variable            value  
For loop is on the output of findstr command and we are finding the name of the sub var, output will contain two lines (one the maxl display command and the second with the value), skip 1 line and you get a single line for the for loop
Now comes the magic of Batch script, many people think that it is not powerful enough (I do agree with that on some, not it is not always).
I’ve mapped each month to their number.
Suppose HYPACTMOYR is Sep-14 then HYPACTMOYRNUM will be set to 9;Oct-10;Nov-11;Dec-12, using the rem command it’ll delete everything after 9(; is the delimiter)
So now HYPACTMOYRNUM is 9 and I can now I can add one number.
Reverse map from number to month. RPTGMOYR will be set to Oct;11-Nov;12-Dec, using rem command it’ll delete everything after Oct
Next step is to add YY, which can be derived from HYPACTMOYR (Start from 4th character and give 3 characters “-14”)
You can do set /a RPTGMOYRNUM=%HYPACTMOYRNUM% -1 to get Previous Month

Hope you enjoy it 😉
Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply