How to: Sort the Pivot Grid's Data by OLAP Member Properties

• Aug 01, 2019
• 2 minutes to read

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

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-sort-the-pivot-grids-data-by-olap-member-properties-t264133

Form1.cs

using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
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;
        }

        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);
            }
        }
    }

}

Form1.vb

Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Imports System.Collections
Imports System.Linq
Imports System

Namespace WinFormsPivotGridCustomOLAPSort
    Partial Public 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
        End Sub

        Private Sub pivotGridControl1_CustomServerModeSort(ByVal sender As Object,
               ByVal e As CustomServerModeSortEventArgs) Handles pivotGridControl1.CustomServerModeSort
            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) Handles pivotGridControl1.FieldValueDisplayText
            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