Introduction to the introduction
I wrote this (or as you’ll see in a minute, I didn’t write this) quite a while ago and was holding off on releasing it because…well because I had other things to post.Now I see that Robb Salzmann has posted something pretty similar to what I-didn’t-but-someone-else wrote.
Think of this as an alternate take on his post, or his post is an alternate of this post. You decide.
Introduction
Are you an Antimimetic or a Mimetic? In other words, does art imitate life, or life imitate art?I’m pretty sure my name isn’t Tom Sawyer, and yet I manage to get others to whitewash Aunt Polly’s fence.
This time it’s Frank Chow who’s done all of the hard work.
The problem
Set substitution variables for the current Actual month and year. Big deal, right? Any automated system worth its salt does this.But where do those dates come from? Manual assignment? Magic?
Frank went for code (Is there really any other way?) and had an additional requirement – that the current month increments on the 25th of the month. And oh yes, and it had to handle the extra-special case of January.
NB – The fiscal year is the same as the calendar year. It has to be for the solution below to work as you’ll shortly see.
The solution
DOS will never die, will it? It is breathtakingly cryptic and quite powerful in its limited way. What’s not to love?Let’s dive in:
Setup the Day, Month, and Year variables
:: ********************* Set up the Day, Month, Year Variables **************************FOR /f "tokens=1-4 delims=/ " %%a in ('date /t') do (
set day=%%a
set mm=%%b
set dd=%%c
set yy=%%d
)
set /a pr_mm=%mm%-1
set /a pr_yy=%yy%-1
Pretty cool stuff here – the code is stripping the output of date /t using the backslash as a delimiter and then putting the day, month number, day number, and two digit year into four temporary variables.
Setting up the strings
:: ***************** set up mapping table ************************set monthmap=00-Dec;01-Jan;02-Feb;03-Mar;04-Apr;05-May;06-Jun;07-Jul;08-Aug;09-Sep;10-Oct;11-Nov;12-Dec
::*************** set current month variable ************
CALL SET cur_month=%%monthmap:*%mm%-=%%
SET cur_month=%cur_month:;=&rem.%
::*************** set prior month variable **************
CALL SET pr_month=%%monthmap:*%pr_mm%-=%%
SET pr_month=%pr_month:;=&rem.%
echo current_month: %cur_month%
echo prior_month: %pr_month%
::*************** set current & prior year variables ***********
set cur_yr=FY%yy:~-2%
set pr_yr=FY%pr_yy:~-2%
echo current_year: %cur_yr%
echo prior_year: %pr_yr%
Here a key-value pair is used with CALL SET to translate the month numbers in the variable %mm% to three character strings and is also used to append “FY” to the two digit year values in %yy%.
Here’s a good guide to string manipulation. You’ll find a section similar to the above in the example called “Map and Lookup - Use Key-Value pair list to lookup and translate values”.
Handle month end and January special case
::************ set up CurrActMonth & CurrActYear variables ***************IF %cur_month% EQU Jan goto Var_Jan
IF %dd% LSS 25 goto before25
IF %dd% GEQ 25 goto after25
:Var_Jan
IF %dd% LSS 25 goto before25_Jan
goto after25
:before25_Jan
SET CurrActMonth=Dec
SET CurrActYear=%pr_yr%
goto End
:before25
SET CurrActMonth=%pr_month%
SET CurrActYear=%cur_yr%
goto End
:after25
SET CurrActMonth=%cur_month%
SET CurrActYear=%cur_yr%
goto End
:End
echo CurrActMonth: %CurrActMonth%
echo CurrActYear: %CurrActYear%
Ever wonder what the DOS commands are for >, >, <>, etc.? Check them out here.
Run it if needed
:: ***************** Excute the MAXL script if the date is 25th ***********************IF %dd% EQU 25 goto MAXL_update_date
exit
REM /============ excute MAXL script to update the Substitution Variables ======================/
:MAXL_update_date
essmsh SubVar_CurrActMonth_Year.msh
This code gets run every night. If it’s the 25th, run the MaxL code, otherwise exit.
Do it in MaxL
/* --------------------------------------------------------------------------* LOGIN AND SETUP SCRIPT VARIABLES - &CurrActMonth and &CurrActYear
* -------------------------------------------------------------------------- */
/* NB -- user, password, and server are set up separately as Windows
environment variables. */
login "$ESS_USERID" "$ESS_PASSWD" on "$ESS_SERVER";
/* ---------------- SET UP SUBSTITUTION VARIALBES --------------------------- */
alter system set variable 'CurrActMonth' $CurrActMonth;
alter system set variable 'CurrActYear' $CurrActYear;
logout;
exit;
Remember, the Windows CMD script code set the temporary OS environment variables CurrActMonth and CurrActYear. These are referenced in MaxL with a “$” in front of the variable name.
Conclusion
This code does three things:- Figures out the current month and year based on system date
- Uses some cool DOS functions
- Sets Essbase substitution variables if it’s the 25th of the month and oh by the way handles year end pretty well
Frank asked me what I thought about it – was it any good, could I make improvements, etc. Some say the greatest form of flattery is imitation. Is ripping code off and using it yourself an even greater form of flattery? Whatever your definition, I plan on using this code when I have the same need.
You can get the whole code stream here. Run it with the PAUSE statements in to understand what it’s doing – take those statements out when you want to use it.
