PivotGridControl.CustomFieldSort Event

Provides the capability to sort data using custom rules.

Namespace: DevExpress.XtraPivotGrid

Assembly: DevExpress.XtraPivotGrid.v18.2.dll

Declaration

C#

public event PivotGridCustomFieldSortEventHandler CustomFieldSort

VB.NET

Public Event CustomFieldSort As PivotGridCustomFieldSortEventHandler

Event Data

The CustomFieldSort event's data class is PivotGridCustomFieldSortEventArgs. The following properties provide information specific to this event:

Property	Description
Data	For internal use. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
Field	Gets the field whose values are being compared. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
Handled	Gets or sets whether a comparison operation is being handled, so no default processing is required. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
ListSourceRowIndex1	Gets the index in the data source for the first of the two rows being compared. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
ListSourceRowIndex2	Gets the index in the data source for the second of the two rows being compared. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
Result	Gets or sets the result of a custom comparison. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
SortLocation	Gets a target UI element to whose values sorting is applied. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
SortOrder	Gets the sort order applied to the field. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
Value1	Gets the first value being compared. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
Value2	Gets the second value being compared. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.

The event data class exposes the following methods:

Method	Description
GetListSourceColumnValue(Int32, String)	Gets a value from the specified row and column. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
GetSortResult()	For internal use. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.
SetArgs(ICustomSortRowsEventArgs)	Initializes the PivotGridCustomFieldSortEventArgsBase<T> instance with values obtained from the specified object. For internal use. Inherited from PivotGridCustomFieldSortEventArgsBase<T>.

Remarks

The CustomFieldSort event occurs for the field whose PivotGridFieldBase.SortMode property is set to the PivotSortMode.Custom value. Handle the CustomFieldSort event to provide a custom sorting algorithm for the field values.

The CustomFieldSort event fires for pairs of rows in the underlying data source, before data is grouped according to the layout of column and row fields. The field values in these rows are specified by the PivotGridCustomFieldSortEventArgsBase<T>.Value1 and PivotGridCustomFieldSortEventArgsBase<T>.Value2 parameters. Compare these values and assign the result to the PivotGridCustomFieldSortEventArgsBase<T>.Result property as follows:

• Set the Result to -1 if the first row should be positioned above the second row when data is sorted in ascending order. When data is sorted in descending order, the first row will be positioned below the second row.
• Set the Result to 1 if the first row should be positioned below the second row when data is sorted in ascending order. When data is sorted in descending order, the first row will be positioned above the second row.
• Set the Result to 0 to indicate that the rows are equal. In this case, the compared rows will be grouped into one field value.

Note

Note that the sorting is applied before grouping. If the rows are equal (the Result is set to 0), the field values are in the same group.

To get additional data from the underlying data source, use the PivotGridCustomFieldSortEventArgsBase<T>.GetListSourceColumnValue method. Parameters - column name and row index - are available as the PivotGridCustomFieldSortEventArgs class property values.

Set the PivotGridCustomFieldSortEventArgsBase<T>.Handled property to true to finalize the comparison and use the e.Result value. Otherwise, the default comparison is in effect and e.Result is ignored.

Note

The CustomFieldSort event occurs in different situations in PivotDataProcessingEngine.Legacy and PivotDataProcessingEngine.LegacyOptimized modes because of the different workflow - the legacy engine aggregates data after sorting performed at the data source level, while the optimized engine sorts the aggregated data. The legacy engine raises the CustomFieldSort event more often to compare different rows of data. The optimized engine raises the event to compare the resulting data groups.

However, you can still use the CustomFieldDataEventArgsBase<T>.ListSourceRowIndex property and the CustomFieldDataEventArgsBase<T>.GetListSourceColumnValue method. They return the data from the first data row included in the processed group.

The CustomFieldSort event also fires to sort unique filter values in the Filter Drop-Down. In this situation, the e.SortLocation property value is PivotSortLocation.Filter or PivotSortLocation.GroupFilter. A field value in a filter relates to multiple rows in the underlying data source, and you cannot use the ListSourceRowIndex1 and ListSourceRowIndex2 properties to identify underlying rows.

Important

ListSourceRowIndex1 and ListSourceRowIndex2 properties are always -1 when the event fires for the field located in the Filter Drop-Down.

Note

Custom sorting is not supported in OLAP or server mode. To sort data in OLAP and server mode using custom algorithms, handle the PivotGridControl.CustomServerModeSort event.

Example

