Calc Guide

8

Chapter
Using the DataPilot

This PDF is designed to be read onscreen, two pages at atime. If you want to print a copy, your PDF viewer shouldhave an option for printing two pages on one sheet ofpaper, but you may need to start with page 2 to get it toprint facing pages correctly. (Print this cover page
separately.)CopyrightThis document is Copyright © 2009–2010 by its contributors as listed
in the section titled Authors. You may distribute it and/or modify it
under the terms of the Creative Commons Attribution-Share Alike
License, version 3.0 or later.
All trademarks within this guide belong to their legitimate owners.

Authors
Stefan Weigel (original author)
Jean Hollis Weber
Sigrid Kronenberger
Andy Brown
Sharon Whiston
Claire Wood

Feedback
Please direct any comments or suggestions about this document to:authors@documentation.openoffice.org

Acknowledgments
The chapter is adapted from a German original written by Stefan
Weigel and translated into English by Sigrid Kronenberger. Andy
Brown, Jean Hollis Weber, and Sharon Whiston edited the translation,
and Andy Brown recaptured the illustrations from an English version of
OpenOffice.org.

Publication date and software version
Published 8 September 2010. Based on OpenOffice.org 3.2.

You can download
an editable version of this document fromhttp://oooauthors.org/english/userguide3/published/

Contents
Copyright............................................................................................... 2
Introduction........................................................................................... 5
Examples with step by step instructions................................................5
Example 1: Sales volume overview.....................................................5
Practical problems and questions....................................................6
Solution...........................................................................................6
Advantages......................................................................................9
Example 2: Timekeeping...................................................................13
Practical problems and questions..................................................13
Solution.......................................................................................... 15
Differences and advantages...........................................................19
Example 3: Frequency distribution...................................................20
Solution with a matrix formula......................................................22
Solution with the DataPilot............................................................23
DataPilot functions in detail.................................................................27
The database (preconditions)............................................................27
Start.................................................................................................. 28
Data source....................................................................................... 29
Calc spreadsheet...........................................................................29
Registered data source..................................................................30
External data (OLAP).....................................................................30
The DataPilot dialog.......................................................................... 30
Basic layout...................................................................................30
More options.................................................................................. 31
More settings for the fields............................................................34
Data fields...................................................................................... 34
Row and column fields...................................................................36
Page fields.....................................................................................39
Working with the results of the DataPilot.........................................39
Start the dialog..............................................................................39
Change layout by using drag and drop..........................................39
Using the DataPilot

3

Grouping rows or columns.............................................................39
Grouping of categories with scalar values.....................................40
Grouping of categories with date or time values...........................41
Grouping without the automatic creation of intervals...................42
Sorting the result........................................................................... 43
Drilling (showing details)...............................................................45
Filtering......................................................................................... 46
Updating (refreshing) changed values...........................................47
Cell formatting............................................................................... 48
Multiple data fields...........................................................................48
Shortcuts.......................................................................................... 52
Function GETPIVOTDATA....................................................................53
Difficulty........................................................................................... 53
Syntax............................................................................................... 54
First syntax variation.....................................................................54
Second syntax variation.................................................................55

4

Using the DataPilot

Introduction
Many requests for software support are caused by complicatedformulas and solutions to simple day to day procedures. For more
efficient and effective solutions use the DataPilot, a tool for combining,
comparing, and analyzing large amounts of data easily. By using the
DataPilot, you can view different summaries of the source data, display
the details of areas of interest, and create reports, whether you are a
beginner or an intermediate or advanced user.
This chapter is divided into two sections:• “Examples with step by step instructions” uses three typical cases

to demonstrate the advantages and applications of the DataPilot.
Follow the examples to learn how easy it is to use the DataPilot.
• “DataPilot functions in detail” starting on page 27 describes the
DataPilot in detail. You can use this part for looking up how afunction is used.

Examples with step by step instructions
This section demonstrates some of the possibilities of the DataPilot in
typical use cases. By following the step by step instructions, you canrecreate the examples and learn about the power of the DataPilot.

Example 1: Sales volume overview
A typical introductory example in courses and books for beginners withspreadsheets is a simple sales volume overview.

Figure 1: Typical example for beginners
This example demonstrates the user interface and how to insert text
and numbers into cells. Useful aids like AutoFill and drag and drop
have been demonstrated in other chapters. The most important part is

Examples with step by step instructions

5the connection between cells through formulas, for example addition
with the plus operator or the SUM function.
This small exercise might be useful for a first contact with theprogram, but it shows only a very small fraction of the tasks in an
office. To create such a sales overview, you also need the original data.
That is, before you can use a spreadsheet for creating the sales
overview, you need to add many single purchases from different lists
and then enter the sums into the relevant cells C5 to F7.

