PivotTable Options Dialog

  • 4 minutes to read

The Pivot Table Options dialog allows end-users to rename the Pivot Table and adjust its layout, filter, display, printing and data settings.

NOTE

The SpreadsheetControl does not support external data sources (ODC files, OLAP cubes, relational databases, XML files, etc.). Therefore all related options are unavailable in this dialog.

End-users can invoke this dialog by selecting the PivotTable Options... item in the context menu...

XtraSpreadsheet_PivotTableOptionsDialog_ContextMenu

...or by clicking the PivotTable Options button on the ribbon. Add the PivotTable ribbon group to enable this button (refer to the Getting Started topic for details on how to provide a Ribbon UI for the SpreadsheetControl).

XtraSpreadsheet_PivotTableOptionsDialog_Ribbon

This dialog includes the following tabs:

Layout & Format

Options under this tab allow end-users to specify additional layout options for a PivotTable report: merge and center cells containing labels, set the indent for row labels, rearrange page fields and autofit column widths when updating the pivot table.

The For error values show: and For empty values show: editors allow end-users to enter the text for cells with errors and empty values.

XtraSpreadsheet_PivotTableOptionsDialog_LayoutAndFormat

The following API allows you to specify the Layout & Format options in code:

API

Description

PivotLayout.MergeTitles

Gets or sets a value indicating whether to merge and center cells containing item labels for the outer row and column fields, subtotal and grand total captions.

PivotLayout.IndentInCompactForm

Gets or sets the indent increment for items from different row fields when a pivot table is shown in compact form.

PivotLayout.PageOrder

Gets or sets the order in which multiple page fields are displayed in the PivotTable report filter area.

PivotLayout.PageWrap

Gets or sets the number of page fields to display before starting another column or row based on the PivotLayout.PageOrder property value.

PivotViewOptions.ShowError

PivotViewOptions.ErrorCaption

Gets or sets a value indicating whether to show custom error messages in cells.

Gets or sets the text to be displayed in cells that contain errors.

PivotViewOptions.ShowMissing

PivotViewOptions.MissingCaption

Gets or sets a value indicating whether to display a custom string in cells that contain no values.

Gets or sets the text to be displayed in cells with no values.

PivotBehaviorOptions.AutoFitColumns

Gets or sets a value indicating whether column widths should be automatically resized when the pivot table is recalculated or refreshed.

Totals & Filters

This tab contains options used to specify whether to display grand totals for rows or columns, or to apply multiple filters to a single field in the row or column area.

XtraSpreadsheet_PivotTableOptionsDialog_TotalsAndFilters

Use the following members to set grand total and filter options in code:

API Description
PivotLayout.ShowRowGrandTotals Gets or sets a value indicating whether grand totals should be displayed for rows in the PivotTable report.
PivotLayout.ShowColumnGrandTotals Gets or sets a value indicating whether grand totals should be displayed for columns in the PivotTable report.
PivotBehaviorOptions.AllowMultipleFieldFilters Gets or sets a value indicating whether fields in the pivot table can have multiple filters applied to them at the same time.

Display

Options under this tab allow end-users to show or hide certain report elements, such as expand/collapse buttons, field headers with filter arrows, and the Values row.

XtraSpreadsheet_PivotTableOptionsDialog_Display

Use the following members to set display options for a pivot table in code:

API Description
PivotViewOptions.ShowDrillIndicators Gets or sets a value indicating whether the expand/collapse buttons should be displayed in a pivot table.
PivotViewOptions.ShowFieldHeaders Gets or sets a value indicating whether to display the row and column field captions and filter drop-down arrows in a pivot table.
PivotViewOptions.ShowValuesRow Gets or sets a value indicating whether to display the Values row that may appear when there are multiple fields in the PivotTable data area.

Printing

End-users can use this tab to specify printing options: print the expand/collapse buttons, repeat row labels on every page or enable print titles.

XtraSpreadsheet_PivotTableOptionsDialog_Printing

NOTE

The Printing tab's options have no effect on the SpreadsheetControl's printing process. However, these options are saved in a supported file format so that you can print the document from Microsoft® Excel®.

Data

Under this tab, end-users can select or clear the Save source data with file check box to specify whether to store the PivotTable cache with the document. Other Data tab options are written to a file in supported formats, but have no effect on a pivot table loaded in the SpreadsheetControl.

XtraSpreadsheet_PivotTableOptionsDialog_Data

Alt Text

This tab allows end-users to specify an alternative title (PivotViewOptions.AltTextTitle) and description (PivotViewOptions.AltTextDescription) for a pivot table. This text can help people with vision or cognitive impairments to understand the PivotTable report.

XtraSpreadsheet_PivotTableOptionsDialog_AltText

See Also