windowsforms-1952-controls-and-libraries-pivot-grid-data-shaping-sorting-sorting-data.md
The PivotGridControl‘s data (and nested values in groups) are sorted by fields in the Column Header Area and Row Header Area. The default sort order is ascending.
To change a field’s sort order, specify the PivotGridFieldBase.SortOrder property or click the pivot grid field’s header.
In the picture below, the data rows are sorted alphabetically in ascending order by the Product Name field. The data columns are sorted in ascending order by the Order Date field.
The sort mode determines the attribute by which to sort field values. You can use the PivotGridFieldBase.SortMode property to specify the sort mode for each field.
Set PivotGridFieldBase.SortMode to PivotSortMode.Value to sort field values in alphabetical order (for text data) or in ascending order (for numeric and date/time data).
Set PivotGridFieldBase.SortMode to PivotSortMode.DisplayText to sort values by the displayed (formatted) text instead of the original field values if numeric field values are formatted in the PivotGridControl.FieldValueDisplayText event handler.
The Pivot Grid allows you to implement a custom sorting algorithm. The custom sorting functionality the control supports depends on whether the Pivot Grid executes data-aware operations on the workstation (In-Memory mode) or server (Server mode).
Optimized calculation engine does not support custom sort mode (PivotGridFieldBase.SortMode is set to Custom) and the PivotGridControl.CustomFieldSort event. Instead, you can implement the technique illustrated in the following example: Custom Sorting in Optimized Mode.
For Legacy and LegacyOptimized engines, set PivotGridFieldBase.SortMode to PivotSortMode.Custom. The PivotGridControl.CustomFieldSort event fires. Implement a field value comparison within the event handler and set the PivotGridCustomFieldSortEventArgsBase<T>.Result value depending on the comparison result.
You can implement custom sorting when Pivot Grid retrieves data from a server data source (for example an OLAP or Entity Framework). For this,
set PivotGridFieldBase.SortMode to PivotSortMode.Custom. The PivotGridControl.CustomServerModeSort event fires.
The following PivotGridFieldBase.SortMode property values are available for sorting data from an OLAP data source in addition to sort modes listed above.
PivotSortMode.DimensionAttributeSorts the field’s data by the OLAP member property (in OLAP mode). Set the PivotGridFieldBase.SortByAttribute property to specify the dimension attribute.PivotSortMode.IDSorts by a level member’s ID (in OLAP mode).PivotSortMode.KeySorts the field’s data by key attributes (in OLAP mode).PivotSortMode.NoneData is not sorted - it is displayed in the order the data source specifies. This option is in effect only in OLAP mode.
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.
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
This example demonstrates how to handle the CustomServerModeSort event to sort data by the e.OLAPMember1 and e.OLAPMember2 properties.
In this example, the Product field values are sorted by the “List Price” OLAP member property. The “List Price” member property values are displayed near the Product field values.
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.Data.Filtering;
using System.Collections;
using System.Linq;
using System;
namespace WinFormsPivotGridCustomOLAPSort {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
// Creates a new collection of OLAP member properties.
fieldProduct.AutoPopulatedProperties = new string[] { "Color", "Class", "List Price" };
//Sets a field's sort mode to Custom to raise the CustomServerModeSort event.
fieldProduct.SortMode = PivotSortMode.Custom;
fieldFiscalYear.FilterValues.FilterType = PivotFilterType.Included;
fieldFiscalYear.FilterValues.ValuesIncluded = new object[] { 2004, 2005 };
pivotGridControl1.BestFit();
}
private void pivotGridControl1_CustomServerModeSort(object sender,
CustomServerModeSortEventArgs e) {
if (e.Field == fieldProduct) {
// Sets the result of comparing the "Product" field's values
// by the "Color" OLAP member property.
e.Result = Comparer.Default.Compare(
e.OLAPMember1.AutoPopulatedProperties["List Price"].Value,
e.OLAPMember2.AutoPopulatedProperties["List Price"].Value
);
}
}
private void pivotGridControl1_FieldValueDisplayText(object sender,
PivotFieldDisplayTextEventArgs e) {
if (e.Field == fieldProduct) {
IOLAPMember currentMember =
e.Field.GetOLAPMembers().First(m => Object.Equals(m.Value, e.Value));
e.DisplayText +=
string.Format(" ({0:C2})", currentMember.AutoPopulatedProperties["List Price"].Value);
}
}
}
}
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Imports System.Collections
Imports System.Linq
Namespace WinFormsPivotGridCustomOLAPSort
Public Partial Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
' Creates a new collection of OLAP member properties.
fieldProduct.AutoPopulatedProperties = New String() {"Color", "Class", "List Price"}
'Sets a field's sort mode to Custom to raise the CustomServerModeSort event.
fieldProduct.SortMode = PivotSortMode.Custom
fieldFiscalYear.FilterValues.FilterType = PivotFilterType.Included
fieldFiscalYear.FilterValues.ValuesIncluded = New Object() {2004, 2005}
pivotGridControl1.BestFit()
End Sub
Private Sub pivotGridControl1_CustomServerModeSort(ByVal sender As Object, ByVal e As CustomServerModeSortEventArgs)
If e.Field Is fieldProduct Then
' Sets the result of comparing the "Product" field's values
' by the "Color" OLAP member property.
e.Result = Comparer.Default.Compare(e.OLAPMember1.AutoPopulatedProperties("List Price").Value, e.OLAPMember2.AutoPopulatedProperties("List Price").Value)
End If
End Sub
Private Sub pivotGridControl1_FieldValueDisplayText(ByVal sender As Object, ByVal e As PivotFieldDisplayTextEventArgs)
If e.Field Is fieldProduct Then
Dim currentMember As IOLAPMember = e.Field.GetOLAPMembers().First(Function(m) Equals(m.Value, e.Value))
e.DisplayText += String.Format(" ({0:C2})", currentMember.AutoPopulatedProperties("List Price").Value)
End If
End Sub
End Class
End Namespace
See Also