Practical problems and questions• For displaying additional values for May, June, July, and so on, youneed to add extra columns. This means that you have to change
the structure of the calculation sheet. This is not only somewhat
inefficient from a workflow point of view, it also adds some
practical questions: How do references react, if you add morecolumns or rows to the sum formulas?
• The layout, where the timeline is displayed horizontally, might beless convenient if you add more months. A vertical layout might
be a more efficient use of space. How can the table then betransposed? Do you have to enter everything again?
• What if the management asks unexpected questions or adds anadditional subdivision for the different sales regions or aconstraint of the sales for a given employee. In these cases you
again have to manually add all the sums and create different
tables in many variations.
• Is it really acceptable and realistic to create such an overview byadding the different values manually? This is really a lot of workand extremely error-prone!

Solution
The most important part of your task in the example is the addition of
the Total sales per month cells. This had to be done manually. To dothis automatically with the program, just get the data into Calc. You
can enter the single numbers by hand or you can import a file fromyour bookkeeping software. In any case we assume a continuous tablethat keeps track of all sales in a somewhat primitive form.

6

Using the DataPilot

Figure 2: Basic data in Calc
You can create the sales volume overview by following these
instructions:
1) Select the cell A1 (or any other single cell within the list).
2) Select Data > DataPilot > Start. On the Select Source dialog,
select Current selection and click OK.
3) The DataPilot dialog (Figure 3) has four white layout areas andseveral fields that look like buttons. These small fields are thetitles of the different columns of your list.
•
Move the date field into the Column Fields area.
•
Move the sales field into the Data Fields area.
•
Move the category field into the Row Fields area.
4) Click More, to see more options in the lower part of the dialog.
5) In the Results to field, select – new sheet – from the drop-down
list.
Examples with step by step instructions76) Click OK.

Figure 3: DataPilot dialog
7) The result appears on a new sheet. It has the desired structure,
but the columns are not yet grouped into months.

Figure 4: DataPilot result without grouping8) To group the columns, select cell B4 or any other cell that
contains a date. Then select Data > Group and Outline >Group. On the Grouping dialog (Figure 5), make sure Intervals
and Months are selected in the Group by section, and click OK.
The result is now grouped for months (Figure 6).
8

Using the DataPilot

Figure 5: Grouping on months

Figure 6: DataPilot result grouped for months
In this result you will recognize the beginners’ example. It is very easy
to produce, without any further knowledge about the spreadsheet. You
do not have to enter any formulas.

Advantages
1) No manual entering or adding of any values is necessary. There isless work and fewer errors.
2) The layout is very flexible: months are listed horizontally andfields vertically or vice versa, in two mouse clicks.
3) Additional differentiating factors are immediately available.
4) Many types of evaluation are possible; for example, number oraverage instead of sum, accumulated values, comparisons, and soon.

Examples with step by step instructions

9

We will now demonstrate some of these advantages.
Starting with the result of Figure 6, drag the Date field under theCategory field, as shown in Figure 7.

Figure 7: Drag Date field under Category field
Now the summary is as shown in Figure 8.

Figure 8: Changed layout

10

Using the DataPilot

To transpose the table completely, just drag the Category field above
the area of the displayed values, to cell C3 (see Figure 9). The result of
this action is shown in Figure 10.

Figure 9: Drag Category field to right of Data field

Figure 10: Transposed layout of Figure 8
In contrast to the beginners’ example in Figure 1, it is now very simple
to view or add different aspects of the underlying data. For example, to
see the values for different regions, do the following:
1) Select the cell A3 (or any other single cell that is part of the
DataPilot result).
2) Select Data > DataPilot > Start to start the DataPilot again.
Drag the Region field into the Row Fields area. Depending on theorder you choose for the row fields, the result is either regionswith date subdivisions or vice versa.
3) Click OK. The result is shown in Figure 11.

Examples with step by step instructions

11

Figure 11: Additional subdivision into regions, added laterIn another variation you may want to add the employees.
1) Select the cell A3 (or any other single cell that is part of the
DataPilot result).
2) Select Data > DataPilot > Start, to start the DataPilot again.
•
You do not need the Region field in this case. Drag it out of
the layout area.
•
Drag the Employee field into the Page Fields area.
3) Click OK. The result is shown in Figure 12.
Fields that you use as page fields are placed in the result above thesummary with the name Filter. You then have a drop-down list that you
can use to show only the sums of a given employee.
Up to now we have not seen the most powerful features of the
DataPilot. The following examples will show you more.

12

Using the DataPilot

Figure 12: Selection of subtotals for several employees.

Example 2: Timekeeping
This example is often used by consultants and in several variations inuser support. The task is to provide a means for one or more users tokeep track of working hours.
A typical way of doing this is to create a spreadsheet per month and asum sheet with all the results of one year. For each employee, there is
one file (see Figures 13 and 14 for examples of two pages from the filefor one employee).

