Tag: Planning

Sub Variables, and a Glorious Dataform Hack

4 Comment(s)

G’day All,

I’ve been a bit lax with regard to posts – apologies – but Cameron harassed me to post this up after he’d not heard of it. To be honest, this is one of those ‘been in my back-pocket for so long I just assumed everyone knew’ kinda features (is there an acronym for that?)

Basically I was asked if anyone knew a way of showing only certain periods on a dataform…without having to update the form every month. This is one of those things that feels like it should be possible (nay easy) but…well…isn’t. Hence this hack.

Where did this all come about…

One of the common problems with Planning dataforms is a fairly hefty limitation in how they can be structured. This has general led to a number of obscure and imaginative hacks to show the data that you want. Significant examples of limitations include:

  • No UDAs available to dynamically filter member selections
  • No range substitution variables
  • Limited member selection functionality

Oh for the want of a cloud…

For those of you already heavily using PBCS you’ll note that a number of these functions have been significantly improved. Including:

Member Exclusions
01-exclusions
Significantly More Member Selections
02-new-member-selections

This gives you the flexibility to build dataforms however you like. In our case, it allows you to selectively show Right or Left members of a member.

So setting a subvar to &Period_Plan = Nov and defining a column header as IRSIBLINGS(&Period_Plan) will show just November and December!
irsiblings

Terrestrial Based Development?

On-premises you’re more limited. The functionality to allow for member selections is basically only Level-0 references and children – difficult if you wish to selectively show certain periods based upon a sub variable.
20161113_dataform2

So what are our options? Interestingly what you would ‘expect’ to work doesn’t. Defining “Nov,Dec” as a sub variable will not allow it to be used – see both ActPeriods and Period_Act_Range for examples of what I tried (although they are obviously for the Actual periods!) Using these, saving the dataform and attempting to open it will just throw a default ‘dataform is invalid’ error message.

20161113_subvars

What’s this about Merging SubVars then?

One of the interesting default behaviours of Planning dataforms is to ‘merge’ the same member into a single column. If you have a column or a row selection with the same member listed sequentially (ie: Actual, Actual, Budget, Forecast) it will merge the Actual columns together rather than duplicating the column.

Therefore we need to work out a way that we can use and abuse that behaviour to suppress the columns that we don’t want to show. In order to do this, we create 12 sub variables.
subvar_definition

The variables are then updated with the ‘same’ value for the first month to be shown. In this example I’m showing just Nov and Dec, but with 12 variables it could obviously be extended to a full year. Obviously in a case like this you’re going to want to automate the setting of sub variables… but I’ll leave that as an exercise for the reader.

Building the Dataform

Then our dataform becomes easy – the period dimension is defined as all 12 variables.
20161113_dataform

The closed variables then merge together on the dataform and we are left with only the plan periods. Simple!

20161113_dataform_updatd

Obviously we’re all hoping for the next round of 11.1.2.4 patches to bring some of this PBCS functionality back to on-premise (my kingdom for on-premise smartpush!) – however for the moment it’s a very effective, if a touch inglorious, hack!

Cheers
Pete

HSP Date Functions in 11.1.2.4

3 Comment(s)

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.
DateSettingsinPlanning

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.
Crazy_American_Dates

Or. You know. Your could standardise.
Standards

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).

All Date HSP Functions

Some interesting functions in that list: CalcMgrRollDate – This is a cool function! Only for Month and Day

@CalcMgrRollDate:

[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.]
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.

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