The function of the DataPilot is mainly managed in two places: first in the dialog of the DataPilot and second through manipulations of the result in the spreadsheet. This section describes the dialog in detail.
Basic layout
In the DataPilot are four big white areas, that indicate schematically the layout of the result. Beside these fields are button-like fields with the names of the fields in your data source. You can choose your layout by moving these fields with the mouse into the white areas.
DataPilot dialog
The area Data Fields in the middle must contain at least one field. Normally it has exactly one field. Advanced users could use more than one field here. For the data field an aggregate function is used. For example, if you move the field sales into the area Data fields, it appears there as Sum – sales.
Row Fields and Column Fields indicate in which groups the result will be sorted in the columns and rows. If there are no entries in the row or column fields, then there won’t be partial sums for the corresponding rows or columns. There are often more than one field at the same time used to get partial sums for rows or columns. The order of the fields gives you then the order of the sums from overall to specific.
If you drag for example region and employee into the area Row Fields, the result will be the following: in the vertical direction (row wise), the sum will be divided into the branches. Within the branches will be the listing for the different regions.
Fields that are placed into the area Page Fields appear in the result above as a drop down list. The summary in your result takes only that part of your base data into account that you’ve selected. For example, if you use the field Employee as a page field, you can filter the shown results for each employee.
To remove a field from the white layout area just move it with the mouse to the border and let it drop there. The cursor will change to a crossed symbol. As alternative you can use the button Remove.
More options
To expand the DataPilot and show more options, click More.
Expanded dialog of the DataPilot
Results to
Results to defines where your result will be shown. If you do not enter anything, the DataPilot will put your result below the list, that contains your data. If you leave – undefined – and you enter a cell reference, then the DataPilot will show the results at this place. It's useful instead of using the setting – undefined – to use the setting - new sheet –. Calc adds in this case a new sheet. This is useful, because it is much cleaner and makes sure that no areas with data are overwritten.<ref name="ftn1">The word - undefined – used in this place is wrong and misleading. The output position is also exactly defined. It is somewhat annoying, that the default setting is not -new sheet -, which is in almost all cases the needed setting.</ref>
Ignore empty rows
By using this setting, completely empty rows in the source area will be ignored by the DataPilot. As mentioned in the requirements for the database, empty rows are against the rules for processing list-like data within a spreadsheet. Therefore, this setting is used on the rare occasions when the source data is not in the expected (recommended) form.
Identify categories
This option also has an effect if the basic data does not meet the recommended data structure. It takes effect when there are missing entries in a list, as shown in this example:
Example of Identify categories
The option Identify categories would make sure that in this case the lines 3 and 4 are added to the product Apples and the row 6 is added to Pears. Without category recognition, the DataPilot shows an empty category.
Example without Identify categories
From a purely logical point of view, the behavior without category recognition is more correct. A list with the shown missing entries is also less useful, because you cannot use other functions (like sorting or filtering).
Total columns / total rows
With this option you decide if the DataPilot will show an extra row with the sums of each column or if it adds on the very right a column with the sums of each row. In some cases, an added total sum is useless, for example if your entries are accumulated or in comparisons.
Add filter
This option allows the blending or hiding of the cell labelled Filter above the DataPilot results. This cell is a convenient button for additional filtering options within the DataPilot.
Enable drill to details
In the table area of the DataPilot result, a double-click has a special effect. Normally a double-click on any cell switches to the edit mode, but within the DataPilot a listing of the detailed data that give you the shown result will appear. By disabling this function the double-click will keep its usual function within a spreadsheet.
More settings for the fields
The discussed options are valid for the DataPilot in general. Additionally, you can change settings for every field, that you have added to the DataPilot layout. You can reach the dialog for the field settings either by clicking on the button Options in the DataPilot dialog box or by a double-click on the appropriate field.
There are differences between data fields, row or column fields, and page fields of the DataPilot.
Data fields
In the preferences dialog of a data field you can select the Sum function for accumulating the values from your data source. In many cases you will need the sum function, but other functions (like standard distribution or a counting function) are also available. For example, the counting function can be useful for non-numerical data fields.
On the Data Field dialog, click More to see more options.
Expanded dialog for a data field
In the Displayed value section, you can choose more analyzing possibilities by using the aggregate function. Depending on the setting for Type, you have to choose a definition for Base field and Base element.
| Type
| Base field
| Base element
| Analysis
|
| Normal
| —
| —
| Simple use of the chosen aggregate function (for example, sum)
|
| Difference from
| Selection of a field from the data source of the DataPilot (for example, employee)
| Selection of an element from the selected base field (for example, Brigitte)
| Result as difference to the result of the base element (for example, Sales volume of the employees as difference of the sales volume of Brigitte)
|
| % of
| Selection of a field from the data source of the DataPilot (for example, employee)
| Selection of an element from the selected base field (for example, Brigitte)
| Result as a ratio based on the result of the base element (for example, Sales result of the employee relative to the sales result of Brigitte)
|
| % difference from
| Selection of a field from the data source of the DataPilot (for example, employee)
| Selection of an element from the selected base field (for example. Brigitte)
| Result as relative difference to the result of the base element (for example, Sales volume of the employees as relative difference of the sales volume of Brigitte)
|
| Running total in
| Selection of a field from the data source of the DataPilot (for example, date)
| —
| Result as a continuing sum (for example, Continuing sum of the sales volume for days or months)
|
| % of row
| —
| —
| Result as relative part of the result in the whole row (for example the row sum)
|
| % of column
| —
| —
| Result as relative part of the total column (for example, the column sum)
|
| % of total
| —
| —
| Result as relative part of the overall result (for example the total sum)
|
| Index
| —
| —
| Default result x total result / (row result x column result)
|
Row and column fields
In the preferences dialog for the row or column fields, you can choose if there are partial sums for each category. This is deactivated by default. It is only useful if the values in one row or column field can be divided into partial sums for another (sub)field.
Some examples are shown in the next three figures.
No subdivision with only one row or column field.
Division of the regions for employees without partial sums
Division of the regions for employees with partial sums
Choose the option Automatically to use the aggregate function for the partial results that can also be used for the data fields (see above). To set up the aggregate function for the partial results independently from the overall settings of the DataPilot, choose User-defined.
preferences dialog of a row or column field
Normally, the DataPilot does not show a row or column for categories that have no entries in the underlying database. With the help of the Show items with no data option, you can force this.
For illustration purposes, the data was manipulated in such a way, that the employee Brigitte has no sales values for the category golfing.
Default setting
Setting “Show Items with no data”
Page fields
The preferences dialog for page fields is the same as for row and column fields, even though it appears to be useless to do the same settings as described for the row and column fields. With the flexible use of the DataPilot you can switch often the use of the different fields for pages, columns or rows. The fields keep the settings that you made for them. For this, the page field has the same properties as a row or column field. These settings only take effect when you use the field not as page field but as row or column field.