Practical problems and questions• It is very difficult and time-consuming to create the timekeeping

table: 12 sheets that have to be copied from a raw template andadjusted for each month, and a sheet with all the yearly sums
with references to all the other sheets. Users often search for a
macro to make the creation easier.
• The file shown contains only the data of one employee. How can
you get all the data for all the employees, so that you can have asummary of all the work hours from all employees of adepartment or the whole company?
• How can you compare employees and/or departments?
• The shown file contains data for one year. How can you compare
it with the data of the previous years?

Examples with step by step instructions

13

Figure 13: One month of timekeeping for one employee

Figure 14: Yearly sums for one employee
14

Using the DataPilot

Solution
To use the DataPilot for this task, collect all the data into one single
table. This can be done either manually or by importing a file from an(electronic) timekeeping machine.
In very simple cases, each employee takes care of their own working
hours. If you need calculations that cover several employees,
departments, or the whole company, just copy everything into one hugetable (Figure 15).

Figure 15: Data basis in Calc
Using the DataPilot requires only 12 mouse clicks and gives you a niceoverview within seconds :1) Select the cell A1 (or any other single cell within the list).
2) Choose Data > DataPilot > Start and click OK.
3) On the DataPilot dialog (Figure 16):•
Drag date into the Row Fields area.
•
Drag hours into the Data Fields area. Notice that it becomes
Sum – hours.
•
Drag name into the Column Fields area.
4) Click More to show more options in the lower part of the dialog.
5) In the Results to field, select – new sheet – from the drop-down
list.
6) Click OK.
Examples with step by step instructions

15

Figure 16: Part of DataPilot dialog
The result appears on a new sheet.

Figure 17: The evaluation, done within seconds with DataPilot

16

Using the DataPilot

The result is much more powerful than is possible with the classic
formula-based calculation. For example, you can summarize the daily
results to a monthly result very easily:1) To group together the rows, select the cell A5 (or any other cellthat contains a date).
2) Choose Data > Group and Outline > Group and click OK. The
result is now grouped into months.

Figure 18: Monthly sums
If you need a result with a percentage, start the DataPilot from thispage.
1) Select the cell A3 (or any other single cell that contains a resultof the DataPilot).
2) Choose Data > DataPilot > Start.
3) Double-click on Sum – hours to open the Data Field dialog(Figure 19).
4) Click on More, to see more options.
5) Switch Displayed value > Type to % of column.
6) Click OK twice.
The result shows percentages as a decimal number. If you prefer tohave a percent format, select the cells and click on the % icon in themenu bar.

Examples with step by step instructions

17

Figure 19: Properties of the data field

Figure 20: Result with percentages
To get a comparison between employees, start the DataPilot again:
1) Select the cell A3 (or any other cell that contains a result of the
DataPilot).
2) Choose Data > DataPilot > Start.
3) Double-click on Sum – hours to open the Data Field dialog.
4) Click More, to see more options.
•Switch the Type of the displayed value to Difference from.
•
Switch the Base Field to name.
•
Switch the Base Item to Brigitte.
5) Click OK twice.
18

Using the DataPilot

Figure 21: Absolute comparison with Brigitte
As a final example we switch to an accumulated view; that is,continuing sums of all values:
1) Choose Data > DataPilot > Start.
2) Double-click on Sums – hours to open the Data Field dialog.
3) Click More, to see more options.
•Switch the type of the displayed value to Running total in.
•
Switch the Base Field to Date.
4) Click OK twice.

Figure 22: The DataPilot now shows accumulated values

Differences and advantages
These examples show an important aspect of the DataPilot. Normally
you have to collect your data according to the way you want the result
to be represented. This means you have to use a specific structure andyou are stuck with it.
The DataPilot works more like a real database. The source data iscollected in a simple spreadsheet that contains all data. Only when you
want to look at it do you select which part of the data you want to use.
Examples with step by step instructions

19

Example 3: Frequency distribution
For showing the frequency of incidents, Calc uses the function
FREQUENCY. This formula has to be used in a so-called matrix
formula. This is an advanced feature. Alternatively you can use the
DataPilot, which requires almost no knowledge of spreadsheets.
In our example we want to investigate the number of emails that go to
the Germanophone support mailing list (users@de.openoffice.org). We
want to know how the activity on the list is distributed during the day.
The starting point for this example is the mailbox file of theThunderbird mail client. The command
grep 'Delivery-Date:' Inbox
creates a text file that contains for the timespan of more than 2 years
one line for each message, with date and time (Figure 23).

Figure 23: Raw data for the frequency ofthe messages

20

Using the DataPilot

