Sorting, drilling, filtering, refreshing

OpenOffice.org 3 Calc Guide

Chapter 8 Using the DataPilot

< Previous Section

Next Section >

• Example: Sales volume overview
• Example: Timekeeping
• Example: Frequency distribution
• DataPilot functions
• Data source
• DataPilot dialog
• Working with the results of the DataPilot
• Sorting, drilling, filtering, refreshing
• Multiple data fields
• GETPIVOTDATA

< Previous Page

Next Page >

Sorting of the results

The result of any DataPilot is sorted (categories) in columns and rows in an ascendent way. You have two possibilities, to change the sorting:

• Sort manually by using drag and drop.
• Sort automatically through choosing the options in the preferences dialog of the row or column field.

Sort manually by using drag and drop

Change the order within the categories just 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.

Automatic sorting

To manage automatic sorting, start the options of the preferences of the row or column field: right-click on the table area with the DataPilot result and choose Start. Open the preferences of the row or column field with a double-click onto the field. In the Data Field dialog, click Options to display the Data Field Options dialog.

Options for a row or column field

For Sort by choose either Ascending or Descending. On the left side is a drop-down list where you can choose for which field this setting should apply. With this method you can specify that sorting does not happen according to the categories but according to the results of the data field.

Drill (show details)

With a drill you can show for the single, compressed values in the DataPilot result the related detailed data. You can activate a drill through a double-click on the cell or use Data > Group and Outline > Show Details. You have to distinguish two cases:

1. 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 category golfing are subdivided according to another field.
   Since there are more possibilities for a subdivision, a dialog appears where you can make your needed settings.


Before the drill down for the category golfing


Selection of the field for the subdivision


After the drill down

To hide the details again, double-click on the cell golfing or choose Data > Group and Outline > Hide Details.

The DataPilot remembers your selection (in our example the field employee), so that for the next drill down for a category in the field region the dialog does not appear. To remove the selection employee, open the DataPilot dialog by right-clicking and choosing Start, then delete the unwanted selection in the row or column field.

2. 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 and you'll get a list of all data sets that are included in this value. This list is shown in an new sheet.


New table sheet after the drilldown for a value in a data field.

Filtering

For limiting the analysis with the DataPilot to a subset of the information that is contained in the data basis, you can filter with the DataPilot.

To use the filtering function that is implemented in the DataPilot, just click on the field Filter on the left side above the results.

Filter field in the upper left area of the DataPilot

In the Filter dialog, you can define up to three filter options that are used in the same way as Calc’s default filter.

Dialog for defining the filter

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 have to always update (refresh) the DataPilot manually.

Changes in the source data could appear in two ways:

1. 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 Update (or choose Data > DataPilot > Refresh from the menu bar).
2. 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. A change for the data reference for an existing DataPilot is not possible. In this case you have to redo the DataPilot.