PivotTable Field Settings Dialog

• May 14, 2021
• 2 minutes to read

The Field Settings dialog allows end-users to rename a PivotTable row or column field and adjust its subtotal and layout settings.

End-users can invoke this dialog by doing the following:

• Select a row or column field;

• Right-click it and select the Field Settings… item from the context menu…

• … or click the Field Settings button on the ribbon. Add the Active Field ribbon group to enable this button (check the Create a Simple Spreadsheet Application topic for details on how to provide a Ribbon UI for the SpreadsheetControl).

The Field Settings dialog includes the following tabs.

• Subtotals & Filters
• Layout & Print

Subtotals & Filters

Options under this tab enable end-users to subtotal row or column fields.

End-users can select one of the following subtotal options:

• Automatic (PivotField.SetSubtotalAutomatic) - to calculate the subtotals using the default summary function;
• Custom (PivotField.SetSubtotal) - to use one or multiple custom functions (PivotSubtotalFunctions) for subtotal calculation. The custom function(s) cannot be used if the target row or column field contains calculated items;
• None (PivotSubtotalFunctions.None) - to remove subtotals.

Checking the Include new items in the manual filter box (PivotField.IncludeNewItemsInFilter) enables adding the new PivotTable items to a manual filter.

Layout & Print

Options under this tab allows end-users to change the layout form for an outer row field.

• Show item labels in the outline form (PivotFieldLayout.Outline is true ) - outlines row data.

• Show items in tabular form (PivotFieldLayout.Outline is false ) - shows the row field in table format.

The Layout & Print tab also allows end-users to specify whether to repeat item labels for an outer field (PivotFieldLayout.RepeatItemLabels), insert a blank line after each field item (PivotFieldLayout.InsertBlankRowAfterEachItem), or show items with no data (PivotFieldLayout.ShowItemsWithNoData).

Tip

Call the PivotField.SetSubtotal method to set subtotals in code. Use properties of the PivotFieldLayout object to change the layout of a specific field in the report. Refer to the How to: Subtotal Fields in a Pivot Table or How to: Change the PivotTable Layout topic for details.