Essbase Custom function for time calculation

This entry was posted in CDF Essbase on by (updated 2301 days ago)
I once got a requirement where in which the client wanted me to set a time-stamp in Essbase.
 
The requirement was like this…..Some of the members that they use in planning has an expiry date set (they will input 201009 if the member is expiring on 2010 September).
 
Suppose if some of the Planners entered values for the members that are already expired, they wanted me to clear the values and then populate today’s date (like 20101102 if I cleared the value on 2010 November 2nd)
 
I was not able to create a function at that time and a workaround was provided to create a substitution variable with yyyymm as the value. (this was used to populate the member which will hold the yyyymm value)
 
Later I thought why not create a function so that I can use that in the Calc script (instead of using a sub var).
 
So I made use of Gregorian Calendar in Java and created a custom function….
 
To use this function follow the steps below.
  • Download the jar file, create a folder called udf in %Essbase_Java_Home% (e.g C:OracleMiddlewareEPMSystem11R1productsEssbaseEssbaseServerjava Please change your path accordingly, mine is a 11.1.2 installation)
  •  Edit udf.policy file in %Essbase_Java_Home and add the lines given (highlighted in blue) below.
          // Grant all permissions to classes internally
         // used by Essbase.
         grant codeBase “file:${essbase.java.home}/essbase.jar” {
         permission java.security.AllPermission;
         };
         grant codeBase “file:${essbase.java.home}../java/udf/cerahyputils.jar” {
         permission java.security.AllPermission;
         };
  • Download the installation bat and Maxl file from here.

  • Run the register_date_function.bat and restart Essbase Service and EAS Service.
You’ll get two new functions in Essbase @Get_Cur_Date and @Change_Cur_Date.


I created a Calc script as given below to get yyyymmdd


“CURYYMMDD”=@Get_Cur_Date (1)*10000+@Get_Cur_Date (2)*100+@Get_Cur_Date (5);


If you look at the Java month….it starts from 0 to 11. (You don’t need to worry about this…I’ve taken care of that in the code.)


Suppose you want to change the Year, Months to Prior Year, Month, you can use

  • @Change_Cur_Date(1,1) for prior Year
  • @Change_Cur_Date(2,1) for prior Month
  • @Change_Cur_Date(5,1) for previous day.
Since all the values are integers (numbers) months/days less than 10 will show as single digits only…..so make sure you are not using the formats that starts with Month/Day (e.g. mmddyyyy)

Sample script

FIX(“100-10″,”Jan”,”New York”,”Budget”)

“Sales”
(
/* Returns date in the format 20130730 */
“YYYYMMDD” = @Get_Cur_Date (1)*10000 + @Get_Cur_Date (2)*100+@Get_Cur_Date (5); 

/* Returns 4-digit year 2013 */
“AC_Year” = @Get_Cur_Date (1);

/* Returns 2-digit month 07 */
“AC_Month” = @Get_Cur_Date (2);

/* Returns 30 for 2 digit day */
“AC_Day” = @Get_Cur_Date (5);

/* Returns Hour in 24 hour format*/
“AC_Hour” = @Get_Cur_Date (11);

/* Returns Minute*/
“AC_Min” = @Get_Cur_Date (12);

/* Returns Seconds*/
“AC_Sec” = @Get_Cur_Date (13);
)

ENDFIX

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “Essbase Custom function for time calculation”