Data source types: Oracle Planning and Budgeting Cloud, Oracle Enterprise Planning and Budgeting Cloud, Oracle Financial Consolidation and Close Cloud, Oracle Tax Reporting 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 source application. The business calculations that you create and save in the Smart Form can then be executed in both Smart View and the provider web interface; for example, in Oracle Planning and Budgeting Cloud. In Smart View, the formulas are evaluated by Excel; in the web interface, the formulas are evaluated by the provider.
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 data form, created in Smart View and based on ad hoc grids, that have functionality not supported by regular 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 the applicable provider, such as Oracle Planning and Budgeting Cloud, as Smart Forms. The business calculations and grid labels, along with any empty rows and columns, are saved 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 applicable web interface or in Smart View.
Smart Form design, as with form design in the application web interface, is not available for end users. You must have the Admin or Interactive User role to save ad hoc grids as Smart Forms.
As with regular forms, the 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 provider web interface, you would need to create separate rows and columns to achieve this.
When connected using a shared connection, the following Smart View functions are supported in Smart Forms: HsGetValue, HsSetValue, and HsActive.
When connected using a private connection, Smart View functions are not supported.
In the provider 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, Oracle Financial Consolidation and Close Cloud, and Oracle Tax Reporting 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, end users can execute the business calculations from the Smart View client, without adding members to the application metadata.
Business calculations are executed in Smart View utilizing Excel's calculation engine, and do not require queries to the provider. These runtime business calculations are supported in both Smart View and the data provider. 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 the data source application, 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.