To import the data into a table in Calc, open the text file users.txt
containing the raw data. The Text Import dialog appears. Here you canchoose your import options. Use the following properties:
Separator options: Fixed width
Divide into columns at position 20, 31 and 40
Column types:
Column 1: HideColumn 2: Date (DMY)
Column 3: Standard
Column 4: Hide

Figure 24: Import settings
Figure 25 shows the imported raw data with a line added at the top
containing titles for each column.

Examples with step by step instructions

21

Figure 25: Raw data in Calc

Solution with a matrix formula
To calculate the frequency you have to create 24 classes, one for eachhour. In the next column you enter the number of emails with thefunction FREQUENCY.

Figure 26: FREQUENCY function ina matrix formula
22

Using the DataPilot

The first argument is the cell area with the times of all approximately
38,000 emails. The second argument is the cell area E2:E25 that
describes the frequency classes. To enter the formula, first select thecell area F2:F25, then enter the formula. Then finish the formula byusing the key combination Shift+Ctrl+Enter. This indicates to theprogram that you want to use a matrix formula. To indicate the matrix
formula, the program uses curly brackets.
This technique is most often known only to advanced users.

Solution with the DataPilot
With the DataPilot you can achieve the same result much easier andfaster. The solution is also possible for less advanced users. Startingwith the raw data (Figure 25), you need only a few mouse clicks.
1) Select the cell A1 (or any other cell within the list).
2) Choose Data > DataPilot > Start and click OK.

Figure 27: Part of DataPilot dialog

Examples with step by step instructions233) In the DataPilot:•
Drag Time into the Row Fields area.
•
Drag Date into the Data Fields area.
4) Click More to show more options in the lower part of the dialog.
5) Choose – new sheet – for Results to.
6) In this case we need to count the number of values, not their sum.
Double-click on Sum – Date to open the Data Field dialog andselect the function Count (see Figure 28).
7) Click OK. As an intermediate result, you get a DataPilot table thathas for every time within the raw data a separate line.

Note

This may be a very time-consuming process because of the large
number of items. The time does not depend that much on thenumber of lines but rather on the number of rows needed for thetable that contains the results.

Figure 28: Properties of the data field
8) For grouping the rows, select the cell A4 or any other cell that
contains a time.
9) Choose Data > Group and Outline > Group, select for theinterval Hours and click OK. The result is now grouped according
to hours.

24

Using the DataPilot

Figure 29: properties for grouping according to hours
10) Figure 30 shows the Data Field dialog for the data field Number– Date. Click More and select as type % of column. The result isshown in Figure 32. Figure 31 shows the absolute occurrence.
Whether the relative values are shown as a decimal (0.1) or as apercentage (10%) depends only on the cell formatting itself andhas nothing to do with the DataPilot.

Figure 30: Data Field settings for relative values

Examples with step by step instructions

25

Figure 31: Frequency
distribution with the DataPilot

26

Figure 32: Relative
occurrence

Using the DataPilot

DataPilot functions in detailThis part describes the use and options of the DataPilot in detail.

The database (preconditions)
The first thing needed to work with the DataPilot is a list of raw data,
similar to a database table, consisting of rows (data sets) and columns(data fields). The field names are in the first row above the list.
The data source could be an external file or database. For the simplest
case, where data is contained in a Calc spreadsheet, Calc offers sorting
functions that do not require the DataPilot.
For processing data in lists, the program needs to know where in the
spreadsheet the table is. The table can be anywhere in the sheet, inany position. A spreadsheet can 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 ifthere are no empty rows or columns in your list. Avoid empty lines (forexample for formatting). You can format your list by using cell formats.
Rule

No empty rows or empty columns are allowed within lists.

If you select more than one single cell before you start sorting, filteringor calling the DataPilot, then the automatic list recognition is switched
off. Calc assumes that the list matches exactly the cells you haveselected.
Rule

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 when using the DataPilot.

DataPilot functions in detail

27

Rule

Calc lists must have the normal form; that is, they must have asimple 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 “Examples with step bystep instructions” starting at page 5. Here are some mistakes
commonly made by inexperienced spreadsheet users:
1) You could have made several sheets, for example, a sheet for eachgroup of articles. Analyses are then possible only within eachgroup. Analyses for several groups would be a lot of work.
2) In the Sales list, instead of only one column for the amount, you
could have made a column for the amounts for each employee.
The amounts then 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 foreach employee if you have entered everything in one column.
3) You could have entered the amounts in chronological order. At theend of each month you could have made a sum total. In this case,sorting the list for different criteria is not possible because the
DataPilot will treat the sum totals the same as any other figure.
Getting monthly results is one of the very fast and easy featuresof the DataPilot.

