Create budget template in Report Designer
See Report Designer for an overview of the user interface and basic functionality in Report Designer.
A budget template is created in a similar manner as a report. However, a budget template also includes storage rules that define how the budget data are stored in the OneStop Reporting budget table. It is important that you know how to use the Report Designer before you start creating a budget template.
In this section, we will discuss how you can add measure and storage rules to create a budget template. The storage rules are defined at the cell level.
In the following example, we have already defined the data that will be displayed in the budget template, such as accounts and periods.
Add measure or calculated storage cell to the template
Drag the desired measure from the OSR budgeting factset, such as Budget Amount, into the form. The Budget Amount will be the input cell where the end user will enter their Budget numbers in the finished executed and prepared Budget template. Locate the Budget Amount in the Module bar and drag it into cell F12, as shown in the figure below. You define the storage rule on this level.
If you have a calculated value that you want to store to the budget table;
- Click in the cell where the calculation is.
- On the Budgeting tab, click Create storage.
Budgeting tab – Mapping Dimensions for storage
After the Budget Amount measure is dragged into the cell, or you have manually created a storage cell, you can see that a new tab appears in the Layout Editor. This is the Budgeting tab and it will appear when a cell contains a value, it is allowed to store back to the database.
The Budgeting tab contains information about which dimensions a transaction will be stored back to.
There are three main sections in this tab you need to pay attention to. They are:
- Store To section. In this section, you will enter the Factset and measure you will store back to. Normally this will be the same measure you dragged into the cell.
- Mandatory fields. These are the fields that make up the key for selecting which dimensions that will be part of the selection for insert/update/delete a value.
- Optional fields. These are the fields that will get a value from the form, but they are not part of the key to select transactions for insert/update/delete.
The figure below illustrates the definition of storage of the value in a specific cell in the budget template:
The data input cell will automatically get a set of default fields that will define the value in the database. The fields are inherited from the context of the cell.
In this example, the dimensions, OSR Scenario, Account, Period, and Region define the context of this particular cell. The stored value will get the current expanded values of these dimensions when the end users work with the budget template in the portal.
The Budgeting store-back allows storage of both user-input cells and calculated cells. This is a strong feature as you may want to make allocations and store values to other accounts based on input values entered by the user.
Budgeting tab – Comparable data
The Budgeting tab has two sub dialogs. They are the Define storage and the Comparable Data dialogs. You can toggle between them by clicking the two arrows in the right bottom part of the Budgeting tab. The first picture shows the label when in the Define storage mode.
When the Budgeting tab is in the Comparable data mode, it will look like this:
See the following sections for detailed descriptions of the two different modes.
Define storage
Below is a picture of the Budgeting tab and the different functions and elements in it. See table below for descriptions of the various elements.
V | Name | Description |
---|---|---|
1.0 | 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 choice 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.0 | 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.0 | 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.0 | 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. <None> This option means that you will have to enter a constant numeric value to be assigned to the field. ‘<None>’ This option means that you will have to enter a constant string value within the apostrophes to be assigned to the field. =<cell reference> 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. *<cell reference> This option works the same way as the =<cell reference> option when it comes to how a cell reference value is selected and used. The difference is that this option will multiply the value of the cell to the value of the field. It can only be used with a field of a numeric data type. {<attribute>} 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. @<parameter> There will be a list of parameters in braces. This list will be dynamic and depends on which parameters 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.0 | 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 (see Line item details, Spread function and Adjust function).
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.