About Smart Forms

Applies to: EPM Cloud data source providers

Smart Forms are a type of data form, created in Smart View and based on ad hoc grids. They support additional functionality that is 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 ad hoc grids to the applicable providers 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.

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 Planning. 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 Planning 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 a 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 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 Forms, not the rest of the application.

To make this ad hoc grid with its grid labels and calculations available as a form in Planning, you can use the Save As Smart Form option. Thereafter, in Planning 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.

Guidelines to work with Smart Forms

Consider the following guidelines while working with Smart Forms.

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 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 Service Administrator or Power User role to save ad hoc grids as Smart Forms. As with regular forms, the service administrator must assign read/write access to Smart Forms for end users.
In Smart View, you design Smart Forms in worksheets, where 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 and HsSetValue.
Note: When you open an a Smart Form containing a Smart View function, click Refresh to update the function cells with their correct values.
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.
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 calculation engine of Excel, 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 a function is not supported, a message notifies 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. Also note the following:

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 Essbase.
Data cells created by the intersection of actual members with grid labels are referred to as calculated cells.