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 salary and facility expenses.
Operating Expense Form
Step 1. Navigate to the Expense Model this time, and create a new Form. Make sure not to add this Form to the Assumption Model, as it doesn't contain the Dimensions we need. Title it Operating Expense.
Step 2. We'll layout this Form in a similar format as the other ones. Let's put Account on the Row, Months on the Column, and Scenario and Fiscal Year on the Filters.
Step 3. In the Row definition, we don't need every single member of the Account Dimension, so let's select just the Children of the Total Operating Expenses member, and then the Total Operating Expenses member itself, which will be the aggregated total of all its children.
Step 4. On the Column, let's select all the members, as we did before, as well as the All member to reflect the totals.
Step 5. Now let's configure the Filters with the same defaults as the other Forms.
Step 6. In this Form, we're also going to configure one additional setting. Navigate to the PROPERTIES tab and make sure the ROW pane is selected. Click Add to add a new display property to the Row.
Step 7. Then select [Member Name] in the Display Properties tab.
Step 8. Then check the Indent Hierarchy box. This changes the display property of the Row from a flat to hierarchical structure. The hierarchy will be based on the hierarchy of members in the Account Dimension.
Step 9. And the 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 Form to organize the full Account Dimension. Feel free to style the Form further, however you'd like.
Net Income Form
Step 1. Now let's add our final Form. Navigate to the Reporting Model, and add a new Form. Title it Net Income.
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.
Tip: Make sure you select All of the All Member A([All]), and not just All [All]. The former will show all members of the Hierarchy, while the latter will show only the aggregated total.
Step 4. Configure the Column in the same way as all the other Forms.
Step 5. Set the default Filters and add an Indent Hierarchy Display Property in the same way as the previous Form. Your Form should now look similar to the one below. As we're using the entire Account Dimension the Form is pretty long, so let's hide any rows we're not using.
Step 6. Click on the drop-down icon next to the Settings option, and select Hide Empty Rows.
Step 7. As all the rows are currently empty, the Form should now look blank. That's okay for now. Let's save 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.