Start
Start the DataPilot with Data > DataPilot > Start. If the list to beanalyzed is in a spreadsheet table, select one cell within this list. Calc
recognizes and selects the list automatically for use with the DataPilot.

28

Using the DataPilot

Figure 33: After the start of the DataPilot

Data source
There are usually three possibilities for the DataPilot to have a data
source: a Calc spreadsheet, an external data source that has to beregistered in OpenOffice.org, and access to an OLAP system (notavailable at this time).

Calc spreadsheet
The simplest and most often used case is analyzing a list in a Calc
spreadsheet. The list might be updated regularly or the data might beimported from a different application.
A huge list can, for example, be copied from a different application andpasted into Calc. The behavior of Calc while inserting the data depends
on the format of the data. If the format is acceptable, the data is copied
directly into Calc. However, if the data is in plain text, a dialog appears
with the text import assistant; see Chapter 1 (Introducing Calc) for
more more information.

DataPilot functions in detail29Calc can import data from a huge number of foreign data formats, forexample from other spreadsheets (Excel, Lotus 1, 2, 3), from databases(like dBase), and from simple text files.
The drawback of copying or importing foreign data is that it will not
update automatically if there are changes in the source file. With a
Calc file you are limited to 65535 rows.

Registered data source
A registered data source in OpenOffice.org is a connection to data held
in a database outside Calc. This means that the data to be analyzed will
not be saved in Calc. It always uses the data from the original source.
Calc is able to use many different data sources and also databases thatare created and maintained with OpenOffice.org Base. See Chapter 10(Linking Calc Data) for more information.

External data (OLAP)
No implementation exists at the moment so this option is not available.

The DataPilot dialog
The function of the DataPilot is 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 yourdata 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.

30

Using the DataPilot

Figure 34: DataPilot dialog
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 ofthese area, then partial sums will not be provided for thecorresponding rows or columns. Often more than one field is used at
the same time get partial sums for rows or columns. The order of the
fields gives 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 thatpart of your base data into account that you’ve selected. For example,
if you use employee as a page field, you can filter the result shown foreach employee.
To remove a field from the white layout area just drag it to the borderand drop it (the cursor will change to a crossed symbol), or click theRemove button.

More options
To expand the DataPilot and show more options, click More.

DataPilot functions in detail31

Figure 35: Expanded dialog of the DataPilot
Results to
Results to defines where your result will be shown. If you do notenter anything, the DataPilot will put your result below the list that
contains your data. This could result in overwriting any data that isalready 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.1 However, a generally better
approach is to use - new sheet – to add a new sheet to the
spreadsheet file and place the results there.
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 therecommended data structure (see Figure 36), the DataPilot adds itto the listed category above it. If this option is not chosen, then the
DataPilot inserts (empty).

Figure 36: Example of data with missing
entries in Column A
The option Identify categories ensures that in this case rows 3 and 4
are added to the product Apples and row 6 is added to Pears (seeFigure 37).
1 In this case the word - undefined – is misleading because the output position is in factdefined.

32

Using the DataPilot

Figure 37: DataPilot result with Identify categories selected
Without category recognition, the DataPilot shows an (empty)
category (Figure 38).

Figure 38: DataPilot result without Identify categories selected
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 such as 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 isuseless, for example if your entries are accumulated or incomparisons.
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, thisfunction gives a more detailed listing of an individual entry. If thisfunction is disabled, the double-click will keep its usual edit function
within a spreadsheet.

