How to: Sort Data by Hidden Data Field Values

• Nov 27, 2019
• 2 minutes to read

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