G’day All,
As promised – my final post on time\date factors\arbitrary rants about date systems.
This time, we delve under the covers to have a look at some of the new HSP functions – which massively simplify any work that you need to do with dates in Hyperion!
Quick Recap
H’okay. If you’ve read back through my previous posts about time in Planning and Essbase (specifically this one and that one) or alternatively if you’ve ever beaten your head against the desk repeatedly about the seemingly cumbersome way in which essbase and planning use dates then you’ll be aware of the following:
- Planning and essbase have different date systems
- What works in Planning doesn’t work in Essbase
- What works in essbase doesn’t work…basically full stop
- It’s really difficult to efficiently use dates
- The ‘simplest’ method is often to convert dates into effective strings and do ‘math‘
But, with the new HSP functions – that’s pretty much all changed.
Surprise Documentation!
Since these functions have been added there have been screams (well…modest complaints) about the staggering lack of documentation. Fortunately some amazing members in the EPM community have taken it upon themselves to change this, and through a combination of blog posts over the years have added many notes:
Alp Burak Beder – HSP Custom Functions 1
Alp Burak Beder – HSP Custom Functions 2
Cameron Lackpour – Calc Manager BSO and PLanning
Celvin Kattookaran – Calc Manager Execute MAXL and MDX
Robert Gideon – Calc Manager HSP Update 11.1.2.3
Robert Gideon – 11.1.2.4 New Functions
And now it seems Oracle has come to the party – adding the following to their Calc Manager \ Business rule Documentation.
Oracle Working with Custom Defined Functions
So let’s have a look then!
Okay, so perhaps most importantly with these functions – they are designed to work directly on Planning input dates. As such, they are expecting an Essbase date in the format (“YYYYMMDD”) and a Planning Date in the selected date format in the Planning apps.
The nice thing about this (for you crazy Americans) is that you can use discrete date options per user, but still use the same underlying functions. See date entry in mm-dd-yyyy in Planning, while the Smartview retrieve is showing dd-mm-yyyy.
Or. You know. Your could standardise.
The Juicy Bit
Where was I….
Below is a listing of almost every date function in 11.1.2.4 – I’ve put in the function, the usage and the outputs (in both essbase and planning).
Some interesting functions in that list: CalcMgrRollDate – This is a cool function! Only for Month and Day
@CalcMgrRollDate:This is very useful. Rolling forward the ‘last day’ in the month without needing to deal with differing month lengths. It’s also setup for leap years, so massively reduces coding.
[Adds or subtracts (up or down) a single unit of time on the given date field without changing larger fields.
For example,
@CalcMgrRollDate(19960131,"month",@_true) will result in the date of 19960229.
@CalcMgrRollDate(19960131,"day",@_true) will result in the date of 19960101.]
Another useful function is @CalcMgrGetCurrentDate. At first I couldn’t get it working, but after a quick google landed me on Robert Gideon’s blog and I was on my way. Could be invaluable if you wanted to script a data export to pass a date to the filename.
I did have issues with @CalcMgrGetDatePart and @CalcMgrAddDatePart returning slightly odd values or no values hence their exclusion above. However the main component of their functions have been replaced by more discrete functions, so they have limited utility anyways.
Anyways – I’ll hopefully be back soon with another blog post on the REST of the HSP functions. Any questions \ comments, hit me up in the comments.
Cheers
Pete