DataPilot functions in detail33

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 byclicking on the Options button in the DataPilot dialog or by doubleclicking on the appropriate field.
There are differences between data fields, row or column fields, andpage 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 manycases you will need the sum function, but other functions (likestandard 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.

Figure 39: Expanded dialog for a data field
In the Displayed value section, you can choose more possibilities foranalysis by using the aggregate function. Depending on the setting for
Type, you have to choose a definition for Base field and Baseelement.

34

Using the DataPilot

Type

Base field

Base element

Analysis

Normal——Simple use of thechosen aggregate
function (forexample, sum)

Difference
fromSelection of afield from thedata source of
the DataPilot(for example,
employee)

Selection of anelement from theselected basefield (forexample,Brigitte)

Result as difference
to the result of thebase element (forexample, Sales
volume of theemployees as
difference of thesales volume ofBrigitte)% ofSelection of afield from thedata source of
the DataPilot(for example,
employee)

Selection of anelement from theselected basefield (forexample,Brigitte)

Result as a ratio
based on the resultof the base element(for example, Sales
result of theemployee relative tothe sales result of
Brigitte)% difference
fromSelection of afield from thedata source of
the DataPilot(for example,
employee)

Selection of anelement from theselected basefield (forexample.
Brigitte)

Result as relative
difference to theresult of the baseelement (forexample, Sales
volume of theemployees as
relative difference ofthe sales volume ofBrigitte)

Running
total in

Selection of afield from thedata source of
the DataPilot(for example,
date)

—

Result as acontinuing sum (forexample, Continuing
sum of the sales
volume for days ormonths)% of row——

Result as relative
part of the result in
the whole row (forexample the row
sum)

DataPilot functions in detail

35

Type

Base field

Base element

Analysis% of column——

Result as relative
part of the total
column (forexample, 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 bydefault. 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.

Figure 40: No subdivision with only one row or column field

36

Using the DataPilot

Figure 41: Division of the regions for employees without partial sums

Figure 42: Division of the regions for employees with partial sums
Select the option Automatically to use the aggregate function for thepartial results that can also be used for the data fields (see above). To
DataPilot functions in detail37

set up the aggregate function for the partial results independently
from the overall settings of the DataPilot, select User-defined.

Figure 43: 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. By choosing the Show
items with no data option, you can force this.
For illustration purposes, the data was manipulated in such a way thatthe employee Brigitte has no sales values for the golfing category.

Figure 44: Default setting

Figure 45: Setting “Show Items with no data”
38

Using the DataPilot

Page fields
The preferences dialog for page fields is the same as for row andcolumn fields, even though it appears to be useless to do the samesettings as described for the row and column fields. With the flexible
use of the DataPilot you can often switch the use of the different fields
for pages, columns or rows. The fields keep the settings that you madefor them. For this, the page field has the same properties as a row orcolumn field. These settings only take effect when you use the field not
as page field but as row or column field.

Working with the results of the DataPilot
One very important feature is the flexibility of the DataPilot. An
analysis can be changed with only a few mouse clicks. Some functionsof the DataPilot can be used only with the results of an analysis.

Start the dialog
Right-click in the area of the resulting table of the DataPilot andchoose Start toopen the DataPilot dialog with all current settings.

Change layout by using drag and drop
The easiest and fastest method to change the layout of the DataPilot isdrag and drop. Within the result table of the DataPilot, move one of thepage, column, or row fields to a different position (see “Examples withstep by step instructions” starting on page 5).
You can remove a column, row, or page field from the DataPilot byclicking on and dragging it out of the DataPilot.

Grouping rows or columns
For many analyses or summaries, the categories have to be grouped.
You can merge the results in classes or periods. In the DataPilot you doa grouping after you have first made an ungrouped DataPilot table.
You can access the grouping with the menu entry Data > Group andOutline > Group or by pressing F12. It is important that you selectthe correct cell area. The grouping function is mainly determined by
the type of values that have to be grouped. You need to distinguish if
you have scalar values, date or time values, or other values, such as
text, that you want grouped.

DataPilot functions in detail

39

Note

Before you can group, you have to produce a DataPilot with
ungrouped data. The time needed for creating a DataPilot
depends mostly on the number of columns and rows and not on
the size of the basic data. Through grouping you can produce the
DataPilot with a small number of rows and columns. The
DataPilot can contain a lot of categories, depending on your data
source. Computing time may take several minutes.

Grouping of categories with scalar values
For grouping scalar values, select a single cell in the row or column ofthe category to be grouped.

Figure 46: DataPilot withoutgrouping (frequency of the
km/h values of a radar
control)

Figure 47: DataPilot
with grouping (classesof 10 km/h each)

Choose Data > Group and Outline > Group from the menu bar orpress F12; you get the following dialog.

Figure 48: Grouping dialog with scalar categories
40

Using the DataPilot

You can define in which value range (start/end) the grouping shouldtake place. The default setting is the whole range from smallest to
biggest value. In the Grouping after field you can enter the class size,
this means the interval size (in this example groups of 10 km/h each).

Grouping of categories with date or time values
For grouping date or time values select a single cell in the column or
row of the category that should be grouped. This was demonstrated in
all three examples in the section “Examples with step by step
instructions” starting on page 5.
With the menu entry Data > Group and Outline > Group or bypressing F12, you get the following dialog.

Figure 49: Grouping dialog for categories
with dates or times
You can decide in which date or time area (start/end) the groupingshould take place. The default setting is the whole area from theearliest to the latest value. In the Group by field you can enter theclass size (the interval size), that should be used for grouping.
Possible intervals are: seconds, minutes, hours, days, months, quarters
and years. These can be combined, for example grouping of years andwithin the years according to months.
As alternative you can enter any number of days as grouping interval.

DataPilot functions in detail

41

Tip

For grouping the DataPilot in calendar weeks, define thebeginning as a Sunday or Monday and manually enter thegrouping interval of 7 days.

Grouping without the automatic creation of intervals
If the categories contain text fields, then the automatic creation ofintervals isn't possible. You can define for each category of any data
type which categories you want to put together in one group.
Every time you use the menu entry Data > Group and Outline >Group or you press F12 and you have more than one cell selected,
then all the cells will be selected as one group.

Figure 50: Database with nonscalar
categories (departments)

Figure 51:
DataPilot with
nonscalarcategories

For grouping of non scalar categories, select in the result of the
DataPilot all single categories that you want to put in one group.
Tip

You can select several non-contiguous cells in one step bypressing and holding the Control key while left-clicking with themouse.

Choose the Data > Group and Outline > Group from the Menu bar
or press F12. Repeat this for all groups that you want to create fromthe different categories.

42

Using the DataPilot

Figure 52: Summary of single
categories in one group

Figure 53: Grouping finished

You can change the automatically given names for the groups and the
newly created group field by right-clicking on a name. The DataPilot
will remember these settings, even if you change the layout later on.
For the following pictures, the dialog was called again (with a rightclick) and within the preferences menu the option Automatic wasselected.

Figure 55: Reduced to
the new groups
Figure 54: Renamed groups and partial results

Note

A well-structured database makes manual sorting within the
DataPilot obsolete. In the example shown, you could add anothercolumn with the name Department, that has the correct entry foreach person based on whether the employee’s department
belongs to either the Office or Technical group. The mapping forthis (1:n relationship) can be done easily with the VLOOKUP
function from Calc.

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.

DataPilot functions in detail43

Sort manually by using drag and drop
You can change the order within the categories by moving the cellswith the category values in the result table of the DataPilot.
Please be aware that in Calc a cell must be selected. It is not enoughthat this cell contains the cell cursor. The background of a selected cellis marked with a different color. To achieve this, click in one cell withno extra key pressed and redo this by pressing also 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 backto your original cell before you release the mouse button.
Sort automatically1) To sort automatically, start the preference options of the row orcolumn field by right-clicking on the table area with the DataPilot
result.
2) Select Start to open the DataPilot (Figure 3).
3) Within the Layout area of the DataPilot, double-click the field you
want to sort.
4) In the Data Field dialog (Figure 43), click Options to display the
Data Field Options dialog (Figure 56).

