The DataPilot dialog
The function of the DataPilot is mainly managed in two places: first in the DataPilot dialog and second through manipulations of the result in the spreadsheet. This section describes the dialog in detail.
Basic layout
In the DataPilot are four white areas, that show the layout of the result. Beside these fields are buttons with the names of the fields in your data source. To define a layout, drag and drop these field buttons into the white areas.
The Data Fields area in the middle must contain at least one field. Advanced users can use more than one field here. For the data field an aggregate function is used. For example, if you move the sales field into the Data fields area, it appears there as Sum – sales.
Row Fields and Column Fields indicate in which groups the result will be sorted in the rows and columns. If there are no entries in one of these areas, then partial sums will not be provided for the corresponding rows or columns. Often more than one field is used at the same time to get partial sums for rows or columns. The order of the fields gives you the order of the sums from overall to specific.
For example, if you drag region and employee into the Row Fields area, the sum will be divided into the employees. Within the employees will be the listing for the different regions.
Fields that are placed into the Page Fields area 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 result shown for each employee.
To remove a field from the white layout area just drag it to the border and drop it (the cursor will change to a crossed symbol) or click the Remove button.
More options
To expand the DataPilot and show more options, click More.
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. This could result in overwriting any data that is already in that location. To avoid overwriting data, you can leave Results to as - undefined -; and enter a cell reference to tell the DataPilot where to show the results. However, a generally better approach is to use - new sheet - to add a new sheet to the spreadsheet file and place the results there.
 |
In this case the word - undefined - is misleading because the output position is in fact defined. |
Ignore empty rows
If the source data is not in the recommended form, this option tells the DataPilot to ignore empty rows.
Identify categories
If the source data has missing entries in a list and does not meet the recommended data structure, the DataPilot adds it to the listed category above it. If this option is not chosen, then the DataPilot inserts (empty).
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.
Logically, the behavior without category recognition is better. 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
Use this option to add or hide 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
If you double-click on a single cell in the DataPilot result, this function gives a more detailed listing of an individual entry. If this function is disabled, the double-click will keep its usual edit function within a spreadsheet.
More settings for the fields
The options discussed in the previous section are valid for the DataPilot in general. Additionally, you can change settings for every field that you have added to the DataPilot layout. Do this either by clicking on the Options button in the DataPilot dialog or by double-clicking 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.
In the Displayed value section, you can choose more possibilities for analysis 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 display the partial sums for each category. Partial sums are deactivated by default. They are 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.
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, select User-defined.
Normally, the DataPilot does not show a row or column for categories that have no entries in the underlying database. By choosing 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 golfing category.
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.
| Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA). |
