Data Sorting
- 3 minutes to read
The Pivot Grid allows its data to be sorted by an unlimited number of fields. To enable data sorting, set the field’s PivotGridFieldOptions.AllowSort property to ‘True’. If this property is set to ‘Default’, the field’s behavior is controlled by the ASPxPivotGrid’s PivotGridOptionsCustomization.AllowSort option.
Sort Order
Data within the ASPxPivotGrid is always sorted against the fields displayed within the Column Header Area and Row Header Area. By default, the values are sorted in ascending order. If values are displayed hierarchically, field values in the child groups are also sorted. The order in which field values are sorted is specified by a field’s PivotGridFieldBase.SortOrder property. End-users can toggle the sort order by clicking a field’s header.
Sort Modes
Field values can be sorted by values, display text, properties or using a custom sorting algorithm. A field’s sorting mode is specified by the PivotGridFieldBase.SortMode property. By default, it is set to PivotSortMode.Default, which is currently the same option as PivotSortMode.Value. Field values are sorted in alphabetical order (for text data) and in order of magnitude (for numeric and date/time data).
To sort field values by display text, set the PivotGridFieldBase.SortMode property to PivotSortMode.DisplayText. This can be useful when field values are numeric, and they are specifically formatted within the ASPxPivotGrid.FieldValueDisplayText event handler.
To sort values using custom rules, set the field’s PivotGridFieldBase.SortMode property to PivotSortMode.Custom, and handle the ASPxPivotGrid.CustomFieldSort event to implement the required sorting logic. In OLAP and server mode, handle the ASPxPivotGrid.CustomServerModeSort event to improve performance.
In OLAP mode, you can sort pivot grid field data by OLAP member properties. Set the field’s PivotGridFieldBase.SortMode property to PivotSortMode.DimensionAttribute and assign a property name to the field’s PivotGridFieldBase.SortByAttribute property. To get a list of field properties, call the PivotGridFieldBase.GetOLAPMemberProperties method.
Example: How to Implement Sort by Summary Feature
This example demonstrates how to sort values of the ‘Product Name’ column field by summary values calculated against the ‘Quantity’ data field. To do this, assign the ‘Quantity’ field name the ‘ProductName’ field’s FieldName property. As a result, the values is arranged in the order specified by summary values.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="SortBySummary._Default" %>
<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v9.3, Version=9.3.1.0,
Culture=neutral, PublicKeyToken=b88d1754d700e49a"
Namespace="DevExpress.Web.ASPxPivotGrid"
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"
DataSourceID="AccessDataSource1">
<Fields>
<dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="1"
FieldName="ProductName" SortBySummaryInfo-FieldName="Quantity">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldSalesperson" Area="RowArea" AreaIndex="0"
FieldName="Salesperson">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldQuantity" Area="DataArea" AreaIndex="0"
FieldName="Quantity">
</dx:PivotGridField>
</Fields>
</dx:ASPxPivotGrid>
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/nwind.mdb"
SelectCommand="SELECT [ProductName], [Salesperson], [Quantity] FROM [Invoices]">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>