Essbase String to Number CDF 8


I was looking at the comments on my post about String Essbase CDF functions and found an odd request there.
I couldn’t find a reason why someone would try doing that. However I ending up creating 4 functions to perform the request!!!
The request was to create a String to Number function.
Let’s say that you want to convert some of your metadata to data in Essbase, there is no function that exists now which can do this. (I’m not sure whether there are some CDFs out there).
Let’s see how it works.
I took Sample Basic and created two members which will hold the integer and double values.
StringInteger and StringDouble

 

FIX(@RELATIVE("Product",0),
 "Jan",
 "New York",
 "Budget")

  "StringInteger"
  (

   @StringtoInteger(@SUBSTRING(@NAME(@CURRMBR("Product")),4)) ;

     )

  "StringDouble"
  (

   @StringtoDouble(@CONCATENATE(@CONCATENATE(@SUBSTRING(@NAME(@CURRMBR("Product")),0,3),"."),@SUBSTRING(@NAME(@CURRMBR("Product")),4))) ;

     )
 
ENDFIX

 

For the next two functions, you can pass the replacement character (for example product name contains “-” in it and I would like to remove it and store the numbers)
StringDoublewReplace and StringIntegerwReplace members were added to hold these numbers.

 

FIX(@RELATIVE("Product",0),
 "Jan",
    "New York",
 "Budget")

  "StringDoublewReplace"
  (

    @StringtoDoublewReplace (@NAME(@CURRMBR("Product")),"-");

     )
 
  "StringIntegerwReplace"
  (

    @StringtoIntegerwReplace (@NAME(@CURRMBR("Product")),"-");

     )

ENDFIX

 

You can register the function using the below given MaxL statements.

 

CREATE or REPLACE FUNCTION '@StringtoInteger' AS 'com.cera.hyperionUtils.HypStringtoInt.strtoint(String)' SPEC '@StringtoInteger(String)' COMMENT 'Convert String to Integer @StringtoInteger("100") will give you 100 as an integer';

CREATE or REPLACE FUNCTION '@StringtoDouble' AS 'com.cera.hyperionUtils.HypStringtoDouble.strtodouble(String)' SPEC '@StringtoDouble(String)' COMMENT 'Convert String to Double @StringtoDouble("100.10") will give you 100.10 as a Double';

CREATE or REPLACE FUNCTION '@StringtoDoublewReplace' AS 'com.cera.hyperionUtils.HypStringtoDoublewReplace.strtodoublewrplc(String,String)' SPEC '@StringtoDoublewReplace(String,String)' COMMENT 'Convert String to Double with replacement option @StringtoDoublewReplace("100-10","-") will give you 100.10 as a Double';

CREATE or REPLACE FUNCTION '@StringtoIntegerwReplace' AS 'com.cera.hyperionUtils.HypStringtoIntwReplace.strtointwrplc(String,String)' SPEC '@StringtoIntegerwReplace(String,String)' COMMENT 'Convert String to Integer with replacement option @StringtoIntegerwReplace("100-10","-") will give you 10010 as an Integer';

 

You can download the CDF from here.

 

For installation instructions please look 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.

8 thoughts on “Essbase String to Number CDF

  • amith

    Hi Celvin,

    StringInteger & StringDouble act as same like Attribute calculation functions rite !!! and in terms of replacement of "-" at any point of time are we defining against which dimension the replacement has to occur ? what if i have my scenario member as "Actual-V1" in the retrieval apart from 100-10 product.

    Thanks
    Amith

  • Faiza

    I am trying to link a data value to an alpha-numeric string. Example: 30.0 (data value of a member) to ABC_30.0 (member name). I’ve created the member @StringtoInteger via maxl. Also updated the udf.policy. Also placed jar file in the essbase java home directory. Am I missing any steps?
    In addition, can you pls tell me where I can find more info on creating this via Calc Manager? We’re on 11.1.2.4.0.79. I can’t find any additional info regarding @CalcMgrDoublefromString command that you’ve mentioned below. Thanks!

    • Celvin Kattookaran Post author

      You don’t need that anymore.
      You can use @Concatenate(“ABC_”,@CalMgrDoubletoString(member which has 30))
      This will generate it as ABC_30.

  • JohnR

    I have a calc that generates data to a date typed measure member. I’m performing a second calc which i need to pull the month and year from that date (presumably using DP_MONTH and DP_YEAR) for a cross dim reference for month and year dims, however these functions result in a numerical value instead of a string. What is the best way to convert these values to a string?