Author: Ian Britz
In this article, we'll use the Models we created to generate Forms. A Form is used to show the data in a Model. Forms can be used as read-only reports, or as input forms to capture data. A Form in Kepion is composed of four sections: Rows, Columns, Filters, and Data. The Rows, Columns, and Filters are driven directly by the Dimensions, while the Data exists as the intersections between them.
Step 1. First, let's create our Assumptions Form. This Form will be used to track the quantity, price, and cost of our products. Navigate to the Forms node in the Assumption Model and click Add to create a new Form.
Name the Form Assumptions.
Step 2. Navigate to the newly created Form in the navigation pane. If you click Refresh in the middle of the page, you'll see that the section remains blank. Before anything is displayed, we need to define the structure of our Form.
Let's first click the edit icon and open the LAYOUT tab. Here we can define which Dimensions to apply to the Rows, Columns, and Filters. Let's put Product on the rows, Months on the columns, and Scenario, Metric, and Fiscal Year on the Filters.
Ensure you've checked the same boxes as the image below.
Note: We're using the Time Dimension twice here.
Tip: When at least one Hierarchy from each Dimension with (Input) is selected, we'll be able to input data on the Form.
Step 3. Navigate to the ROW tab. Here we can choose exactly which Dimension Members we want to display. In this particular case, we'd like to display all of our products, so we have a few options to choose from.
- Single - Brings in just the selected Member.
- All - Brings in all Members of the Hierarchy below the selected Member.
- Children - Brings in all the Members on the next level of the Hierarchy.
- Leaves - Brings in all the Members on the lowest level of the Hierarchy.
In the example below we've selected Leaves of the All member, which is represented as L([All]).
Step 4. Let's do the same thing in the COLUMN tab, for the Time Dimension.
Let's return to the MAIN tab and Refresh the Form. We should see all of our products on the rows and every month from the entire 8-year span on the columns. This is far too many members of the Time Dimension than we can easily work with, so let's use the Filters to scope down what we're looking at.
In the FILTER tab, we can select the default Filter to use.
For the Metric Filter, let's select Price and click OK.
Select Plan as the default Scenario, and FY20 for Fiscal Year.
Tip: For more information on the other Filter options here, check out this article.
Save the Form and return to the MAIN tab. We should now see that our Form only displays the months for the Fiscal Year of 2020. Also note that the cells have changed color to yellow. This indicates that these cells are inputable.
Note: Every Dimension Member we're using needs to be on the lowest level of the hierarchy in order to input data.
Now let's move to the General tab. Here we have a wide variety of common formatting tools. Feel free to style the Form however you see fit. For best performance, try setting the format by sheet, row, and column rather than formatting individual cells.
One last feature to be aware of in Forms are Cell Attributes. Cell Attributes allow you to apply formatting to cells that intersect particular Dimension Members. This makes it so the formatting dynamically follows these members as the Form changes. To add Cell Attributes, right-click on the Form and select Add Cell Attributes.
As this Form will be used to input the price, cost, and quantity of the products, let's create a Cell Attribute that formats the price and cost members differently than quantity. Let's give the Cell Attribute a name and then set the Scope to just Price and Cost.
In the Format tab, we can define a different format for when either of the scoped members are selected in the filter. Let's check Enable formatting and then increase the number of decimal places.
Now if we select Price or Cost in the Filter, the numbers are formatted with two decimal places.
However, if we select Quantity, the numbers do not display decimal places. Let's save this Form.
In the next article we'll work on creating the other two Forms, in the Expense and Reporting Models.