Data source types: Oracle Planning and Budgeting Cloud
You create and manage Smart Forms in Oracle Smart View for Office. Starting with an ad hoc analysis grid, you can customize the grid by adding business calculations to it, in the form of Excel functions and formulas. These calculations don't affect metadata in the rest of the Oracle Planning and Budgeting Cloud application. The business calculations that you create and save in the Smart Form can then be executed in both Smart View and the Oracle Planning and Budgeting Cloud web interface. In Smart View, the formulas are evaluated by Excel; in the web interface, the formulas are evaluated by Oracle Planning and Budgeting Cloud.
For example, say you're analyzing Oracle Planning and Budgeting Cloud data in Smart View. You want to calculate the average profit margin for four products. You can add a row to the grid, with the grid label of "Average Profit Margin." In the new row, add an Excel function for average profit margin, selecting the profit margin data cells for each of the four products. If the profit margin for each product appears in column D of the grid, then your function may be =AVERAGE(D5:D8). In Smart Forms, the Excel formulas and functions you add are referred to as business calculations. After you add the business calculation, the average profit margin is instantly displayed in the new row, but the new row will be saved only in the Smart Form, not the rest of the application.
To make this ad hoc grid with its grid labels and calculations available as a form in Oracle Planning and Budgeting Cloud, you select the Save As Smart Form option in the Planning ribbon in Smart View. Thereafter, in Oracle Planning and Budgeting Cloud or Smart View, you can open and use this Smart Form as a form, including any rows, columns, and business calculations you added. In Smart View, you can perform ad hoc against this Smart Form. You can even create a sandbox from a Smart Form.
The sections that follow provide descriptions and guidelines for working with Smart Forms.
Smart Forms
Smart Forms are a type of Oracle Planning and Budgeting Cloud form, created in Smart View and based on ad hoc grids, that have functionality not supported by regular Oracle Planning and Budgeting Cloud data forms. Smart Forms support grid labels, along with business calculations in the form of Excel formulas and functions. In Smart View, you can save these ad hoc grids to Oracle Planning and Budgeting Cloud as Smart Forms. The business calculations and grid labels, along with any empty rows and columns, are saved in Oracle Planning and Budgeting Cloud as a part the Smart Form definition. Additionally:
Beside calculated cells and empty rows and columns functionality, Smart Forms support extended Excel formatting features including cell merging.
Smart Forms can be used by end users in the same way as regular forms in the Oracle Planning and Budgeting Cloud web interface or in Smart View.
Smart Form design, as with form design in the Oracle Planning and Budgeting Cloud web interface, is not available for Oracle Planning and Budgeting Cloud end users. You must have the Admin or Interactive User role to save ad hoc grids as Smart Forms.
As with regular forms, the Oracle Planning and Budgeting Cloud administrator must assign read/write access to Smart Forms for end users.
In Smart View, you design Smart Forms in Excel worksheets, meaning you can clearly see the Smart Form layout, even an asymmetric layout. In the Oracle Planning and Budgeting Cloud web interface, you would need to create separate rows and columns to achieve this.
Smart View functions, such as HsGetVal or HsActive, are not supported in Smart Forms.
In the Oracle Planning and Budgeting Cloud web interface, while in design mode:
You can assign business rules to Smart Forms.
You cannot modify the Smart Form grid layout, Excel formatting, or business calculations.
Smart Form functionality is available for ad hoc grids created with Oracle Planning and Budgeting Cloud. Check with your service administrator about the availability of Smart Forms in your service.
Business Calculations
In Smart Forms, the Excel functions and formulas that you add to an ad hoc grid are referred to as business calculations. When a grid is saved as a Smart Form, Oracle Planning and Budgeting Cloud users can execute the business calculations from the Smart View client, without adding members to Oracle Planning and Budgeting Cloud metadata.
Business calculations are executed in Smart View utilizing Excel's calculation engine, and do not require queries to Oracle Planning and Budgeting Cloud . These runtime business calculations are supported in both Smart View and Oracle Planning and Budgeting Cloud. Also note the following:
Many Excel functions are supported in Smart Forms. If an Excel function is not supported, a message will notify you when you attempt to save the Smart Form.
You can apply Excel formatting to the business calculation cells so that the figures display according to your preferences; for example, with dollar signs or decimal points.
In the grid label row or column, you cannot enter only constants for the business calculation cells; for example, 1000 or 0.10 are not supported. If you want to use a constant in a calculated cell, be sure to prefix it with an equal sign (=); for example, =1000 or =0.10.
Grid Labels
Grid labels are used to provide placeholders in a grid for entering business calculations for corresponding data cell intersections. They are required in order to save an ad hoc grid with business calculations. Additionally:
You enter grid labels manually in Excel in place of actual members in an ad hoc grid.
Grid label names cannot match any actual member name in Oracle Planning and Budgeting Cloud or any member alias from any alias table. In case of a match, the actual member name or alias name will take precedence over the grid label name.
Grid labels cannot consist of only blank spaces.
Grid labels do not have any dimension properties.
Grid labels are not visible in the Planning Dimension Editor and they are not sent to Oracle Essbase.
Data cells created by the intersection of actual members with grid labels are referred to as calculated cells.