Sunday, 15 September 2013

Mysterious Custom Functions - Part 2

Today, in our quest to discover custom functions that came along with new Planning modules, those which start with @hsp, we will see some very useful string functions. Developers at Oracle have written 5 new functions for string manipulation and boolean control. And again, names are quite self-explanatory for the functionality itself. So my short descriptions will not surprise anyone. Here they are:
  • @HspNumToString (NumericInput): Converts the given number to text. Takes member and variable as well as constant integer but who wants to pass constant integer, right?
  • @HspDateToString (NumericDateInput): Converts planning date format to date string in dd/mm/yyyy format.
  • @HspConcat (StringArray): Concatenates all the strings in a given array. No restriction of two parameters (Yay!)
  • @HspStringCompare (srcstring, trgtstr): Compares two strings and returns true if they match and false if they don't.
  • @HspStringCompareArray (srcstring, trgtstr[]): Compares source string to an array of strings and returns, strangely, doesn't return boolean result rather integer 1, 2 and/or 3.

Frankly speaking, none of these functions are rocket science or very tough to code in Java and what they do one way or another can be done using native Essbase functions. However, these come from Oracle, which means they are convenient and they are designed to ease your life. In most cases, they greatly shorten the script. Now onto the examples and real life use-cases.

@HspNumToString: You can pass any number into @HspNumToString and it throws a string which can be used in other string functions such as @Substring, @Concatenate, @Match or better with one of the other @Hsp custom string functions which you'll find in today's blog. It accepts constant number, variable or member as parameter. Usage is simple:

@HspNumToString(10) Returns 10 as string

Var num1=10;
@HspNumToString(a) Returns 10 as string

"Sales"=100;
@HspNumToString("Sales") Returns 100 as string

@HspDateToString (NumericDateInput): Oracle has been criticized by Hyperion Planning community due to the fact that date data type in Essbase and Planning are not compatible. Essbase keeps the date the way Excel keeps it but rather more precisely. The numeric corresponding value for a date in Essbase is the number of seconds that elapsed since January 1 1970. Although there are ways to interchange one format to another, yet there was no out of box function to calculate differences between two dates or to roll a given date by a given number of date parts (i.e. days, weeks, months). We will touch upon date type custom @Hsp functions in the next entry in the blog.

@HspDateToString is a cross between string and date type functions. What it does is quite simply converting Planning's date format to a dd/mm/yyyy string date format (including forward slashes). You may ask why anyone needs this function. Curiosity grows considering the fact that you cannot return or store a string value in an Essbase calculation. At least this was what I had thought when I first noticed this function. However, couple this function with Essbase's own date functions and you will have unrestricted access to native Essbase date functions.

All Essbase date functions accepts a numeric value that is calculated as above and @TODATEEX function converts a given date in a specific string format to this number. Here is a sample:

var date1, date2, weeksduration;
date1=@TODATEEX("dd/mm/yyyy",@HspDateToString("Project Start Date"));
date1=@TODATEEX("dd/mm/yyyy",@HspDateToString("Project End Date"));
weeksduration=@DATEDIFF(date1,date2,DP_WEEK);

Above example, as you might have already noticed, calculates duration of a project in weeks. Believe me, it has never been any simpler... But it will be even simpler! Wait until next post...
@HspConcat (StringArray): If I shout like "tell me the biggest problem with @Concatenate function in Essbase", I imagine most of the audience would shout back to me like "the limitation of two string parameters!" Ok, that's a too long sentence to shout together in a group but you got my point I guess. @HspConcat allows you to merge n number of strings into a single string. It also accepts other string functions as input parameter. Below is how:

@HspConcat(@LIST("Project","-","2013","-","001")) Returns Project-2013-0001 as string.

With @Concatenate however, you would have ended up using multiple nested functions to get the same result.

If your purpose is to You can pass this into @Member to convert the string into a member.

@HspStringCompare (srcstring, trgtstr): This simple boolean function returns true if given two string matches and false if they don't. Here is how:

IF(@HspStringCompare("abcd","abcd"))
calculation;
ENDIF

@HspStringCompareArray (srcstring, trgtstr[]): I am not sure whether there is a bug with this function or it's how it's designed but this function is not a boolean function. It returns 1, 2 and possibly 3. The result is 1 if not all the string values in array are equal to first given string. The result is 2 if all strings in the array are equal to first given string. The result is 3, well, I am not sure when, but if the results were limited to two, Essbase would have accepted it as boolean. Below calculation works:

IF(@HspStringCompareArray("abcd",@NAME(@LIST("abcd","abcd","abcd")))==2)
calculation;
ENDIF

This concludes today's blog on the string functions in @hsp function series. In the next post, we will look into date type functions.

No comments:

Post a Comment