Sub Variables, and a Glorious Dataform Hack

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

Significantly More 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!

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.

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.

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.

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.

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

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