Sorting, drilling, filtering, refreshing
Sorting the result
The result of any DataPilot is sorted (categories) in columns and rows in an ascending order. You can change the sorting in two ways:
- Sort manually by using drag and drop.
- Sort automatically by choosing the options in the preferences dialog of the row or column field.
Sort manually by using drag and drop
You can change the order within the categories by moving the cells with the category values in the result table of the DataPilot.
Please be aware that in Calc a cell has to be selected. It is not enough that this cell contains the cell cursor. The background of a selected cell is marked with a different colour. To achieve this, click in one cell with no extra key pressed and redo this by pressing additionally the Shift or Ctrl key. Another possibility is to keep the mouse button pressed on the cell you want to select, move the mouse to a neighbor cell and move back to your original cell before you release the mouse button.
Sort automatically
- To sort automatically, start the preference options of the row or column field by right-clicking on the table area with the DataPilot result.
- Select Start to open the DataPilot.
- Within the Layout area of the DataPilot, double-click the field you want to sort.
- In the Data Field dialog, click Options to display the Data Field Options dialog.
For Sort by select how to display the information, either Ascending or Descending. On the left side is a drop-down list where you can select the field this setting should apply to. With this method you can specify that sorting does not happen according to the categories but according to the results of the data field.
Drilling (showing details)
Drill allows you to show the related detailed data for a single, compressed value in the DataPilot result. To activate a drill, double-click on the cell or use Data > Group and Outline > Show Details. You have to distinguish two cases:
- The active cell is the category of a row or column field.
In this case drill means an additional breakdown into the categories of another field.
For example, double-click on the cell with the value golfing in the row field region. In this case the values that are aggregated in the golfing category are subdivided according to another field.
Since there are more possibilities for a subdivision, a dialog appears so you can choose your settings. - The active cell is a value of the data field.
In this case drilldown means a listing of all data entries of the data source that aggregates to this value.
Double-click on the cell with the value $18,741. You now have a list of all data sets that are included in this value. This list is shown in an new sheet.
To hide the details again, double-click on the cell containing golfing or choose Data > Group and Outline > Hide Details.
The DataPilot remembers your selection (in our example the employee field), so that for the next drill down for a category in the region field, the dialog does not appear. To remove the employee selection, open the DataPilot dialog by right-clicking and choosing Start, then delete the unwanted selection in the row or column field.
Filtering
To limit the DataPilot analysis to a subset of the information that is contained in the database, you can filter with the DataPilot.
 |
An Autofilter or default filter used on the sheet has no effect for the analysis of the DataPilot. The DataPilot always uses the complete list that was selected when it was started. |
To do this, click Filter on the top left side above the results.
In the Filter dialog, you can define up to three filter options that are used in the same way as Calc’s default filter.
|
Even if they are not called a filter, page fields are a practical way to filter the results. The advantage is that the filtering criteria used are clearly visible. |
Updating (refreshing) changed values
After you have created the DataPilot, changes in the source data do not cause an automatic update in the resulting table. You always have to update (refresh) the DataPilot manually.
Changes in the source data could appear in two ways:
- The content of existing data sets has been changed. For example, you might have changed a sales value afterwards. To update the DataPilot, right-click in the result area and choose Refresh (or choose Data > DataPilot > Refresh from the menu bar).
- You have added or deleted data sets in the original list. In this case the change means that the DataPilot has to use a different area of the spreadsheet for its analysis. Changing the data reference for an existing DataPilot is not possible. In this case you have to redo the DataPilot.
Cell formatting
The cells in the results area of the DataPilot are automatically formatted in a simple format by Calc. You can change this formatting using all the tools in Calc, but please note that if you make any change in the design of the DataPilot or any updates, the formatting will return to that applied automatically by Calc.
For the number format in the data field, Calc uses the number format that is used in the corresponding cell in the source list. In most cases, this is useful (for example, if the values are in the currency format, then the corresponding cell in the result area is also formatted as currency). However, if the result is a fraction or a percentage, the DataPilot does not recognize that this might be a problem; such results must either be without a unit or be displayed as a percentage. Although you can correct the number format manually, the correction stays only until the next update.
| Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA). |
