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…
…or by clicking the PivotTable Options button on the ribbon. Add the PivotTable group to enable this button (refer to the Create a Simple Spreadsheet Application topic for details on how to provide a Ribbon UI for the SpreadsheetControl).
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.
The following API allows you to specify the Layout & Format options in code:
API | Description |
|---|---|
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. | |
Gets or sets the indent increment for items from different row fields when a pivot table is shown in compact form. | |
Gets or sets the order in which multiple page fields are displayed in the PivotTable report filter area. | |
Gets or sets the number of page fields to display before starting another column or row based on the PivotLayout.PageOrder property value. | |
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. | |
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. | |
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.
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.
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.
Note
The Printing tab’s options have no effect on the SpreadsheetControl’s printing process. However, these options are saved in a file in supported formats, 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.
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.
