Smart Form Example Scenario
Smart Forms start with an ad hoc grid.
The ad hoc grid is created either by opening an Planning, Financial Consolidation and Close, or Tax Reporting form for ad hoc analysis or by starting ad hoc analysis at the cube level, as shown in Figure 11-1.
Figure 11-1 Form Opened for Ad Hoc Analysis
![Shows a Planning form that was opened for ad hoc analysis Shows a Planning form that was opened for ad hoc analysis](img/client_side_calc_1.jpg)
Add a row by typing "entity2" for the Entity dimension, and the label "accountX" for the Account dimension on row 13, cells A13 and B13, creating on the row two grid labels. Cells C13:F13 will be calculated cells. In order to be used as grid labels, the member names "entity2" and "accountX" should not exist in any dimension and there should not be any alias with the same name in any alias table. However, note that in the basic ad hoc above, entity2 is a member name, so entity2 will be treated as a member.
Figure 11-2 Grid Labels Added to cells A13 and B13
![Grid Labels Added to cells A13 and B13 Grid Labels Added to cells A13 and B13](img/client_side_calc_2.jpg)
Add business calculations, in the form of Excel formulas, for cells C13:F13, created by the intersection of real column dimension members with the grid label’s row 13. Figure 11-3 shows that the calculation was entered and executed in cell C13, and the same calculation is copied to cell D13.
Figure 11-3 Business Calculations Added to Cells C13 and D13, through to Cell F13
![Business Calculations Added to Cells C13 and D13, through to Cell F13, is =SUM(D10:D12); will be applied to cells C13 through F13. Business Calculations Added to Cells C13 and D13, through to Cell F13, is =SUM(D10:D12); will be applied to cells C13 through F13.](img/client_side_calc_3.jpg)
Figure 11-4 shows the business calculation in the Excel formula bar.
Figure 11-4 Business Calculation in the Excel Formula Bar
![Business Calculation in the Excel Formula Bar Business Calculation in the Excel Formula Bar](img/client_side_calc_4.jpg)
Upon refresh, entity2 in cell A13 will take on the properties of a member, accountX in cell B13 will take on the properties of a grid label, and cells C13 and D13 will take on the properties of business calculation cells.
Notes
-
You must enter grid labels in order to save an ad hoc grid with business calculations in the form of Excel formulas and functions.
-
If an empty space is entered instead of a grid label, the grid behavior is determined by generic ad hoc rules, such as replacing empty member cell with the same member from this dimension or displaying an error in case the grid becomes malformed.
-
You can enter grid labels at any dimension location for an axis: innermost, outermost, or any location in the middle.
-
You can enter business calculations only on rows and columns created by grid labels.
-
You may type a member name in a grid label row, but it must be placed within the correct dimension. If the member name is entered out of place, and is already in use by the applicable data provider in another dimension, a message is returned upon Refresh, prompting you to change the label name.
Now you can save the ad hoc grid as a Smart Form, as described in Creating Smart Forms. By doing this, our grid labels and business calculations are also saved to the applicable data provider.
The provider will identify "accountX" as a grid label on refresh by displaying it as an ad hoc "comment" row or column (blank cells background). The "entity2" label will take on the same properties as the other entity2 members in the grid, as this member name already exists in the provider database.
Other Examples
Using the Vision sample application, here is an example of a Smart Form, saved from an ad hoc grid, then later opened as a form in Oracle Smart View for Office.
In Figure 11-5, the business calculation was constructed to show the average gross quarterly profit and average gross total profit for two years, FY13 and FY14. It also shows cell styles applied to the grid label and business calculations cells.
Figure 11-5 Originally an Ad Hoc Grid, Saved as a Smart Form, then Opened as a Form in Smart View
![An ad hoc grid, saved as a Smart Form and later opened as a form, showing cell styles applies to the grid label and business calculations cells. An ad hoc grid, saved as a Smart Form and later opened as a form, showing cell styles applies to the grid label and business calculations cells.](img/smart_form_11.jpg)
Figure 11-6 shows a Smart Form opened as an ad hoc grid. The grid labels and business calculations were added in the middle of the grid, and cell styles were applied.
Figure 11-6 Smart Form Opened as an Ad Hoc Grid in Smart View
![An ad hoc grid with the grid labels and business calculations added in the middle of the grid. An ad hoc grid with the grid labels and business calculations added in the middle of the grid.](img/smart_form_01.jpg)
Figure 11-7 is the same ad hoc grid, that was saved as a Smart Form and opened as a form:
Figure 11-7 Smart Form Opened as a Form in Smart View
![The same ad hoc grid, with the grid labels and business calculations added in the middle of the grid, then opened as a form. The same ad hoc grid, with the grid labels and business calculations added in the middle of the grid, then opened as a form.](img/smart_form_02.jpg)
Figure 11-8 shows two grid labels and a business calculation cell formula, along with a member name typed into the grid label row. In the form, the hand-typed member name displays a member.
Figure 11-8 Smart Form with Member Name Typed on Grid Label Row
![Smart Form with Sales Calls as a member and Sales Calls also hand-typed in column A of the Grid Label Row Smart Form with Sales Calls as a member and Sales Calls also hand-typed in column A of the Grid Label Row](img/smart_form_03.jpg)
In Figure 11-9, a new column contains grid labels in cells F1 and F2. Column G contains a repeated, hand-typed member name in cell G1, and a grid label in cell G2 between the member and business calculation in cell G3.
Figure 11-9 Grid Label Between Members
![Grid Labels are in cells F1 and F2, a valid member name is in cell G1, and another grid label is in cell G2. Grid Labels are in cells F1 and F2, a valid member name is in cell G1, and another grid label is in cell G2.](img/smart_form_04.jpg)
Figure 11-10 shows how grid labels can surround a valid member.
Figure 11-10 Member Between and Below Grid Labels
![The No Product member is in cell B7, between grid labels in cell A7 and C7, and below a grid label in cell B6. The No Product member is in cell B7, between grid labels in cell A7 and C7, and below a grid label in cell B6.](img/smart_form_05.jpg)
Figure 11-11 shows how a member cannot be out of place between, or surrounded by, grid labels. A message notifies you that the member is out of place.
Figure 11-11 Out of Place Member in Grid Label Row
![A similar scenario to the previous figure, but the BaseData member is in cell B7, between grid labels in cell A7 and C7, and below a grid label in cell B6. A member out of place message is displayed. A similar scenario to the previous figure, but the BaseData member is in cell B7, between grid labels in cell A7 and C7, and below a grid label in cell B6. A member out of place message is displayed.](img/smart_form_06.jpg)