A data sheet is always part of a control program, and it can be used by one or more controls within the control program.
In the first step, the relevant control program must be opened. A new data sheet can now be created using the button “Add data sheet”. The data sheet is created using an editor with three components: structure editor, response editor and data sheet editor. The following diagram, fig.1, shows which contribution the respective editor makes to the creation of the data sheet:
In the structure editor (fig. 2), the data sheet is named. In addition, the characteristics of the first columns of the data sheet are specified, pre-filled with content.
The number of specified columns can be freely selected. In most cases, two columns are sufficient to define the structure of a data sheet.
The first step is to define the respective column type. Two alternative types can be selected here, using drag-and-drop:Text
A column, the content of which consists of text, such as names of employees, areas or descriptions for certain key figures.Number
A column, the content of which is a number, e.g., planned, expected or realized values, account numbers.
The column is named by clicking on the selected column type.
In addition, the following attributes can be set:Required
Means that the respective cell must be filled in. If the user does not enter data in the cell, the control cannot be carried out. In other words: a mandatory field in the data table.Unique key
Means that the content of the cell must not appear twice in the table. Examples are a customer number, account number or other reference numbers and names. They are unique and are not assigned twice.Grouping
A grouping is used to determine the affiliation of data. Different departments, responsible persons, areas or even individual countries are conceivable as possible groupings. Groupings make it possible to assign parts of a data sheet to a control that are only relevant for a specific group.Total
With the help of this attribute, a sum can be calculated from the values of a number column. If a data sheet is created with sales from individual quarters, the function can be used to calculate the sum of the total sales.
Finally, the structure editor offers the option of deactivating live updates. Normally, a data sheet that has already been sent will change in real-time if changes have been made (see chapter 3). If this function is not desired, Impero offers the option to deactivate this.
To illustrate how the structure editor works, a data sheet for collecting and commenting on the key financial figures of three subsidiaries is shown below. The column type "Text " is required twice for this. The first column is named "Key figure". This is a required and unique key, as each key figure may only be present once (e.g., "Sales Italy" is not equal to "Sales Spain"). The subsidiaries are also required, and they are grouped by country and created in the second column.
Figure 2: Structure editor
In the response editor (fig. 3), the response columns for the data sheet are defined. This results in the response fields being filled in by the person executing the control. The same data and column types are available when creating a control: text, comment field, checklist, option fields, upload field. Columns can be set as mandatory fields (“required”). There is also the option of calculating the sum of individual columns in the response editor.
In the example already outlined for determining key figures, the column type Number is selected here, and a comment field is created in which the user can optionally write a comment for the reviewer.
Figure 3: Response editor
In the last step, the first columns of the data sheet (structure specifications) are filled with specified content. In the example of the key figure analysis, the key figures to be determined are defined and assigned to the respective subsidiaries. Note: If a column has been given the attribute “unique key”, a designation can only be used once.
Figure 4: Data sheet with pre-filled structure specifications
Fig. 4 shows the completed creation of our data sheet example.
Insert data from Excel
In addition to entering the data manually, it is also possible to import existing data from an Excel table. To do this, select the corresponding cells in Excel, copy them with the key combination Ctrl + C and paste them in the data sheet editor with Ctrl + V.
Assign controls to datasheet
After creating the data sheet, the second and last step are as follows: The data sheet must be sent within a control. For this purpose, a new control is created in the control program to which the data sheet was assigned. Alternatively, the data sheet can be added to an existing control in the control program.
In the form editor (fig. 5), each control that is assigned to the control program is now available with a new “data sheet” module. The module must first be assigned to the form in the usual way. By clicking on the added module, a drop-down menu opens in which the corresponding data sheet can be selected.
Figure 5: Form editor
A grouping must be created in each data sheet. A separate control can be created for each grouping. After the first control has been fully created (this also includes the grouping), it can be copied. Then, only the grouping and the assignment to an executor or a user group have to be adapted in the copy (see fig. 6). Important: A separate control must be created for each grouping. A data sheet is only visible to the responsible person specified if it has also been assigned to a control.
Figure 6: Selection of a data sheet and a grouping