Figure 56: Options for a row or column field
44

Using the DataPilot

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 selectthe 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, doubleclick on the cell or choose 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 thecategories of another field.
For example, double-click on the cell with the value golfing in therow field region. In this case the values that are aggregated in
the golfing category are subdivided according to another field.

Figure 57: Before the drill down for the category golfing
Since there are more possibilities for subdivision, a dialog
appears so you can choose your setting.

Figure 58: Selection of the field for the subdivision

DataPilot functions in detail45

Figure 59: After the drill down
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 theemployee field), so that for the next drill down for a category in
the region field, the dialog does not appear. To remove theemployee selection, open the DataPilot dialog by right-clicking
and choosing Start, then delete the unwanted selection in therow 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 thedata source that aggregates to this value.
Double-click on the cell with the value $18,741 from Figure 57.
You now have a new list of all data sets that are included in thisvalue. This list is shown in a new sheet.

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

Filtering
To limit the DataPilot analysis to a subset of the information that iscontained in the data basis, you can filter with the DataPilot.

46

Using the DataPilotNote

An Autofilter or default filter used on the sheet has no effect for
the analysis of the DataPilot. The DataPilot always uses thecomplete list that was selected when it was started.

To do this, click Filter on the top left side above the results.

Figure 61: Filter field in the upper left area of the DataPilot
In the Filter dialog, you can define of up to 3 filter options that areused in the same way as Calc’s default filter.

Figure 62: Dialog for defining the filter
Note

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 notcause 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 afterward. To
update the DataPilot, right-click in the result area and choose
Refresh (or choose Data > DataPilot > Refresh from the menu
bar).
DataPilot functions in detail47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 of thedata reference for an existing DataPilot is not possible. You must
redo the DataPilot.

Cell formatting
The cells in the results area of the DataPilot are automaticallyformatted 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 returnto the 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 resultsmust 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.

Multiple data fields
Until now we have assumed that the layout of the DataPilot contains
only one data field. However, it is possible to have several data fields inthe middle of the layout. This makes summaries and analyses ofmultiple aspects possible.
You could, for example, list all the sales values per day and additionallygive the number of entries per day. To do this, put both the sales field
and the date field into the Data Fields area. For the date field, selectthe Count-date option for the aggregate function (see Figure 63).
Since every entry has a specific date, this field will give you thenumber of entries for each date. If you group the values per month,
you get an overview with the sales value and the number of closed
sales for each category and month (see Figure 64).

48

Using the DataPilot

Figure 63: Multiple data fields in the DataPilot

Figure 64: DataPilot shows sales value and number of entries
For manipulating the existing data fields, DataPilot result contains afield called Data. You can move it just like row or column fields byusing drag and drop. This is an easy way to archive a different
structure for the results (see Figures 65 and 66).

