The Excel formulas and functions that you add to ad hoc grids in Oracle Smart View for Office can now be saved as planning artifacts called “Smart Forms”.
Note: Smart Form functionality is available for ad hoc grids created from several of Oracle’s Cloud Service offerings, including Planning and Budgeting Cloud Service. Check with your service administrator about the availability of Smart Forms in your service.
Smart Forms give administrators and interactive users greater flexibility in meeting business calculation requirements that may be in the minority and are not built into the main application logic. Users with form creation capability can add Excel formulas or functions to an ad hoc planning grid, and then save the grid, including the formulas, functions and formatting to the Cloud Service as a Smart Form. Once saved, the form can be provisioned to end users.
The formulas and functions, or business calculations, can be created in rows and columns at the cell level, and are evaluated at runtime without adding metadata members. Smart Form business calculations leverage the native Excel calculation engine, without a round trip to the database.
Creating a Smart Form
To add formulas or formatting to excel, you must be using a Planning Ad Hoc connection. If you want to start from an existing planning form, you can execute the form, then choose the Analyze button to create an Ad Hoc copy. 
Let’s look at a simple example. For this exercise, we have a department who uses some account and periodic metrics that were not built into the main application outline. However, it was agreed to provide a Smart Form provisioned to this one department, so they could still retrieve these metrics in a controlled, governed manner.
I will start with the standard budget entry form:
And select the Analyze button to convert it into a Planning Ad Hoc sheet.
Business Calc #1:
The Computer Products department needs to track Total COGS without Direct Labor. We will insert a row after the standard Total Cost of Goods line and enter formula =sum(B8:B10), then drag it across the columns. We also give the row a label – “Total COGS less Labor.”
Business Calc #2:
This department also wants to see the 1st 8 months subtotal directly in the input form. So we also add a column between Aug and Sep, enter the formula to sum the first 8 periods, then copy that formula down all the rows. As before, we must also enter a label for the new column, so we call it “Period 8 YTD.”
We can also add some formatting, such as Bold headings, and a different cell color for the new row.
Now for the magic – On the Planning Ad Hoc ribbon, select “Save as Smart Form.”
Give the form a name, and select the box to include the formatting. You can also choose a different form folder location, and give the form a description.
Once saved, the form will show up in the Forms list in the Smart View Panel. You can tell it’s a Smart Form by the “fx” label on the form icon.
The Smart Form is also visible in the Forms and AdHoc Management window:
You can open the form in Edit mode, and while changes to the Layout are not allowed, you can make changes in the other tabs, such as Other Options and Business Rules. For example, here I updated the number of decimal places for Currency and Non-Currency Values in our Smart Form.
Executing the Smart Form
Once users are provisioned to the Smart Form, they can execute the form as part of their normal business process. Users see the customized form content, including any formulas that were used.
Note – when you render the form in Smart if you have the default cell styles turned on, any Smart Form business calculations will be shaded in green. (These cells are read-only).
If the Smart Form has custom styles saved (as our example did), you can turn those on and we’ll see our bold labels and blue COGS row:
Smart Forms can be executed from the web interface, as well as included in Task Lists and Navigation Flows in the web interface:
Final Thoughts
This is some very nice functionality that Oracle has provided in the Cloud space. However, from a Project implementation perspective, the endless customization opportunity of Smart Forms should never replace proper Business Requirements, Application Design vetting, and examination of the Processes and Governance that the application is supporting.
There are instances, however where Smart Forms can fill a need. As in our simple example, you may have a group of users who require something different, and it’s of great enough importance to ensure that difference is accommodated in a sustainable way. Or perhaps business needs have changed since an application was implemented, and Smart Forms can help fill a user experience gap until an enhancement project is available to properly implement the change into the core application.
Questions? Comments? Feel free to reach us at:
Robert Reis, rreis@ecapitaladvisors.com
Jon Harvey, jharvey@ecapitaladvisors.com
