[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Shapes
    Printing
    Protection
    Find and Replace
   [Expand]Mail Merge
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheet
    [Expand]Rows and Columns
    [Expand]Cells
    [Expand]Formulas
    [Expand]Data Binding
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Tables
    [Collapse]Pivot Tables
      How to: Create a Pivot Table
      How to: Refresh a Pivot Table
      How to: Change a Data Source for a Pivot Table
      How to: Move a Pivot Table
      How to: Clear or Remove a Pivot Table
      How to: Change the PivotTable Layout
      How to: Subtotal Fields in a Pivot Table
      How to: Display or Hide Grand Totals for a Pivot Table
      How to: Apply a Predefined Style to a Pivot Table
      How to: Apply a Custom Style to a Pivot Table
      How to: Control Style Options
      How to: Change the Summary Function for a Data Field
      How to: Apply a Custom Calculation to a Data Field
      How to: Create a Calculated Field
      How to: Create a Calculated Item
      How to: Sort Items in a Pivot Table
      How to: Filter Items in a Pivot Table
      How to: Group Items in a Pivot Table
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Mail Merge
    [Expand]Cell Templates
    [Expand]Commands
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Diagram Control
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

How to: Subtotal Fields in a Pivot Table

This topic describes how to manage subtotals in a pivot table. Subtotals automatically appear for outer fields when you add multiple row and/or column fields to your report.

Select the task you wish to perform.

Expanded Show or Hide Subtotals For a Pivot Table

To show or hide subtotals in a PivotTable report, use the following methods of the PivotLayout object accessible using the PivotTable.Layout property.

Method

Description

PivotLayout.ShowAllSubtotals Displays all subtotals in a pivot table.
The method's topOfGroup parameter specifies the subtotal location for the outer row fields in compact or outline form.
PivotLayout.HideAllSubtotals Hides all subtotals in a pivot table.

The following code example displays all subtotals in a pivot table. Subtotals for the "Category" row field are shown at the bottom of each item in the field.

The image below shows the resulting report.

Expanded Modify Subtotals for a PivotTable Field

The table below describes properties and methods you can use to specify and adjust subtotals for a specific row or column field in a report.

Member

Description

PivotField.SetSubtotalAutomatic Displays automatic subtotals for a given field.
PivotField.SetSubtotal Allows you to change the default summary calculation or to show multiple subtotals for a given field.
To remove subtotals for a field, pass the PivotSubtotalFunctions.None value to the method as a parameter.
PivotSubtotalFunctions Specifies summary functions used to calculate subtotals for a PivotTable field.
PivotFieldLayout.SubtotalOnTop Specifies the subtotal location for an outer row field shown in outline or compact form.
You can display subtotals at the top or bottom of the field's items.
PivotField.SubtotalCaption Specifies the text to be displayed in the field's subtotal row or column heading.

The following example demonstrates how to use multiple functions to subtotal the "Category" row field. To do this, combine the required PivotSubtotalFunctions enumeration values with a bitwise OR operator.

Note

You cannot change the subtotal function for a field containing a calculated item. In this case, a System.InvalidOperationException exception will be thrown.

The image below shows the resulting report.

Is this topic helpful?​​​​​​​