Syntax

The syntax has two variations:

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

GETPIVOTDATA(DataPilot; specification)

First syntax variation

Giving the target field is necessary to specify 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 have to 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 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 in Figure 65, 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")

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 in Figure 65, 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]")