In this session, we’ll learn how to create input Forms. First, we’ll put together the general layout and structure of our Form. Then we’ll examine the properties that determine whether a Form is inputable or not. And lastly, we’ll see how to create dynamic formatting through the Cell Attributes feature.
For more information on Cell Attributes, go to Why Is My Cell Not Inputable / Annotatable?
In this session, we’ll learn how to create input Forms. We’ll use the Base Assumptions Form as our primary example.
First, we’ll put together the general layout and structure of our Form. Then we’ll examine the properties that determine whether a Form is inputable or not. And lastly, we’ll see how to create dynamic formatting through the powerful Cell Attributes feature.
Let’s navigate to the Assumption Model and create a new Form.
Then let’s open the LAYOUT tab and assign Dimensions to the axes. Note that for input Forms, we have to make sure that we select INPUT nodes for all Dimensions.
Again, we should use the mockup Form from our Architecture Document, to guide the process. Let’s put SKUs on the Rows, Months on the Columns, and then Account, Entity, Scenario, and Fiscal Year on the Filters.
You may notice that although we defined attributes in the both the Entity and Product Dimensions, they don’t appear here. For performance considerations, attribute hierarchies aren’t enabled by default, except for the Time Dimension. However, we can configure this by going to the Attributes node and checking the Hierarchy box.
As this affects the structure of the Model, we’ll need to deploy the application.
Now we can choose the Brand attribute as a Filter.
In the Row tab, select the All member and choose Leaves. This will bring in every SKU.
In the Column tab, we can do the same thing.
Then we should set our Default Filter selections. For the Account Filter, we only need to access the three accounts under the Base Assumptions header. The rest of the Accounts in this list don’t make sense in the context of this Form. To reduce confusion for our end-users, we can use the Scope option to hide the rest of the accounts. Select Custom Set and then select the three members we need.
Now let’s set the rest of our defaults.
When we return to the main tab, the cells on the Form should be yellow, to show that they’re inputable.
If we were expecting our cells to be inputable but they’re not, we can investigate this by right-clicking on a cell and selecting Cell Details. This window provides detailed information about the cell.
A cell is inputable only when all the Dimension Members that intersect the cell are inputable leaf members. We have these states to help us confirm that.
Is Lowest checks if the Members are on the lowest level of the Hierarchy. If this was false, we could use the Slice information below to investigate further. The value is the MemberId of the Dimension. It has to be non-zero. If its value is 0, it means that we’re not on the lowest level.
Is Writeable checks if the current user has permission to write to the model and dimension that the cell is associated with.
Is Input checks if all the Members that define the cell have their Input attribute set to 1.
Is Annotate checks if those Members have their Annotation attribute set to 1.
Is Locked is set in the Workflow settings, which we’ll cover later. This value has to be False for input regions.
For a cell to be inputable these values have to be True, True, True, and False. When ‘Is Annotate’ is true, the cell is a text input field. Otherwise it’s a numeric or dropdown input field
Now let’s style our Form so that it’s consistent with the P&L Report. It looks good now, however we actually have a small issue with number formatting that might not be apparent right away.
To explain, let’s switch our Filter to Price. This Form will be used to enter the Price of the products we’re selling. If we enter a number with decimals, it will automatically be rounded up. We can change this by selecting the whole Form and increasing the number of decimal places.
But now if we switch the Account Filter back to Volume, the formatting doesn’t make sense. The extra decimal places are unnecessary, and might be confusing.
To solve this issue, we can add dynamic formatting to our Forms using cell attributes.
Let’s right-click on the Form and choose Add Cell Attributes. Then we need to select which Dimensions the formatting should apply to. Let’s remove everything except Volume.
Then in the Formatting tab we can check Enable Formatting and set a different format only when Volume is selected. Now the formatting is appropriate, regardless of which filter is selected.
Let’s save this Form. Following the same process and using the Architecture Document as our guide, we should have no problem constructing the Deflator Percent and Trade Spend Forms in their respective Models.
In this session, we configured the structure and layout of our Input Forms, then we looked into the various properties that determine whether a Form is inputable, and lastly, we examined how to create dynamic formatting with Cell Attributes.
That concludes Part II of this series. In Part III we’ll look at creating Dashboards to interact with our Forms, and managing the data in the application.