LEFT and RIGHT functions in Essbase 13


Since these days I’m working (playing around) a lot with Custom things….I thought why not create some custom functions in Essbase.

Actually the seed for this was implanted by my best friend…..Mihriban……(maybe a hangover of Inception)

So my dear Mihriban Hanam this is for you šŸ™‚

We were once discussing why Financial Reporting is not having a substring function…..I’m still to figure out how to add it to FR šŸ˜‰ …..but then it was time for Essbase…..

So here we go…..I’ve created 4 string functions…
  • LEFT is similar to the EXCEL Left function
  • RIGHT is similar to EXCEL Right function
  • EQUALS
  • CONTAINS
I used Sample Basic to check the functions…..

LEFT

FIX(“100-10″,”Jan”,@DESCENDANTS (“Market”),”Budget”)

“Sales”

(

   IF(@Comp_String_Equals (@LEFT (@NAME(@CURRMBR(“Market”)),3),”new”) ==0)

       “Sales” = 1;
   ELSE
       “Sales” =0;
   ENDIF
)
ENDFIX


Result


New York, New Hampshire, New Mexico got 1.

RIGHT

FIX(“100-10″,”Jan”,@DESCENDANTS (“Market”),”Budget”)

“Sales”

(

   IF(@Comp_String_Equals (@RIGHT (@NAME(@CURRMBR(“Market”)),2),”on”) ==0)

       “Sales” = 1;
   ELSE
       “Sales” =0;
   ENDIF
)
ENDFIX


RESULT


Oregon, Washington got 1

CONTAINS

FIX(“100-10″,”Jan”,@DESCENDANTS (“Market”),”Budget”)

“Sales”

(

   IF(@Comp_String_Contains((@NAME(@CURRMBR(“Market”))),”sh”) ==0)

       “Sales” = 1;
   ELSE
       “Sales” =0;
   ENDIF
)
ENDFIX


RESULT


New Hampshire, Washington got 1

You can get the JAR file from here.
You can get the installation file from here.

For Installation instructions go here

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.

13 thoughts on “LEFT and RIGHT functions in Essbase

  • Shantan Kommera

    Celvin,

    Any possibilities of having something like @STRINGTONUM(@CURRMBR("Entity"))

    for example my entity dimension has all numbers as dimension members, and essbase treats them as string, upon certian conditions if i happen to store the member of entity dimension as data to any other member in other dimensions then this function will be very useful.

    Hope i am making sense in my above explanation.

    Shantan Kommera

  • Sivaprasad Rayala

    Hi Celvin,
    I am getting error below after restarting EAS & Essbase server
    for the Function

    IF (@Comp_String_Equals (@LEFT (@NAME(@CURRMBR(“AFE”)),1),”B”) ==0)

    Error: 1200324 Error compiling formula for [Remaining Bucket Spend] (line 25): operator expected after [@Comp_String_Equals]

          • Sivaprasad Rayala

            Wrong java method specification [com.cera.hyperionUtils.HypStringtoInt.strtoint(String)] (function [@StringtoInteger]): [specified class not found:com.cera.hyperionUtils.HypStringtoInt]

            [Mon Feb 12 13:49:37 2018]Local/Sample///4264/Warning(1200490)
            Wrong java method specification [com.cera.hyperionUtils.HypStringtoDouble.strtodouble(String)] (function [@StringtoDouble]): [specified class not found:com.cera.hyperionUtils.HypStringtoDouble]

            [Mon Feb 12 13:49:37 2018]Local/Sample///4264/Warning(1200490)
            Wrong java method specification [com.cera.hyperionUtils.HypStringtoDoublewReplace.strtodoublewrplc(String,String)] (function [@StringtoDoublewReplace]): [specified class not found:com.cera.hyperionUtils.HypStringtoDoublewReplace]

            [Mon Feb 12 13:49:37 2018]Local/Sample///4264/Warning(1200490)
            Wrong java method specification [com.cera.hyperionUtils.HypStringtoIntwReplace.strtointwrplc(String,String)] (function [@StringtoIntegerwReplace]): [specified class not found:com.cera.hyperionUtils.HypStringtoIntwReplace]

            [Mon Feb 12 13:49:37 2018]Local/Sample///4264/Warning(1200490)
            Wrong java method specification [com.cera.hyperionUtils.HypDate.curdate(int)] (function [@Get_Cur_Date]): [specified class not found:com.cera.hyperionUtils.HypDate]

            [Mon Feb 12 13:49:37 2018]Local/Sample///4264/Warning(1200490)
            Wrong java method specification [com.cera.hyperionUtils.HypChangeDate.changedate(int,int)] (function [@Change_Cur_Date]): [specified class not found:com.cera.hyperionUtils.HypChangeDate]

            [Mon Feb 12 13:49:37 2018]Local/Sample///4264/Warning(1200490)
            Wrong java method specification [com.cera.hyperionUtils.HypStringLeft.left(String,int)] (function [@LEFT]): [specified class not found:com.cera.hyperionUtils.HypStringLeft]

          • Celvin Kattookaran Post author

            So the registration is not successful, did you download the jar file? Did you update udf policy file?