Back to Devexpress

Totals

aspnet-7307-components-pivot-grid-data-shaping-aggregation-totals.md

latest11.0 KB
Original Source

Totals

  • Dec 17, 2020
  • 4 minutes to read

The Pivot Grid calculates summary values ( totals ) and displays them as additional columns or rows. The following totals are supported:

Automatic Totals

  • The automatic totals can be divided into two types:

  • Row/column totals display sub-totals calculated for outer row/column fields.

  • Row/column grand totals display summary totals calculated against all the rows/columns.

The automatic totals are calculated using a summary function the corresponding data field specifies.

Tip

Demo: Automatic Totals

The table below lists common members that configure automatic totals:

MemberDescription
PivotGridFieldOptions.ShowTotalsGets or sets whether Totals that correspond to the current data field are visible.
PivotGridOptionsViewBase.ShowRowTotalsGets or sets whether to display row automatic Totals.
PivotGridOptionsViewBase.ShowColumnTotalsGets or sets whether to display column automatic Totals.
PivotGridOptionsViewBase.ColumnTotalsLocationGets or sets the column totals’ location.
PivotGridOptionsViewBase.RowTotalsLocationGets or sets the totals’ and grand totals’ location.
PivotGridOptionsViewBase.ShowColumnGrandTotalsGets or sets whether to display column Grand Totals.
PivotGridOptionsViewBase.ShowRowGrandTotalsGets or sets whether to display row Grand Totals.
PivotGridOptionsViewBase.ShowTotalsForSingleValuesGets or sets whether automatic totals are displayed for the field values which contain a single nesting field value.
PivotGridOptionsViewBase.ShowCustomTotalsForSingleValuesGets or sets whether custom totals are displayed for the field values which contain a single nesting field value.
PivotGridOptionsViewBase.ShowGrandTotalsForSingleValuesGets or sets whether grand totals are displayed when the control lists a single value of an outer column field or row field along its left or top edge.

Note

See the PivotGridOptionsViewBase class members to get a full list of members you can use to configure totals.

Use custom totals to calculate totals using a different summary function, or to calculate multiple subtotals.

Custom Totals

You may need to manually specify how many and what type of totals to display for each field. The custom totals replace automatic totals and can be calculated using various aggregation functions like Sum, Min, Max, Average, etc. The number of totals depends on how many items you add to the field’s custom total collection.

For example, the image below shows the Category field displaying Sum and Max totals:

The code below creates the layout as in the image above:

csharp
using DevExpress.Xpf.PivotGrid;

if (!IsPostBack && !IsCallback) {
   // Gets a reference to the CategoryName field. 
   PivotGridField field = ASPxPivotGrid1.Fields["CategoryName"];
   ASPxPivotGrid1.BeginUpdate();
   try {
      // Clears the custom total collection. 
      field.CustomTotals.Clear();
      // Adds items to the custom total collection with  
      // the specified summary types. 
      field.CustomTotals.Add(PivotSummaryType.Sum);
      field.CustomTotals.Add(PivotSummaryType.Max);
      // Makes the custom totals visible for this field. 
      field.TotalsVisibility = PivotTotalsVisibility.CustomTotals; 
   }
   finally {
      ASPxPivotGrid1.EndUpdate();
   }
}
vb
Imports using DevExpress.Xpf.PivotGrid

If (Not IsPostBack) AndAlso (Not IsCallback) Then
   ' Gets a reference to the CategoryName field. 
   Dim field As PivotGridField = ASPxPivotGrid1.Fields("CategoryName")
   ASPxPivotGrid1.BeginUpdate()
   Try 
      ' Clears the custom total collection. 
      field.CustomTotals.Clear()
      ' Adds items to the custom total collection with  
      ' the specified summary types. 
      field.CustomTotals.Add(PivotSummaryType.Sum)
      field.CustomTotals.Add(PivotSummaryType.Max)
      ' Makes the custom totals visible for this field. 
      field.TotalsVisibility = PivotTotalsVisibility.CustomTotals
   Finally 
      ASPxPivotGrid1.EndUpdate()
   End Try 
End If

You can set the same layout in markup as well:

aspx
<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" DataSourceID="SqlDataSource1">
    <Fields>
        <dx:PivotGridField ID="fieldCountry" Area="ColumnArea" AreaIndex="0" FieldName="Country" Name="fieldCountry">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="1" Caption="Product" FieldName="ProductName" Name="fieldProductName">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0" Caption="Category" FieldName="CategoryName" 
            Name="fieldCategoryName" TotalsVisibility="CustomTotals">
            <CustomTotals>
                <dx:PivotGridCustomTotal SummaryType="Sum" />
                <dx:PivotGridCustomTotal SummaryType="Max" />
            </CustomTotals>
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldExtendedPrice" Area="DataArea" AreaIndex="0" Caption="Total Sum" FieldName="Extended_Price" Name="fieldExtendedPrice">
        </dx:PivotGridField>
    </Fields>
</dx:ASPxPivotGrid>

The following table lists the members you can use to configure custom totals:

MemberDescription
PivotGridFieldBase.TotalsVisibilityGets or sets whether to display totals for the current field when it is in the Column Header Area or Row Header Area and if so, whether they are automatic or custom.
PivotGridField.CustomTotalsGets the current field’s custom total collection.
PivotGridFieldBase.SummaryTypeGets or sets the type of the summary function which is calculated against the current data field.

Running Totals

Running totals allow you to calculate cumulative values that correspond to the specified column or row fields. For instance, in the ASPxPivotGrid below, running totals are enabled for the Quarter field:

In the result, the PivotGrid control in the image below displays cumulative sales for each quarter over a two-year period:

Note that cumulative values depend on the values’ order. End-users can change the order by sorting, grouping or filtering.

You can specify whether running totals are calculated independently within individual groups, or for the entire PivotGrid. In the image below, running totals for the Quarter field are calculated independently for each year (cross-group variation is disabled):

The table below lists the members you can use to configure running totals:

MemberDescription
PivotGridFieldBase.RunningTotalGets or sets whether Running totals are calculated for values in the Data Area that correspond to the current column or row field.
PivotGridOptionsData.AllowCrossGroupVariationGets or sets whether summary variations and running totals are calculated independently within individual groups, or throughout the Pivot Grid.