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
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
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
For the updated jar files and installation file please check https://orahyplabs.com/2010/11/left-and-right-functions-in-essbase.html
Hello Celvin,
I have a similar situtaiton. I am trying to get Email Alert Using Calculation Script in Essbase.
I have defined all the functions and using the below script
RUNJAVA com.oracle.essbase.cdf.SetEmailProp "target:target1" "subject:Subject Line";
RUNJAVA com.oracle.essbase.cdf.SendMail target:target1 server:smtp.adomian.com
from:user1@domain.com to:user2@domain.com "body:Body of email goes here.";
The script is working fine but i am getting no email. Any suggestion will be helpful
Check whether your SMTP server needs authentication to send mails. If so then you'll have to provide sender's password.
If you are trying to get hour minute and second information you can use
@Get_Cur_Date (11) will give you HH
@Get_Cur_Date (12) will give you MM
@Get_Cur_Date (13) will give you SS
Celvin, this is awesome! Here's some sample code if others are looking to do the same.
Outline (non-consolidating members in Account dimension):
ACCOUNT
DATABASE_INFO
YYYYMMDD
YYYY
MM
DD
Calc script excerpt:
/* Returns 20130730 */
"YYYYMMDD" = @Get_Cur_Date (1)*10000 + @Get_Cur_Date (2)*100+@Get_Cur_Date (5);
/* Returns 4-digit year 2013 */
"YYYY" = @Get_Cur_Date (1);
/* Returns 2-digit month 07 */
"MM" = @Get_Cur_Date (2);
/* Returns 30 for 2 digit day */
"DD" = @Get_Cur_Date (5);
Mike, good to know that it worked for you
Where are the UDF/CDF files kept? I assume each is it's own code in a Jaav library somewhere – and I need to migrate them from DEV to UAT for an EPMA app using LCM and LCM cannot see the functions that are in EAS.
Thanks
Have a look at udf policy file and that will tell you which jar file is been used. LCM cannot do that migration. You've to register those functions
Hi Celvin
I followed the steps mentioned by you. But i am getting the below error.
[Sun Nov 17 18:31:03 2013]Local/Test///5180/Warning(1200490)
Wrong java method specification [com.cera.hyperionUtils.HypDate.curdate(int)] (function [@Get_Cur_Date]): [specified class not found:com.cera.hyperionUtils.HypDate]
Can you help me in knowing .. why i am getting this.
Regards
Sandeep Kumar Upadhyay
@sandeep Did you register it? Did you update the udf policy?
Hi Celvin – This is a great tool that I hope to be working with reallllll soon. 🙂 Question (because I am not a Java person at all)…I keep getting an error that I cannot resolve. I have tried changing the CDF's to application specific ones (so I can start/stop my application on my own time w/o waiting weeks for a service restart by the client's IT group) but still with no luck. Any thoughts?
Error: 1200324 Error compiling formula for [Actual] (line 9): operator expected after [@Comp_String_Equals]
Sarah, did you look at https://orahyplabs.com/2010/11/left-and-right-functions-in-essbase.html, I've given some examples there
Even though it is registered at all app level, you can still stop and start application so that the changes are reflected. Could it be about how you register the function.
Hi Vishal,
Did you get this to work?
Thanks,
Mahathi
hi, I have the following problem, perform all the steps but when I try to run the rule from calc manager does not perform the function.
The function ‘@JPMT’ cannot be found.
Why? Sorry my english is bad.
It looks like the function didn't register correctly, did you register the function
Hi All,
My Email is getting triggered after running the calc Scripts , but i wanna get which user is running the Script
Thanks
Well that is tricky, I don't think there is a function which get you the username. It is going to be there in the log file.