How to: Sort Data in Server Mode Using Custom Sorting Algorithm

Dec 17, 2020
5 minutes to read

This example demonstrates how to implement a custom sorting algorithm by handling the ASPxPivotGrid.CustomServerModeSort event. The “Month” column field is sorted by the “Dairy Products” row using the cross area key, and the “Category” field is sorted directly by the 1996 year.

View Example

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="ASPxPivotGridCustomServerModeSort.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.1, Version=15.1.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<%@ Register assembly="DevExpress.Web.v15.1, Version=15.1.4.0, Culture=neutral, 
PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Data.Linq" tagprefix="dx" %>
<%@ Register assembly="DevExpress.Web.v15.1, Version=15.1.4.0, Culture=neutral, 
PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Data.Linq" tagprefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            DataSourceID="EntityServerModeDataSource1" EnableTheming="True" Theme="Metropolis" 
            oncustomservermodesort="ASPxPivotGrid1_CustomServerModeSort">
            <Fields>
                <dx:PivotGridField ID="fieldOrderMonth" Area="ColumnArea" AreaIndex="1" 
                    Caption="Month" FieldName="OrderDate" GroupInterval="DateMonth" 
                    UnboundFieldName="fieldOrderMonth">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldPrice" Area="DataArea" AreaIndex="0" 
                    Caption="Price" FieldName="Extended_Price">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldOrderYear" Area="ColumnArea" AreaIndex="0" 
                Caption="Year" FieldName="OrderDate" GroupInterval="DateYear" UnboundFieldName="field">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0" 
                FieldName="CategoryName" Caption="Category" >
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
        <dx:EntityServerModeDataSource ID="EntityServerModeDataSource1" 
            runat="server" 
            ContextTypeName="ASPxPivotGridCustomServerModeSort.nwindEntities" 
            OnSelecting="EntityServerModeDataSource1_Selecting" 
            TableName="SalesPersons" />
    </div>
    </form>
</body>
</html>

using System;
using DevExpress.XtraPivotGrid;
using System.Collections;
using DevExpress.Data.Linq;
using DevExpress.Web.ASPxPivotGrid;

namespace ASPxPivotGridCustomServerModeSort
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack && !IsCallback)
            {
                PivotGridField field = ASPxPivotGrid1.Fields["Year"];
                ASPxPivotGrid1.BeginUpdate();
                try
                {
                    field.FilterValues.Clear();
                    field.FilterValues.Add(1996);
                    field.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included;
                }
                finally
                {
                    ASPxPivotGrid1.EndUpdate();
                }
            }

            // Sets fields' sort mode to Custom to raise the CustomServerModeSort event.
            fieldOrderMonth.SortMode = PivotSortMode.Custom;
            fieldCategoryName.SortMode = PivotSortMode.Custom;           
        }

        protected void EntityServerModeDataSource1_Selecting(object sender, 
            LinqServerModeDataSourceSelectEventArgs e)
        {
            e.KeyExpression = "OrderID";
            e.QueryableSource = new ASPxPivotGridCustomServerModeSort.NWindEntities().SalesPersons;
        }

        protected void ASPxPivotGrid1_CustomServerModeSort(object sender, CustomServerModeSortEventArgs e)
        {
            // Sorting using a cross area object.
            if (e.Field.ID == "fieldOrderMonth")
            {
                // Sets the cross area key, by which the "Month" field will be sorted. 
                // In this example, it's one of the "Category" cross area field values.
                CrossAreaKey sorting = e.GetCrossAreaKey(new object[] { "Dairy Products" });

                // Sets the result of the "Month" field's values comparison 
                // by the cross area key object and the "Price" field.
                e.Result = Comparer.Default.Compare(
                    e.GetCellValue1(sorting, fieldPrice), 
                    e.GetCellValue2(sorting, fieldPrice)
                );

                // Allows you to change the "Month" field's sort order without lose of sorting.
                if (fieldOrderMonth.SortOrder == PivotSortOrder.Descending) e.Result *= -1;
            }

            // Direct sorting without using a cross area object. 
            if (e.Field.ID == "fieldCategoryName")
            {
                // Sets the result of "Category" field's values comparison by the Year and Price fields.
                e.Result = Comparer.Default.Compare(
                    e.GetCellValue1(new object[] { "1996" }, fieldPrice),
                    e.GetCellValue2(new object[] { "1996" }, fieldPrice)
                );

                // Allows you to change the "Category" field's sort order without lose of sorting.
                if (fieldCategoryName.SortOrder == PivotSortOrder.Descending) e.Result *= -1;
            }
        }
    }
}