This example demonstrates how to sort a field (Sales Person) by the hidden data field values (Last Name) in the pivot’s column header (the field’s SortLocation is Pivot ). If a field is located in a filter popup, a custom comparison method is used to to sort the field values.

Note

The complete sample project CustomFieldSort - How to Use a Hidden Field to Sort the Visible Field is available in the DevExpress Examples repository.

C#

using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
using System;
using System.Collections;

namespace CustomFieldSortExample
{
    public partial class Form1 : XtraForm
    {
        public Form1()
        {
            InitializeComponent();
            pivotGridControl1.CustomFieldSort += new PivotGridCustomFieldSortEventHandler(pivotGridControl1_CustomFieldSort);
        }

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

        private void checkEdit1_CheckedChanged(object sender, EventArgs e)
        {
            fieldSalesPerson1.SortMode = ((CheckEdit)sender).Checked ? PivotSortMode.Custom : PivotSortMode.Default;
        }
        void pivotGridControl1_CustomFieldSort(object sender, PivotGridCustomFieldSortEventArgs e)
        {
            if (e.Field.FieldName == "Sales Person")
            {
                if (e.SortLocation == PivotSortLocation.Pivot)
                {
                    object orderValue1 = e.GetListSourceColumnValue(e.ListSourceRowIndex1, "Last Name"),
                        orderValue2 = e.GetListSourceColumnValue(e.ListSourceRowIndex2, "Last Name");
                    e.Result = Comparer.Default.Compare(orderValue1, orderValue2);
                }
                else
                {
                    e.Result = Comparer.Default.Compare(e.Value1.ToString().Split(' ')[1], e.Value2.ToString().Split(' ')[1]);
                }
                e.Handled = true;
            }
        }
    }
}

VB.NET

Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid
Imports System
Imports System.Collections

Namespace CustomFieldSortExample
  Partial Public Class Form1
    Inherits XtraForm

    Public Sub New()
      InitializeComponent()
      AddHandler pivotGridControl1.CustomFieldSort, AddressOf pivotGridControl1_CustomFieldSort
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
      excelDataSource1.FileName = "SalesPerson.xlsx"
      excelDataSource1.Fill()
      pivotGridControl1.BestFit()
    End Sub

    Private Sub checkEdit1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles checkEdit1.CheckedChanged
      fieldSalesPerson1.SortMode = If(DirectCast(sender, CheckEdit).Checked, PivotSortMode.Custom, PivotSortMode.Default)
    End Sub
    Private Sub pivotGridControl1_CustomFieldSort(ByVal sender As Object, ByVal e As PivotGridCustomFieldSortEventArgs)
      If e.Field.FieldName = "Sales Person" Then
        If e.SortLocation = PivotSortLocation.Pivot Then
          Dim orderValue1 As Object = e.GetListSourceColumnValue(e.ListSourceRowIndex1, "Last Name"), orderValue2 As Object = e.GetListSourceColumnValue(e.ListSourceRowIndex2, "Last Name")
          e.Result = Comparer.Default.Compare(orderValue1, orderValue2)
        Else
          e.Result = Comparer.Default.Compare(e.Value1.ToString().Split(" "c)(1), e.Value2.ToString().Split(" "c)(1))
        End If
        e.Handled = True
      End If
    End Sub
  End Class
End Namespace

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the CustomFieldSort event.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-reorder-pivotgrid-rows-and-columns-by-drag-and-drop-e4545/CS/ReorderHelper.cs#L61

targetPivot = pivot;
targetPivot.CustomFieldSort += targetPivot_CustomFieldSort;
targetPivot.MouseMove += targetPivot_MouseMove;

customfieldsort-how-to-sort-field-values-by-invisible-data-column-values-e331/CS/CustomFieldSortExample/Form1.cs#L13

    InitializeComponent();
    pivotGridControl1.CustomFieldSort += new PivotGridCustomFieldSortEventHandler(pivotGridControl1_CustomFieldSort);
}

VB.NET

how-to-reorder-pivotgrid-rows-and-columns-by-drag-and-drop-e4545/VB/ReorderHelper.vb#L83

targetPivot_Renamed = pivot
AddHandler targetPivot_Renamed.CustomFieldSort, AddressOf targetPivot_CustomFieldSort
AddHandler targetPivot_Renamed.MouseMove, AddressOf targetPivot_MouseMove

customfieldsort-how-to-sort-field-values-by-invisible-data-column-values-e331/VB/CustomFieldSortExample/Form1.vb#L12

    InitializeComponent()
    AddHandler pivotGridControl1.CustomFieldSort, AddressOf pivotGridControl1_CustomFieldSort
End Sub