Pivot Table Overview

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.

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

  

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