Sorting by Summary

Dec 01, 2022
7 minutes to read

Sorting by Summary allows you to sort the current column or row field’s values by corresponding summary values.

The following image illustrates the Pivot Grid Control with Country field values sorted by the January | Price column:

Sorting by Summary in the UI

Users can sort by summary through the context menu by right-clicking an innermost column or row header.

The image in the row’s/column’s header indicates if the field values are sorted by this row/column.

Sorting by Summary is available for all fields if they are displayed within the Column Header Area or Row Header Area. You can disable sorting by summary for users in the UI with the following properties:

Property	Description	Note
PivotGridOptionsCustomization.AllowSortBySummary	Gets or sets whether end-users can sort row field values by column values, and column field values by row values.	This property affects to all fields.
PivotGridFieldOptions.AllowSortBySummary	Gets or sets whether end-users can sort the current row/column field values by other column/row summary values.	This property affects individual fields.

Specified PivotGridFieldOptions.AllowSortBySummary property takes priority over the PivotGridOptionsCustomization.AllowSortBySummary property.

Sorting by Summary in Code

Use the field’s PivotGridFieldBase.SortBySummaryInfo property to get access to the settings that are used to sort the values of the current column field or row field by corresponding summary values.

Tip

Demo: Code Examples - Sorting - Sorting By Summary module in the XtraPivotGrid MainDemo

Requires installation of WinForms Subscription. Download.

To sort data by summaries, you need to specify a data field whose summary values should define the sort order. Do one of the following:

Assign the data field to the PivotGridFieldSortBySummaryInfo.Field property.
Assign the data field name to the PivotGridFieldSortBySummaryInfo.FieldName property.

After you specified a data field, Pivot Grid sorts field values by a Grand Total column/row that corresponds to this data field.

The image below shows the Pivot Grid sorted by the Extended Price‘s Grand Total values:

To sort field values by other column/row or their totals, adding sort conditions identify the field (the PivotGridFieldSortBySummaryInfo.Conditions property). Each condition is a PivotGridFieldSortCondition object that identifies a field value so that the whole collection identifies a column/row.

Create a sort condition to sort field values by the 2016 Total column total.

C#
VB.NET

csharp

fieldProductName.SortBySummaryInfo.Field = fieldExtendedPrice;
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldYear, 2016));

fieldProductName.SortBySummaryInfo.Field = fieldExtendedPrice
fieldProductName.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldYear, 2016))

The image below demonstrates the result:

Create additional conditions to sort data by the specified column/row:

C#
VB.NET

csharp

fieldProductName.SortBySummaryInfo.Field = fieldPrice;
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldYear, 2016));
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldQuarter, 2));
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldMonth, 5));

fieldProductName.SortBySummaryInfo.Field = fieldPrice
fieldProductName.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldYear, 2016))
fieldProductName.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldQuarter, 2))
fieldProductName.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldMonth, 5))

The highlighted column on the image below is identified by three sort conditions:

To sort data by a custom total column/row, specify its type in the PivotGridFieldSortBySummaryInfo.CustomTotalSummaryType property.

Use the PivotGridFieldBase.SortOrder property to specify whether to sort values in ascending or descending order.

Sort Data in OLAP

In OLAP mode, create sort conditions in the PivotGridFieldSortCondition constructor overload that takes a unique OLAP member name as a parameter.

To obtain an OLAP member for a field value, use the PivotGridControl.GetFieldValueOLAPMember method. This method returns an object that implements the IOLAPMember interface. To access the unique OLAP member name, use its IOLAPMember.UniqueName property.

Limitations

The following Pivot Grid features are not supported or ignored when you sort data by summary:

Running totals is not supported.
The custom summary representation (PivotGridFieldBase.SummaryDisplayType) is ignored.
The custom values obtained in the PivotGridControl.CustomCellValue and PivotGridControl.CustomCellDisplayText events are ignored.
You cannot use the CalculationBindingBase descendants to apply summary filter or sorting by summary when Pivot Grid operates with Optimized calculation engine.

Example: How to Use a Hidden Field to Sort the Visible Field

This example demonstrates how to sort the Sales Person field by the hidden Last Name data field. Check the “Enable custom sorting…” box to sort the Sales Person values by Last Name instead of the default alphabetical sort order.

View Example

Form1.cs
Form1.vb

using DevExpress.Data.PivotGrid;
using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
using System;

namespace CustomSortingExample {
    public partial class Form1 : XtraForm {
        public Form1() {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e) {
            excelDataSource1.FileName = "SalesPerson.xlsx";
            excelDataSource1.Fill();
            pivotGridControl1.BestFit();

            // Create a hidden field to apply custom sorting
            fieldLastName.Area = PivotArea.DataArea;
            fieldLastName.Visible = false;
            fieldLastName.SummaryType = PivotSummaryType.Max;
        }

