Function GETPIVOTDATA

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 >

The function GETPIVOTDATA can be used with formulas in Calc if you want to reuse the results from the DataPilot somewhere else in your spreadsheet.

Difficulty

Normally you create a reference to a value by entering the address of the 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 the result that was calculated by referencing specific categories of the row and column fields. For example, the cell C6 contains the sum of the sales values of the employee Hans in the category Sailing. The formula in the cell C12 uses this value.

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 a different 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 the figure below, the location is now C7.

The value that you really want to use can be 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 this value.

Syntax

The syntax has two variations:

GETPIVOTDATA(target field, DataPilot; [ Field name / Element; ... ])

GETPIVOTDATA(DataPilot; specification)

First syntax variation

The target field specifies which data field of the DataPilot is used within 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 always to use the upper left corner cell 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)

If you enter only the first two arguments, then the function returns the total result of the DataPilot.

You can add more arguments as pairs with field name and element to retrieve specific partial sums. In the example below, where we want 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")

First syntax variation

Second syntax variation

The argument DataPilot has to be given in the same way as for the other syntax variation.

For the specifications, enter a list separated by spaces to specify the value you want from the DataPilot. This list must contain the name of the 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 the form of Field name[element].

In the example below, where we want to get the partial sum of Hans for Sailing, the formula in cell C12 would look like this:

=GETPIVOTDATA(A1;"sales value employee[Hans] category[sailing]")

Second syntax variation