DataPilot functions in detail49

Figure 65: Layout option for presenting the sums
and numbers of the sales values

Figure 66: Another layout option for presenting the sums andnumbers of the sales values
If you want to put the different data fields in different columns andyour DataPilot does not contain another column field or you sort thedifferent data fields in different rows and don’t have another row field,
then it is useful to disable the view of the row or column sums

Figure 67: Unnecessary columns

50

Using the DataPilot

Figure 68: Disabled column sums
A frequent use case for multiple data fields is the aggregation of one
value according to different aggregate functions at the same time. You
can create a DataPilot that shows you the monthly sales values andshows you additionally the smallest and the largest amounts.

Figure 69: Multiple analyses for the samedata field
Unfortunately, Calc does not allow you to use the same data field
multiple times. For such multiple analyses you have to duplicate therespective column in the source. This column is then available within
the DataPilot; it just happens to have identical values.

DataPilot functions in detail51

Figure 70: Multiple identical fields for sales value, that havebeen created as duplicates within your source.

Shortcuts
If you use the DataPilot very often, you might find the frequent use of
the menu paths (Data > DataPilot > Start and Data > Group andOutline > Group) inconvenient.
For grouping, a shortcut is already defined: F12. For starting the
DataPilot, you can define your own keyboard shortcut. If you prefer tohave toolbar icons instead of keyboard shortcuts, you can create auser-defined symbol and add it to either your own custom made toolbar
or the Standard toolbar.
For an explanation how to create keyboard shortcuts or add icons totoolbars, see Chapter 14 (Setting Up and Customizing Calc).

52

Using the DataPilot

Function GETPIVOTDATA
The function GETPIVOTDATA can be used with formulas in Calc if you
want to reuse the results from the DataPilot elsewhere in yourspreadsheet.

Difficulty
Normally you create a reference to a value by entering the address ofthe cell that contains the value. For example, the formula =C6*2
creates a reference to cell C6 and returns the doubled value.
If this cell is located in the results area of the DataPilot, it contains theresult that was calculated by referencing specific categories of the row
and column fields. In Figure 71, the cell C6 contains the sum of thesales values of the employee Hans in the category Sailing. The formulain the cell C12 uses this value.

Figure 71: Formula reference to a cell of the DataPilot
If the underlying data or the layout of the DataPilot changes, then you
must take into account that the sales value for Hans might appear in adifferent cell. Your formula still references the cell C6 and therefore
uses a wrong value. The correct value is in a different location. For
example, in Figure 72, the location is now C7.

Function GETPIVOTDATA

53

Figure 72: The value that you really want to use canbe found now in a different location.
The function GETPIVOTDATA allows you to have a reference to a value
inside the DataPilot by using the specific identifying categories for thisvalue.

SyntaxThe syntax has two variations:
GETPIVOTDATA(target field, DataPilot; [ Field name / Element; ... ])GETPIVOTDATA(DataPilot; specification)

First syntax variation
The target field to specify which data field of the DataPilot is usedwithin the function. If your DataPilot has only one data field, this entry
is ignored, but you must enter it anyway.
If your DataPilot has more than one data field, then you have to enter
the field name from the underlying data source (for example “sales
value”) or the field name of the data field itself (for example “sum –sales value”).
The argument DataPilot specifies the DataPilot that you want to use.
It is possible that your document contains more than one DataPilot.
Enter here a cell reference that is inside the results area of your
DataPilot. It might be a good idea to always use the upper left cornercell of your DataPilot, so you can be sure that the cell will always be
within your DataPilot even if the layout changes.
Example: GETPIVOTDATA("sales value";A1)

54

Using the DataPilot

If you enter only the first two arguments, then the function returns thetotal result of the DataPilot.
You can add more arguments as pairs with field name and element toretrieve specific partial sums. In the example in Figure 73, where wewant to get the partial sum of Hans for sailing, the formula in cell C12
would look like this:=GETPIVOTDATA("sales value";A1;"employee";"Hans";"category";"sailing")

Figure 73: First syntax variation

Second syntax variation
The argument DataPilot has to be given in the same way as for theother syntax variation.
For the specifications, enter a list separated by spaces to specify thevalue you want from the DataPilot. This list must contain the name ofthe data field, if there is more than one data field, otherwise it is not
required. To select a specific partial result, add more entries in theform of Field name[element].
In the example in Figure 74, where we want to get the partial sum ofHans for Sailing, the formula in cell C12 would look like this:=GETPIVOTDATA(A1;"sales value employee[Hans] category[sailing]")Function GETPIVOTDATA

55

Figure 74: Second syntax variation

56

Using the DataPilot