ASPxPivotGrid.CustomServerModeSort Event

In OLAP and server mode, provides the capability to sort data using custom rules.

Namespace: DevExpress.Web.ASPxPivotGrid

Assembly: DevExpress.Web.ASPxPivotGrid.v18.2.dll

Declaration

C#
VB.NET

public event EventHandler<CustomServerModeSortEventArgs> CustomServerModeSort

Public Event CustomServerModeSort As EventHandler(Of CustomServerModeSortEventArgs)

Event Data

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

Property	Description
Field	Gets the field being processed. Inherited from PivotFieldEventArgsBase<T>.
IsFilterPopupSorting	Get the value that indicates whether the PivotGridControl.CustomServerModeSort event is raised. Inherited from CustomServerModeSortEventArgsBase<T>.
OLAPMember1	Get the first member being compared. Inherited from CustomServerModeSortEventArgsBase<T>.
OLAPMember2	Get the second member being compared. Inherited from CustomServerModeSortEventArgsBase<T>.
Result	Gets or sets the result of a custom comparison. Inherited from CustomServerModeSortEventArgsBase<T>.
Value1	Gets the first value being compared. Inherited from CustomServerModeSortEventArgsBase<T>.
Value2	Gets the second value being compared. Inherited from CustomServerModeSortEventArgsBase<T>.

The event data class exposes the following methods:

Method	Description
GetCellValue1(CrossAreaKey, PivotGridFieldBase)	In OLAP and server mode, returns the first cell value calculated for the specified cross area key against the specified data field. Inherited from CustomServerModeSortEventArgsBase<T>.
GetCellValue1(Object[], PivotGridFieldBase)	In OLAP and server mode, returns the first cell value calculated for the specified cross area field values against the specified data field. Inherited from CustomServerModeSortEventArgsBase<T>.
GetCellValue2(CrossAreaKey, PivotGridFieldBase)	In OLAP and server mode, returns the second cell value calculated for the specified cross area key against the specified data field. Inherited from CustomServerModeSortEventArgsBase<T>.
GetCellValue2(Object[], PivotGridFieldBase)	In OLAP and server mode, returns the second cell value calculated for the specified cross area field values against the specified data field. Inherited from CustomServerModeSortEventArgsBase<T>.
GetCrossAreaKey(Object[])	In OLAP and server mode, returns cross area values by which you want to sort the pivot grid column or row. Inherited from CustomServerModeSortEventArgsBase<T>.

Remarks

Handle the CustomServerModeSort event to provide a custom sorting algorithm in OLAP and server mode for a specific field. Note, that the PivotGridFieldBase.SortMode property of the sorted field should be set to Custom to apply custom sorting. Otherwise, the CustomServerModeSort event will not be raised.

The field being processed is specified by the ID using the PivotGridField.ID property.

Specify the cross area field values by which you want to sort the data by creating a new instance of CrossAreaKey class and assign values to this variable using the CustomServerModeSortEventArgsBase<T>.GetCrossAreaKey method.

Use the CustomServerModeSortEventArgsBase<T>.GetCellValue1 and CustomServerModeSortEventArgsBase<T>.GetCellValue2 methods to get cell values.

In the event handler, compare these values and assign the result to the CustomServerModeSortEventArgsBase<T>.Result property.

Set the value to -1 to make the current object precede the compared object in the sort order.
Set the value to 0 to leave the current object in the same position as the compared object.
Set the value to 1 to make the current object follow the compared object in the sort order.

In OLAP, the members to be compared are specified by the CustomServerModeSortEventArgsBase<T>.OLAPMember1 and CustomServerModeSortEventArgsBase<T>.OLAPMember2 properties.

To sort data using the OLAP member properties, set the PivotGridFieldBase.SortMode property of the sorted field to DimensionAttribute and use the PivotGridFieldBase.SortByAttribute property to sort data.

In OLAP mode, when you get the IOLAPMember.Properties property, the PivotGridControl sends a query to the server every time for each member. While sorting data using the CustomServerModeSort event, you can use the PivotGridFieldBase.AutoPopulatedProperties to specify the list of OLAP member properties which should be returned with a data query from the server. Create a new collection of OLAP member attributes using the PivotGridFieldBase.AutoPopulatedProperties property to improve performance.

To get the OLAP member properties values, use the IOLAPMember.AutoPopulatedProperties property.

Example

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.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-sort-data-in-server-mode-using-the-customservermodesort-event-t262815

<%@ 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

ASPxPivotGrid.CustomServerModeSort Event

Declaration

Event Data

Remarks

Example