How to: Sort Data by OLAP Member Properties

Dec 17, 2020
3 minutes to read

This example demonstrates how to sort data by OLAP member properties using the ASPxPivotGrid.CustomServerModeSort event. In this example, the “Product” field’s values are sorted by the “Color” OLAP member property.

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

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.1, Version=15.1.7.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" ClientIDMode="AutoID" 
            OLAPConnectionString="provider=MSOLAP;
            data source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll; 
            initial catalog=Adventure Works DW Standard Edition;cube name=Adventure Works;" 
            Theme="Metropolis" OLAPDataProvider="Adomd" 
            oncustomservermodesort="ASPxPivotGrid1_CustomServerModeSort" 
            onfieldvaluedisplaytext="ASPxPivotGrid1_FieldValueDisplayText">
             <Fields>
                <dx:PivotGridField ID="fieldSalesAmount" Area="DataArea" AreaIndex="0" Caption="Sales Amount" DisplayFolder="Sales Summary" FieldName="[Measures].[Sales Amount]" SortByAttribute="">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldProduct" Area="RowArea" AreaIndex="0" Caption="Product" FieldName="[Product].[Product].[Product]" SortByAttribute="" SortMode="Custom">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCategory" AreaIndex="1" Caption="Category" 
                     FieldName="[Product].[Category].[Category]" SortByAttribute="" 
                     Area="ColumnArea" Visible="False">
                </dx:PivotGridField>
                 <dx:PivotGridField ID="fieldFiscalYear" Area="ColumnArea" AreaIndex="0" 
                     Caption="Year" DisplayFolder="Fiscal" 
                     FieldName="[Date].[Fiscal Year].[Fiscal Year]" 
                     ValueFormat-FormatString="d" ValueFormat-FormatType="Custom">
                 </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
    </div>
    </form>
</body>
</html>

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

namespace ASPxPivotGridOLAPCustomServerModeSort
{
    public partial class WebForm1 : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Creates a new collection of OLAP member properties.
            fieldProduct.AutoPopulatedProperties = new string[] { "Color", "List Price" };
            ASPxPivotGrid1.DataBind();
            // Sets a field's sort mode to Custom to raise the CustomServerModeSort event.
            fieldProduct.SortMode = PivotSortMode.Custom;
        }

        protected void ASPxPivotGrid1_CustomServerModeSort(object sender, 
            CustomServerModeSortEventArgs e)
        { 
            if (e.Field.ID == "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
                );
            }

        }

        protected void ASPxPivotGrid1_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
Imports System.Linq
Imports System.Collections
Imports DevExpress.XtraPivotGrid
Imports DevExpress.Web.ASPxPivotGrid

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

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ' Creates a new collection of OLAP member properties.
            fieldProduct.AutoPopulatedProperties = New String() { "Color", "List Price" }
            ASPxPivotGrid1.DataBind()
            ' Sets a field's sort mode to Custom to raise the CustomServerModeSort event.
            fieldProduct.SortMode = PivotSortMode.Custom
        End Sub

        Protected Sub ASPxPivotGrid1_CustomServerModeSort(ByVal sender As Object, ByVal e As CustomServerModeSortEventArgs)
            If e.Field.ID = "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

        Protected Sub ASPxPivotGrid1_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) Object.Equals(m.Value, e.Value))
                e.DisplayText += String.Format(" ({0:C2})", currentMember.AutoPopulatedProperties("List Price").Value)
            End If
        End Sub
    End Class
End Namespace

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

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v15.1, Version=15.1.7.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" ClientIDMode="AutoID" 
            OLAPConnectionString="provider=MSOLAP;
            data source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll; 
            initial catalog=Adventure Works DW Standard Edition;cube name=Adventure Works;" 
            Theme="Metropolis" OLAPDataProvider="Adomd" 
            oncustomservermodesort="ASPxPivotGrid1_CustomServerModeSort" 
            onfieldvaluedisplaytext="ASPxPivotGrid1_FieldValueDisplayText">
             <Fields>
                <dx:PivotGridField ID="fieldSalesAmount" Area="DataArea" AreaIndex="0" Caption="Sales Amount" DisplayFolder="Sales Summary" FieldName="[Measures].[Sales Amount]" SortByAttribute="">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldProduct" Area="RowArea" AreaIndex="0" Caption="Product" FieldName="[Product].[Product].[Product]" SortByAttribute="" SortMode="Custom">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCategory" AreaIndex="1" Caption="Category" 
                     FieldName="[Product].[Category].[Category]" SortByAttribute="" 
                     Area="ColumnArea" Visible="False">
                </dx:PivotGridField>
                 <dx:PivotGridField ID="fieldFiscalYear" Area="ColumnArea" AreaIndex="0" 
                     Caption="Year" DisplayFolder="Fiscal" 
                     FieldName="[Date].[Fiscal Year].[Fiscal Year]" 
                     ValueFormat-FormatString="d" ValueFormat-FormatType="Custom">
                 </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
    </div>
    </form>
</body>
</html>

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode