Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Collapse]Visual Elements
     Workbook
     Worksheet
    [Expand]Cells and Cell Ranges
     Rows and Columns
     Formulas
     Name Box
     Pictures
    [Collapse]Dialogs
      Format Cells Dialog
      Paste Special Dialog
      Edit Hyperlink Dialog
      Symbol Dialog
      Create Table Dialog
      Change Chart Type Dialog
      Find and Replace Dialog
      Encrypt Document Dialog
      Password Dialog
      Protect Workbook Dialog
      Protect Sheet Dialog
      User Permissions Dialog
      Insert Function Dialog
      Function Arguments Dialog
      Name Manager
      New Name Dialog
      Create Names from Selection Dialog
      Subtotal Dialog
      Data Validation Dialog
      Properties Dialog
      Page Setup Dialog
      Header and Footer Dialog
      Create PivotTable Dialog
      Move PivotTable Dialog
      PivotTable Field List
      PivotTable Field Settings Dialog
      PivotTable Value Field Settings Dialog
      PivotTable Sort Dialog
      Insert Calculated Field Dialog
      Insert Calculated Item Dialog
      PivotTable Options Dialog
      PivotTable Solve Order Dialog
      New Formatting Rule Dialog
      Conditional Formatting Rules Manager
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

PivotTable Options Dialog

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 ribbon group to enable this button (refer to the Getting Started topic for details on how to provide a Ribbon UI for the SpreadsheetControl).

This dialog includes the following tabs:

Expanded 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
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 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.

Expanded 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.

Expanded 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.

Expanded 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 supported file format so that you can print the document from Microsoft® Excel®.

Expanded 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.

Expanded 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.

Expanded See Also

How would you rate this topic?​​​​​​​