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
   [Collapse]Pivot Table Overview
     Pivot Table Structure
     Pivot Table API
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    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

Pivot Table Overview

This document introduces the Pivot Table functionality that allows you to perform complex analysis of raw data in a worksheet using pivot tables.

A pivot table represents a summary table used to explore, analyze and aggregate huge amounts of data in a worksheet. It helps break your data into categories and subcategories, and automatically calculates subtotals and grand totals using the most suitable summary function from a predefined list.

Pivot tables in the SpreadsheetControl are interactive. That means you can easily change the layout of the existing report to create a new summary table that will fully meet your analysis requirements. For example, the image gallery below shows three PivotTable reports that allow you to analyze the same business data in different forms.

Expanded Pivot Table Functionality

The SpreadsheetControl provides a comprehensive and straightforward Pivot Table API that enables you to create and modify pivot tables in code.

In particular, you can perform the following actions.

  • Create a pivot table using a cell range as a data source or base your report on the data cache of the existing pivot table.
  • Important

    Currently, the SpreadsheetControl uses only worksheet data as a data source for a pivot table. External data sources (such as ODC files, OLAP cubes, relational databases, XML files, etc.) are not supported.

  • Organize the structure of a pivot table by adding and arranging its fields.
  • Calculate summaries against data fields using a wide range of preset aggregate functions.
  • Change source data for a pivot table.
  • Refresh a pivot table to obtain the latest data from the source.
  • Move a pivot table to a new location.
  • Display a pivot table in a compact, outline, or tabular form.
  • Format a pivot table by applying a built-in or custom style.
  • Display or hide subtotals and grand totals for rows and columns.
  • Insert a blank row after each group of items.
  • Sort field items in descending or ascending order.
  • Apply a filter to a pivot table to show or hide specific items, construct the filter expression to display only items that meet the specified condition, or filter report data based on calculated values.
  • Create calculated fields and items.
  • Group items in a PivotTable report in a custom way to create new subsets of data.

For details, refer to the Pivot Tables section of examples.

Expanded Manage Pivot Tables in the User Interface

  • Insert a Pivot Table

    End-users can create a pivot table using the Create PivotTable dialog, which can be invoked by clicking the PivotTable button on the Insert tab, in the Tables group.

  • Organize the PivotTable Structure

    End-users can use the Field List pane to add fields to the pivot table and arrange them as required using drag and drop.

  • Adjust the PivotTable Options

    After end-users create a pivot table and lay out its fields, they can fine-tune their report using various commands located on the PivotTable Tools contextual tab, which appears when any cell in the pivot table is selected.

    Options on the Analyze tab allow end-users to change the underlying settings of a pivot table. They can specify various options affecting the table layout, display and print settings, select the aggregate function used to summarize values in the data field, change the data source for the report, refresh the pivot table, change its location, display or hide the Field List, etc.

    The Design tab of the PivotTable Tools contextual tab provides a wide range of formatting options enabling end-users to change the pivot table appearance. In particular, end-users can adjust the report layout by displaying the pivot table in compact, outline, or tabular form, show or hide subtotals and grand totals, apply a style to the pivot table and configure style options.

  • Explore the PivotTable Data

    After a pivot table is completely organized and adjusted, end-users can easily explore the resulting report: sort field items, expand or collapse individual categories of data, and apply a filter to the pivot table to display only significant values.

Expanded Online Video

To learn the basics of working with pivot tables in the SpreadsheetControl, watch the following introductory video.

Expanded See Also

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