PivotTable Field Settings Dialog
- 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
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.
Check the Display labels from the next field in the same column (compact form) box to compress the current and next row fields, as shown on the image below.
Checking the Display subtotals at the top of each group box moves subtotals to the top of each item in the row field if it’s shown in compact or outline form.
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.