This article will detail how to map dimensions for storage in a input template. A budget template is created in a similar manner to a report template. However, a input template also includes storage rules that define how the budget data is stored in the Reporting budget table. It is important that you know how to use Report Designer before you start creating a budget template.
The following shows how you can add storage rules to create a input template. The storage rules are defined at the cell level.
Below is a picture of the Budgeting tab in the Layout Editor and the different functions and elements in it. See the table below for descriptions of the various elements.
| 1 Store To | In this section, you will select the Factset and field you will store back to. Normally this will be the same field you dragged into the cell. NOTE: It is not only the selected field (in this case, the Budget Amount field) that is affected by the selection you make in the Store to section. You actually select the total transaction row that will be affected by the choice you make in the Mandatory fields and Optional fields sections described below. |
| 2 Mandatory fields | The Mandatory fields are also referred to as Key fields.These are the fields that make up the key for selecting which dimensions will be part of the selection for insert/update/delete a row in the Factset selected in the Store to section. This means that if it doesn’t already exist in a Factset row with the combination of the values of the fields in this section in the form instance, a new row will be inserted in the Factset. The value in the cell in the context of these fields get written to the field selected in the Store to section. If a row with the combination of these fields already exists, the value in the cell will overwrite the existing value in the row. Some fields are absolutely mandatory. They are defined as mandatory by the metadata model for that specific ERP integration. They are marked with a lock symbol to the right and cannot be removed from the Mandatory fields section. Other fields you choose to be part of the Mandatory field key can be deleted again. These are marked with a delete symbol (cross). Technically speaking, all fields in the Mandatory fields section will be part of the WHERE – clause when the row to insert, update, or delete will be searched for.The fields in the Mandatory fields section will get the assigned values specified in the right side of the mapping view. |
| 3 Optional fields | These fields will also get values from the form, but they are not part of the key to select transactions for insert/update/delete. These fields will not play a role when rows are searched for being candidates for an insert/update/delete operation in the factset table selected in the ‘Store to’ section.Technically speaking, these fields will not be part of the WHERE clause when the row to insert/update/delete will be searched for. They will simply be inserted or updated when a row is found for the combination of the fields in the Mandatory fields section. |
| 4 Data source reference options | Depending on the data type of the field and/or the placement in the Mandatory or Optional sections, the number and type of options may differ. Here is the complete list: Blank (empty) If you select the empty field, you will get a lookup option to help you select a value to assign to the field. This will be a constant value. This option means that you will have to enter a constant numeric value to be assigned to the field.‘This option means that you will have to enter a constant string value within the apostrophes to be assigned to the field.=This option allows you to specify that a value in a cell should be stored into the field it is associated with. When selected, the Excel cell-reference functionality for selecting a cell in the Excel workbook will appear. This functionality supports the dynamic cell reference technology used by Excel. *This option works the same way as the ={There will be a list of attributes in braces. This list will be dynamic and depends on which attributes are already in the Excel form. In the example in the picture above the {Period}, {Account},and {Account Name} attributes are listed. All of them can be used for referencing values you can store back on the dimension that is selected.@There will be a list of parameters in braces. This list will be dynamic and depends on which parameter are already in the budget form. The example in the picture above, the {@Period}, {@Scenario}, and {@Region} attributes are listed. All of them can be used for referencing values you can store back on the dimension that is selected. |
| 5 Settings | Conditional storage settings. For more information, see the Conditional storage settings section below. |
Conditional Storage Settings:
Store unchanged cells: This option will force data from this cell to always be stored. The application will not do a check whether the input cell has changed its value or not.
Ignoring rows or columns:
- Use ignore flag on row: If the first cell in a row contains a value, the entry is ignored; hence not stored. False, 0, or empty string count as no value.
- Use ignore flag on column: If the first cell in a column contains a value, the entry is ignored; hence not stored. False, 0, or empty string count as no value.
- Use ignore flag on row or column: If the first cell in either the row or column contains a value, the entry is ignored; hence not stored. False, 0, or empty string count as no value.
Delete transactions if cell value is 0: Means that cells including 0, NULL, or empty are not stored. This is useful when you have larger budgets to avoid creating unnecessary records.
Delete transactions for empty cells: Normally, values in a cell are deleted after pressing BACKSPACE or DEL or blanking out the value. If this option is selected, the value is used to update existing/insert a record in the storage dimension. This will also apply to transactions where there has been a value that has now been set to 0.
Validation of Content
There is also a certain validation process going on as you configure your Define storage dialog. When you have a valid set of mappings, the icon at the bottom left corner will be a green checkmark. If incomplete or the mapping was incorrectly done, you will get a red stop icon. In this case, the Region dimension field does not have a value or reference assigned:
Define Comparable Data
The Comparable data functionality allows the budget template creator to set up comparison data for the users performing the budgeting in the portal when using the Spreading and Line Item Detail tool.
There are two main options for defining the comparable data;
- Based on historical data: If you use this option, you will get data from your accounting system.
- Based on Excel range: If you use this option, you must define the area in the Excel sheet you want to show when using comparable data in the Spreading and Line Item Detail tool.
When using the Based on historical data option, you must do the following:
- Drag a Measure field, such as posted amount, into the top part of the window as shown in the example below.
- Enter a title for the comparable data in the Title
- Select or deselect the Include data from current fiscal year checkbox: If you select this option, the current fiscal year and the previous year will be included (depending on the selected period parameter you run the budget for). If left blank, you will get the two previous years.
NOTE: In the description above, only the period dimension is mentioned. Other dimensions that are used for selecting the Comparable data are inherited from the selections used in the template. If you want to override those selections, you can drag in dimensions from the module list and drop them in the section called Filters override. This is an advanced feature and should be used with care.