The Compleat Idiot's Guide to PBCS, No. 7 -- Smart Forms are Smart

A triumph, a warning, and a lament

Yes, it’s another one of my “All of Gaul is divided into three parts” posts.  Or maybe it’s my Descartesian nature.  Or maybe PBCS’ Smart Forms simply lend themselves to threes (four but I can’t count and it breaks the analogy).  Read on, Gentle Reader, and you may agree.

What makes Smart Forms so smart

When I first heard Shankar Viswanathan, Planning’s product manager, talk about Smart Forms at ODTUG’s Kscope15 (Or was it Kscope14?  It all blurs together after a while.) I thought he was off his rocker/absolutely brilliant because what he described was using Excel to build Planning forms and use Excel logic within Planning itself.  Howzat supposed to work?

At first I thought he meant we’d be able to create traditional forms in Smart View instead of Planning’s web interface the way the Planning admin extension to Smart View provides but that is wrong, wrong, wrong or at least partially, partially, partially right.  What Shankar actually meant is that we’d be able to create forms (so that bit I had right) and calculations in Excel and have those both persist in Planning.  I thought this would be really cool – we could (actually we can) create calculations using Excel’s functions instead of Planning’s form calculations and then have them be available for other users.  Even better, those Excel formulas would get translated into web-based forms.  Could it really work that way?  The answer is yes.

You can read through this blog (please, else why should I have spent a day of my all too short life writing this), but also have a read of the rather excellent PBCS documentation on it as well as I’ve undoubtedly missed something.

And it’s dead easy.  Let’s wall through the process, shall we?

Step 1

Smart Forms start out in – wait for it – Smart View.  That’s right, unlike every other kind of form they cannot be created in the web interface but instead must originate from a Planning ad-hoc query.  

Ad-hoc queries can either be created from a single cell retrieve or via the conversion of a proper form into an ad-hoc query.   In the screenshot below, I picked a Nice N’ Easy Income Statement form from PBCS’ Vision application.  

Step 2

I then converted the form into an ad hoc view by right clicking on the form name and choosing Ad hoc analysis.

I also could have started the ad hoc analysis by creating a new tab and then selecting Ad hoc analysis.  

The end result is the below:

I then did a mild bit of moving members about and created a four column report with a 2nd Half member in Column D with the super-sophisticated formula of SUM(B3:C3).  Silly, yes, but it prevents me from having to go into the outline and add that number as I only care about it once in a Blue Moon.

NB – To get this to work, I have to put a label on the column.  If that isn’t done the Smart Form won’t work.

Step 3

Once I have an ad hoc sheet with a formula, I simply save the analysis as a Smart Form using “Save As Smart Form”.

Step 4

Planning converts the ad hoc grid to a form, nicely centers the year across columns B through E, and then gives a visual cue that column D is calculated in Excel, not Planning itself by shading “2nd half” in orange and the calculated values  from D3 to D12 in green.

Big deal you may say but you’re missing three (see, Descartes does have a role in this post) points:

1. That calculation is now part of a form that you can pull onto any new sheet just as you wish.  The calculation is available to all with no need to go into ad hoc mode.
2. That calculation is now in Planning Simplified Interface’s web forms.
3. The need to use Planning grid formulae is gone, gone, gone.

Here’s what it looks like in the Simplified Interface:

There’s my 2nd half column in Planning itself.  That means that the boffins at Oracle have managed to convert Excel formulae into Planning’s web interface.  Pretty cool.

One big caveat – these forms are only available in the Simplified Interface.  Oh, you can see them in Workspace but click on them and Wait for Godot.

Coming back to the Simplified Interface, it seems logical that editing the form further should be possible.  

Alas and alack, that isn’t the case.  It’s a bit difficult to see but the form definition below is read-only.  I don’t think I’ve ever seen that in on-premises Planning.

Note also how column A is Q3 and Q4 and column B shows only the dimension name “Period”.  However PBCS translates Excel into the web interface, we’re not going to see it.

Coming back to Smart View, as this is a form I can change the Product dimension from T_TP (I think that’s Total Product.  Sorry, I was too lazy/sloppy to make sure that the Default Alias was used) and just like a form it changes.

And if I want to convert this form back into an ad hoc analysis, that’s available to me as well.  The formula that I created when I saved the original ad hoc view is retained.

Where exactly does this ability to convert Excel formulae into a Smart Form begin and end?  There a lots and lots and lots of formula functions in Excel – that’s one of its many strengths.  Unfortunately, not all formula functions are supported and if there’s a list anywhere of what works and what doesn’t I was unable to find it.

Arrrgh

Just what is supported?

An example of this is what I thought to be a rather clever (Clever is as clever does but I don’t actually think this is all that clever.) use of Excel to predict Net Income through Excel’s TREND function using the least squares method.  Think of it as a poor man’s Predictive Planning.

I will gloss over the amount of time it took me to actually figure out how to use TREND and show you instead the super awesomeness that I created:

   

That’s a bit hard to see but I wanted to show you how TREND does a halfway decent approach to fitting a curve.  It’s not a patch for Essbase’s own BSO @TREND function but it is a lightweight approach that might only make sense in a handful of forms or even just one – a great use case for Smart Forms.

Or so I thought because when I try to save this as a Smart Form I get this:

Bugger.  So that’s at least one formula that isn’t supported via Smart Form.  It sure would be nice to have that list of supported/unsupported.

No pretty pictures

One other thing doesn’t work:  applying formats.  According to the documentation, I should be able to apply formatting to both Planning (Essbase really) data as well as the Excel-derived cells.  Here’s what I get when I try:

Oddly enough I can use the Apply Formats function for the normal Planning cells but again that doesn’t work with the Excel-derived cells.  Bugger yet again.

Conclusion

Other than that, everything works and two minor bugs (one’s a function of missing documentation and the other really is a bug although I can’t find it in MOS) in what is pretty new functionality isn’t half bad.

I’ll take the compromises – I now never, ever, ever have to remember Planning grid formulas, I can do all sorts of neat-o labels, I can build it from an ad hoc view to a Smart Form back to an ad hoc form – that is pretty cool.

Smart Forms are Yet Another Feature That PBCS Has That On-Premises Doesn’t aka YAFTPHTONPD pronounced “yaphtopd”.  Oracle, please, please, please bring this to on-premises.  The rest of your customers are waiting with baited breath.  It’s that good of a feature.

Be seeing you.