The database (preconditions)
The basis for work with the DataPilot is always a list with your data in raw format. Such a list is comparable to a database table. The table consists of rows (data sets) and columns (data fields). The field names are in the first row above the list.
Later we will explain that the data source could be an external file or database. The simplest use case is that your data is contained in a Calc spreadsheet. For such a list, Calc offers sorting functions that do not depend on the DataPilot.
For processing data in lists, the program must know in which area of the spreadsheet the table is. A Calc table can be anywhere in the sheet, in any position. It is also possible, that a spreadsheet contains several unrelated tables.
Calc recognizes your lists automatically. It uses the following logic:
Starting from the cell you’ve selected (which must be within your list), Calc checks the surrounding cells in all 4 directions (left, right, above, below). The border is recognized if the program discovers an empty row or column, or if it hits the left or upper border of the spreadsheet.
This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.
|| No empty rows, no empty columns within lists!
If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected. This might be useful in only very few cases.
|| For sorting, filtering or using the DataPilot, always select only one cell.
A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.
In addition to these formal aspects, the logical structure of your table is very important for using the DataPilot.
|| Calc lists must have the normal form; that is, they must have a simple linear structure.
When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example in the section
Sales volume overview. This will give you a list of bad ideas, that you can find very often among spreadsheet users who are not informed about the possibilities of processing lists within a spreadsheet.
First bad idea: You could have made several sheets. For example, you could have made a sheet for each group of articles. Analyses are then only possible within each group. Analyses for several groups would then be a lot of hassle.
Second bad idea: In the turnover list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.
Third bad idea: You could have entered the amounts in chronological order. At the end of each month you could have made a sum. In this case, a sorting of the list for different criteria is not possible, and you could not use the DataPilot. The rows with the sums would be treated by the DataPilot like any other amount you have entered. Getting monthly results is one of the very fast and easy features of the DataPilot.