Planning

Get Involved. Join the Conversation.

Topic

    Greg Olma
    Using Smart View Save Smart Form Functionality
    Topic posted July 11, 2018 by Greg OlmaRed Ribbon: 250+ Points, tagged Financial Planning, PBCS, Planning, Reports, SmartView 
    548 Views, 2 Comments
    Title:
    Using Smart View Save Smart Form Functionality
    Summary:
    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.
    Content:

    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.

    smart%20form_01.png

    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.

    smart%20form_02.png

    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.

    smart%20form_03.png

    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.

    smart%20form_04.png

    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:

    smart%20form_05.png

    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:

    smart%20form_06.png

    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.

    smart%20form_07.png

    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:

    smart%20form_08.png

    Choose to save as a Smart Form, here you can overwrite the prior version by choosing the same name / location.

    smart%20form_09.png

    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:

    smart%20form_10.png

    The results: the form now contains additional formulas, and the formatting I chose “stuck”.

    smart%20form_11.png

    Let’s look at this form in the web interface:

    smart%20form_12.png

    In the web interface, or in Excel, edits to the yellow cells are dynamically calculated:

    smart%20form_13.png

    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:

    smart%20form_14.png

    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.

    Comment

     

    • Mark Rinaldi

      Great post!  A few helpful documents:

      Smart Form Excel Functions Supported there are about 132 Excel Functions supported across most/all categories (i.e. Financial, Logical, Information, Statistical, Text, Date & Time, and Math & Trigonometry)

      Smart Form Chapter in the Smart View 11.1.2.5.800 User's Guide

      Also, "COUNT" and "COUNTA" are supported but not "COUNTIF"

    • Tejas Patel

      Thanks Greg for sharing this information.

      We use smart forms for customers that has lot of drivers since it is easier to use excel formulae instead of creating multiple dynamic members in the outline; for eg; i had requirement to calculate labor cost for the mines which utilizes various drivers assumptions(straight time, double time, overtime hours and dollars for weekdays and weekends)formulaes. These are setup in the smart form. It is easier to update the formulae and save the smart form.

      Thanks,

      Tejas