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.
- Getting them from a database
- Updating using an EXCEL macro
- 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 😉
