Welcome reader, this is the second in a series of articles exploring rarely used (or in this case newer) functionality in Smart View. Currently we will be exploring Smart Forms. Oracle added in “Save As Smart Form” functionality to Smart View for PBCS in 2016, but I have not seen it used in practice in any implementations yet, so I feel that it is worth exploring.
So, what are Smart Forms? Smart Forms are essentially ad hoc grids saved up to the cloud with a twist: they can carry *some* native excel calculations. This can be extremely useful for users that do not want to learn the frankly clunky form formula syntax, but would like to create simple views into their data that can be shared with other users and are “repeatable”.
Smart Forms are easy to create. First, create and ad-hoc retrieval in Smart View. This can be a ground-up retrieval, or a form that has been converted to ad-hoc analysis.
Here we have a simple retrieve with level 0 data which can be edited and a dynamically calculated parent.
Choose “Save As Smart Form” from the Planning Ad Hoc ribbon.
Name the form, and choose the directory for where it is saved. Note: checking Submit Formatting will come into play in the next steps… might as well get into the habit of doing it.
Now that the Smart Form has been saved, you can see it both in Smart View, and the web Workspace interface. Note the unique icon for the “Projects_View” form with the “fx” notation.
Now you have a form that was user-generated, and saved in a location that can now be accessed by the community of all users. It will provide repeatable and consistent views to the entire organization. The POV can be adjusted as needed when run, in the web interface or Smart View.
Up to this point we have done little more than the “Save Ad Hoc Grid” functionality provides. Where Smart View forms are special is the ability to carry basic excel calculations from ad hoc sheets into forms.
Consider the ad hoc grid shown below:
The “Buildings” and “People” rows and associated calculations were added in Excel. Saving this ad hoc grid as a Smart Form, and then opening that Smart Form has the following results:
Note the “Buildings” and “People” lines have become shaded. The green cells are now protected. You can see the excel formulas if they cells are selected, but you cannot edit them.
So, this is now a form that cannot be edited… how can we modify to add more information? Simply right-click on the form in the Smart View panel, and choose “Ad hoc analysis”. This will bring up your what is essentially your initial excel retrieve with formulas intact.
Now, you can add additional formulas. In this case, we are going to add the ever-popular “13 month variance analysis” and associated %. Here it is as an ad hoc retrieve:
Choose to save as a Smart Form, here you can overwrite the prior version by choosing the same name / location.
Note the formatting shown above. I am showing 2 decimal places for all columns, and %’s for column E. I have selected “Submit Formatting” with the form. As I am overwriting the existing Smart From, Smart View wisely gives me a warning:
The results: the form now contains additional formulas, and the formatting I chose “stuck”.
Let’s look at this form in the web interface:
In the web interface, or in Excel, edits to the yellow cells are dynamically calculated:
So, what are the limits of the excel formula to form formula conversion? Well, simple arithmetic appears to work well, but just scratching the surface of excel functions reveals limitations pretty quickly. In the example below, I am attempting to apply a count function and receive the following error:
That’s about it for Smart View Forms, I recommend experimenting with this functionality and see what works for you. It is a fairly quick way to further merge ad hoc capabilities with a cloud based analytical application. More entries in this series will follow, please check back soon.
Blog post by James Renger of Key Performance Ideas.
Posted on Tue, July 10, 2018
by Anne Stein