Author: Ian Britz
In this article, let's move on to our other two Models, Expense and Reporting, and create the Forms we need there. We'll work on the Operating Expense Form first. This will be used to track costs related to running our business, such as employee salaries and facility expenses.
Step 1. Navigate to the Expense Model and create a new Form. Name it Operating Expenses.
Step 2. We'll layout this Form in a similar format as the previous one. Let's put Account on the rows, Months on the columns, and Scenario and Fiscal Year on the filters.
Ensure you've checked the same boxes as the image below.
Step 3. In the Row definition, we don't need every single member of the Account Dimension, so let's select Total Operating Expenses and then click Children. Then let's also select the member by itself by clicking Single. This will be aggregated total of all of its children.
Tip: The order we select the members here is the same order they'll appear on the Form. Moving [Total Operating Expenses] above C([Total Operating Expenses]) would put the total at the top of the Form instead of the bottom.
Step 4. In the Column tab, let's select the All member and then choose Leaves. Then select Single to bring in the total value of all the columns.
Step 5. Now let's configure the Filters with the same defaults as the other Form.
In this Form, we're also going to configure one additional setting. Navigate to the Properties section and make sure the Row tab is selected. Click Add to add a new display property to the rows.
Then select [Member Name] in the Display Properties tab.
Then check the Indent Hierarchy box. This overrides the display property of the rows from a flat list to a hierarchical structure. The hierarchy will be based on the hierarchy of members in the Account Dimension.
The final result should look similar to the example below. Notice that the members above Total Operating Expenses are indented now. It's a small change in this Form, but we'll use the Indent Hierarchy option again in the Net Income Report to organize the full Account Dimension. Feel free to style the Form appropriately.
Step 1. Now let's add our final Form. The Net Income Report will be used to display all the calculated results in our application. Navigate to the Reporting Model and add a new Form.
Step 2. Configure the Layout in the same manner as the Operating Expense Form, but also check the Reverse option in the Account Layout. This moves the Parent Members to the bottom of their Children. We're doing this so our totals end up on the bottom of the Form, rather than the top.
Step 3. In the Row definition, we can include all members of the Account Dimension by selecting Income Statement and then clicking All.
Step 4. Configure the Columns in the same way as the previous Form.
Step 5. Set the default Filters and add an Indent Hierarchy Display Property in the same way as the Operating Expenses Form. Your Form should now look similar to the one below. Click on the Advanced Settings option.
Step 6. Here you can change the default cell color, show or hide grid-lines, and a number of other important configuration options. Feel free to style your report however you'd like.
Your final report should look similar to this.
Tip: To add empty static rows to a Form, right-click on the row numbers and select Insert Before or Insert After. To add empty dynamic rows, add headers into the row definition of the Form.
Now that we have all of our Forms ready, in the next article we can take a look at how we can combine all of them into one place, using a Dashboard.