        private void checkEdit1_CheckedChanged(object sender, EventArgs e) {
            fieldSalesPerson.SortBySummaryInfo.Field = ((CheckEdit)sender).Checked ? fieldLastName : null;
        }
    }
}

Imports DevExpress.Data.PivotGrid
Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid
Imports System

Namespace CustomSortingExample

    Public Partial Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
            excelDataSource1.FileName = "SalesPerson.xlsx"
            excelDataSource1.Fill()
            pivotGridControl1.BestFit()
            ' Create a hidden field to apply custom sorting
            fieldLastName.Area = PivotArea.DataArea
            fieldLastName.Visible = False
            fieldLastName.SummaryType = PivotSummaryType.Max
        End Sub

        Private Sub checkEdit1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
            fieldSalesPerson.SortBySummaryInfo.Field = If(CType(sender, CheckEdit).Checked, fieldLastName, Nothing)
        End Sub
    End Class
End Namespace

Example: Implement Sorting by Summary in OLAP Mode

The following example demonstrates how to implement sorting by summary in OLAP mode. In this example, values of the Fiscal Year field are sorted by the Australia | Bendigo column summary values.

Two PivotGridFieldSortCondition objects contain OLAP members that correspond to Australia and Bendigo values. The PivotGridControl.GetFieldValueOLAPMember method obtains these values. Obtained values specify columns by which the Fiscal Year field should be sorted and are stored in the Fiscal Year’s PivotGridFieldSortBySummaryInfo.Conditions collection. OLAP members can be obtained only for visible field values. For this reason, the Australia field value is expanded before initializing OLAP members to obtain the Bendigo member. The PivotGridFieldSortBySummaryInfo.Field property specifies the data field whose summary values should be used to sort values of the Fiscal Year field.

This sample uses the Adventure Works 2008 cube.

View Example

Form1.cs
Form1.vb

using System;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;

namespace XtraPivotGrid_OLAPSortBySummary {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e) {
            // Expands the Australia column to be able to retrieve OLAP members 
            // that correspond to the nested columns.
            pivotGridControl1.ExpandValue(true, new object[] { "Australia" });
            // Obtains OLAP members corresponding to the Australia and Bendigo values.
            IOLAPMember countryMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCountry, 0);
            IOLAPMember cityMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCity, 0);
            // Exits if the OLAP members were not obtained successfully.
            if (countryMember == null || cityMember == null)
                return;
            // Locks the pivot grid from updating while the Sort by Summary
            // settings are being customized.
            pivotGridControl1.BeginUpdate();
            try {
                // Specifies a data field whose summary values should be used to sort values
                // of the Fiscal Year field.
                fieldFiscalYear.SortBySummaryInfo.Field = fieldInternetSalesAmount;
                // Specifies a column by which the Fiscal Year field values should be sorted.
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(
                    new PivotGridFieldSortCondition(fieldCountry, "Australia", countryMember.UniqueName));
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(
                    new PivotGridFieldSortCondition(fieldCity, "Bendigo", cityMember.UniqueName));
            }
            finally {
                // Unlocks the pivot grid and applies changes.
                pivotGridControl1.EndUpdate();
            }
        }
    }
}

Imports System
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid

Namespace XtraPivotGrid_OLAPSortBySummary

    Public Partial Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
            ' Expands the Australia column to be able to retrieve OLAP members 
            ' that correspond to the nested columns.
            pivotGridControl1.ExpandValue(True, New Object() {"Australia"})
            ' Obtains OLAP members corresponding to the Australia and Bendigo values.
            Dim countryMember As IOLAPMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCountry, 0)
            Dim cityMember As IOLAPMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCity, 0)
            ' Exits if the OLAP members were not obtained successfully.
            If countryMember Is Nothing OrElse cityMember Is Nothing Then Return
            ' Locks the pivot grid from updating while the Sort by Summary
            ' settings are being customized.
            pivotGridControl1.BeginUpdate()
            Try
                ' Specifies a data field whose summary values should be used to sort values
                ' of the Fiscal Year field.
                fieldFiscalYear.SortBySummaryInfo.Field = fieldInternetSalesAmount
                ' Specifies a column by which the Fiscal Year field values should be sorted.
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldCountry, "Australia", countryMember.UniqueName))
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldCity, "Bendigo", cityMember.UniqueName))
            Finally
                ' Unlocks the pivot grid and applies changes.
                pivotGridControl1.EndUpdate()
            End Try
        End Sub
    End Class
End Namespace