How to: Sort Data by Columns (Rows) in OLAP Mode

• Jun 27, 2019
• 3 minutes to read

The following example demonstrates how to implement Sorting by Summary in OLAP mode.

In this example, values of the Semester field are sorted by the Australia | Bendigo column summary values. To do this, two sort conditions represented by PivotGridFieldSortCondition instances are created. One of them represents an OLAP member that corresponds to the ‘Australia’ value, while another corresponds to the ‘Bendigo’ value. These sort conditions are added to the Semester field’s PivotGridFieldSortBySummaryInfo.Conditions collection to specify the column by which Semester values should be sorted. A data field that identifies the column is specified via the PivotGridFieldSortBySummaryInfo.Field property.

OLAP members corresponding to the Australia and Bendigo values are obtained using the PivotGridControl.GetFieldValueOLAPMember method. Note that OLAP members can be obtained only for visible field values. For this reason, the Australia field value is expanded before initializing OLAP members in order to obtain the Bendigo OLAP member.

This sample uses the Adventure Works 2008 cube.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-implement-sorting-by-summary-in-olap-mode-e3023

Form1.cs

using System;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;

namespace XtraPivotGrid_OLAPSortBySummary {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e) {
            // Expands the Australia column to be able to retrieve OLAP members 
            // that correspond to the nested columns.
            pivotGridControl1.ExpandValue(true, new object[] { "Australia" });

            // Obtains OLAP members corresponding to the Australia and Bendigo values.
            IOLAPMember countryMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCountry, 0);
            IOLAPMember cityMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCity, 0);

            // Exits if the OLAP members were not obtained successfully.
            if (countryMember == null || cityMember == null)
                return;

            // Locks the pivot grid from updating while the Sort by Summary
            // settings are being customized.
            pivotGridControl1.BeginUpdate();
            try {

                // Specifies a data field whose summary values should be used to sort values
                // of the Fiscal Year field.
                fieldFiscalYear.SortBySummaryInfo.Field = fieldInternetSalesAmount;

                // Specifies a column by which the Fiscal Year field values should be sorted.
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(
                    new PivotGridFieldSortCondition(fieldCountry, "Australia", countryMember.UniqueName));
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(
                    new PivotGridFieldSortCondition(fieldCity, "Bendigo", cityMember.UniqueName));
            }
            finally {
                // Unlocks the pivot grid and applies changes.
                pivotGridControl1.EndUpdate();
            }
        }
    }
}

Form1.vb

Imports System
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid

Namespace XtraPivotGrid_OLAPSortBySummary
    Partial Public Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
        End Sub
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
            ' Expands the Australia column to be able to retrieve OLAP members 
            ' that correspond to the nested columns.
            pivotGridControl1.ExpandValue(True, New Object() { "Australia" })

            ' Obtains OLAP members corresponding to the Australia and Bendigo values.
            Dim countryMember As IOLAPMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCountry, 0)
            Dim cityMember As IOLAPMember = pivotGridControl1.GetFieldValueOLAPMember(fieldCity, 0)

            ' Exits if the OLAP members were not obtained successfully.
            If countryMember Is Nothing OrElse cityMember Is Nothing Then
                Return
            End If

            ' Locks the pivot grid from updating while the Sort by Summary
            ' settings are being customized.
            pivotGridControl1.BeginUpdate()
            Try

                ' Specifies a data field whose summary values should be used to sort values
                ' of the Fiscal Year field.
                fieldFiscalYear.SortBySummaryInfo.Field = fieldInternetSalesAmount

                ' Specifies a column by which the Fiscal Year field values should be sorted.
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition( _
                                                                 fieldCountry, _
                                                                 "Australia", _
                                                                 countryMember.UniqueName))
                fieldFiscalYear.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition( _
                                                                 fieldCity, _
                                                                 "Bendigo", _
                                                                 cityMember.UniqueName))
            Finally
                ' Unlocks the pivot grid and applies changes.
                pivotGridControl1.EndUpdate()
            End Try
        End Sub
    End Class
End Namespace