<< Click to Display Table of Contents >> Field settings (Row/Column labels area)

If you open the dropdown menu to the right of a field in the Row labels or Column labels areas in the lower areas section of the field list, you will find the option Field settings. Alternatively, you can select this option in the context menu by right-clicking directly in the Pivot table report (select a cell of the desired field below the header Row labels/Column labels).

This section describes the adjustments you can make with the field settings.

This is illustrated by the familiar sample data shown in the figure below: The Amount field is dragged into the Values area of the areas section, and the Employee and Article fields are dragged into the Row labels area.

The pivot table report with default values for field settings. Highlighted in blue: selecting the field settings for the "Employee" field

Note: For the purposes of simplicity, the descriptions in this section refer to the arrangement of the fields in the Row labels area. If you drag the fields to the Column labels area instead, the results will be basically the same. However, they will be displayed transposed (that is, from left to right instead of from top to bottom).

Field settings, "Subtotals" tab

If you have arranged several fields in a row labels or column labels area, you will obtain partial results for the higher-level field element (Employee) in the pivot table report, as shown in the figure above (for Anna, Maria, Toni). These subtotals will be automatically calculated as sums if a number field has been identified in the values area. In the Subtotals tab, you can also change the display of the subtotals to other evaluation functions (for example, count, average, etc., instead of sums) or hide the display of subtotals completely.

Important: The field settings affect only the selected field of the row/column labels area in the pivot table.

Click on the dropdown menu to the right of the field (Employee) in the Row labels area (areas section of the field list), and select the Field settings. In the open dialog box, select the Subtotals tab.

Here, you have the following options:

▪Auto (default): Sums are displayed for the subtotals (Employee, as in the figure above). If the value field (Amount) contains values with other formats rather than numbers, the Count function is automatically used for the subtotals.

▪None: The subtotals (Employee) are not displayed.

▪Custom

Tip 1: You can also combine several custom functions. Each function is then displayed as a separate row for the subtotal.

Tip 2: If you want to change both the subtotals and also the other values to a certain display, you can make the required changes in the Values area with the option Value settings. For more information, see Value settings (Values area).

Tip 3: If you want to show or hide the grand total for columns/rows, you will find the options available under Pivot table settings.

Field settings, "Form" tab

In the example figure at the beginning of the section, you can see the pivot table report displayed in the outline view. You can use the Form options to customize this view.

Note: The Form can only be customized for fields in the Row labels area. For fields in the Column labels area, the outline forms cannot be set.

In the Row labels area (in the areas section of the field list), click on the dropdown menu to the right of the Employee field and select Field settings. In the open dialog box, select the Form tab.

The Field settings dialog box with the default values in the "Form" tab

Form section

Here, you can select between the view in Outline form or in Tabular form:

▪In the Outline form – with Compact form also enabled – several fields are displayed together in one column, and the items of the lower-level element are indented in the column. Here, you can also select whether the subtotals should appear above or below the grouping.

▪The Tabular form always displays a separate column for each field, with the corresponding field heading:

Further options in the "Form" tab

There are three other options in the lower section of the Form tab that you can use to give the pivot table report a more structured look as needed.

Please note: The options Repeat item labels and Insert blank lines after subtotals only affect higher-level field elements (Employee). The option Show items without data, however, only affects lower-level field elements (Article).

▪Repeat item labels

▪Insert blank lines after subtotals

▪Show items without data

Field settings, "Field" tab

You can assign a Custom name to the field here so that it appears in the pivot table with a custom name. Enable the checkbox and type the new name into the input field. For more information, see also Working with the field list: Areas section.