Imports System
Imports DevExpress.XtraPivotGrid
Imports System.Collections
Imports DevExpress.Data.Linq
Imports DevExpress.Web.ASPxPivotGrid

Namespace ASPxPivotGridCustomServerModeSort
    Partial Public Class WebForm1
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If (Not IsPostBack) AndAlso (Not IsCallback) Then
                Dim field As PivotGridField = ASPxPivotGrid1.Fields("Year")
                ASPxPivotGrid1.BeginUpdate()
                Try
                    field.FilterValues.Clear()
                    field.FilterValues.Add(1996)
                    field.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included
                Finally
                    ASPxPivotGrid1.EndUpdate()
                End Try
            End If

            ' Sets fields' sort mode to Custom to raise the CustomServerModeSort event.
            fieldOrderMonth.SortMode = PivotSortMode.Custom
            fieldCategoryName.SortMode = PivotSortMode.Custom
        End Sub

        Protected Sub EntityServerModeDataSource1_Selecting(ByVal sender As Object, ByVal e As LinqServerModeDataSourceSelectEventArgs)
            e.KeyExpression = "OrderID"
            e.QueryableSource = (New ASPxPivotGridCustomServerModeSort.NWindEntities()).SalesPersons
        End Sub

        Protected Sub ASPxPivotGrid1_CustomServerModeSort(ByVal sender As Object, ByVal e As CustomServerModeSortEventArgs)
            ' Sorting using a cross area object.
            If e.Field.ID = "fieldOrderMonth" Then
                ' Sets the cross area key, by which the "Month" field will be sorted. 
                ' In this example, it's one of the "Category" cross area field values.
                Dim sorting As CrossAreaKey = e.GetCrossAreaKey(New Object() { "Dairy Products" })

                ' Sets the result of the "Month" field's values comparison 
                ' by the cross area key object and the "Price" field.
                e.Result = Comparer.Default.Compare(e.GetCellValue1(sorting, fieldPrice), e.GetCellValue2(sorting, fieldPrice))

                ' Allows you to change the "Month" field's sort order without lose of sorting.
                If fieldOrderMonth.SortOrder = PivotSortOrder.Descending Then
                    e.Result *= -1
                End If
            End If

            ' Direct sorting without using a cross area object. 
            If e.Field.ID = "fieldCategoryName" Then
                ' Sets the result of "Category" field's values comparison by the Year and Price fields.
                e.Result = Comparer.Default.Compare(e.GetCellValue1(New Object() { "1996" }, fieldPrice), e.GetCellValue2(New Object() { "1996" }, fieldPrice))

                ' Allows you to change the "Category" field's sort order without lose of sorting.
                If fieldCategoryName.SortOrder = PivotSortOrder.Descending Then
                    e.Result *= -1
                End If
            End If
        End Sub
    End Class
End Namespace

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="WebForm1.aspx.vb" Inherits="ASPxPivotGridCustomServerModeSort.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.1, Version=15.1.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<%@ Register assembly="DevExpress.Web.v15.1, Version=15.1.4.0, Culture=neutral, 
PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Data.Linq" tagprefix="dx" %>
<%@ Register assembly="DevExpress.Web.v15.1, Version=15.1.4.0, Culture=neutral, 
PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Data.Linq" tagprefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            DataSourceID="EntityServerModeDataSource1" EnableTheming="True" Theme="Metropolis" 
            oncustomservermodesort="ASPxPivotGrid1_CustomServerModeSort">
            <Fields>
                <dx:PivotGridField ID="fieldOrderMonth" Area="ColumnArea" AreaIndex="1" 
                    Caption="Month" FieldName="OrderDate" GroupInterval="DateMonth" 
                    UnboundFieldName="fieldOrderMonth">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldPrice" Area="DataArea" AreaIndex="0" 
                    Caption="Price" FieldName="Extended_Price">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldOrderYear" Area="ColumnArea" AreaIndex="0" 
                Caption="Year" FieldName="OrderDate" GroupInterval="DateYear" UnboundFieldName="field">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0" 
                FieldName="CategoryName" Caption="Category" >
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
        <dx:EntityServerModeDataSource ID="EntityServerModeDataSource1" 
            runat="server" 
            ContextTypeName="ASPxPivotGridCustomServerModeSort.nwindEntities" 
            OnSelecting="EntityServerModeDataSource1_Selecting" 
            TableName="SalesPersons" />
    </div>
    </form>
</body>
</html>

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode