Sorting by Summary

  • 4 minutes to read

Sorting by Summary allows you to sort the current column or row field's values by corresponding summary values.

The following image illustrates the Pivot Grid Control with Product field values sorted by the USA | Margaret Peacock | Quantity column:

SortBySummaryMenu_res

This topic consists of the following sections.

End-User Sorting by Summary

End-users can sort by summary through the context menu by right-clicking an innermost column or row header.

win-pivot-sorting-by-summary-context-menu

The pivotgrid_sorbysummaryglyph image in the row's/column's header indicates if the field values are sorted by this row/column.

Sorting by Summary is available for all fields if they are displayed within the Column Header Area or Row Header Area. There are two ways with which you can disable this functionality for end-users.

Property Description Note
PivotGridOptionsCustomization.AllowSortBySummary Gets or sets whether end-users can sort row field values by column values, and column field values by row values. This property affects to all fields.
PivotGridFieldOptions.AllowSortBySummary Gets or sets whether end-users can sort the current row/column field values by other column/row summary values. This property affects individual fields.

Note that in this instance, settings of individual fields take priority over Pivot Grid Control settings.

Sorting by Summary in Code

Use the field's PivotGridFieldBase.SortBySummaryInfo property to get access to the settings that are used to sort the values of the current column field or row field by corresponding summary values.

TIP

Demo: Code Examples - Sorting - Sorting By Summary module in the XtraPivotGrid MainDemo

Requires installation of WinForms Subscription. Download.

To sort data by summaries, you need to specify a data field whose summary values should define the sort order. Do one of the following:

  • Assign the data field to the PivotGridFieldSortBySummaryInfo.Field property.

    This allows you to sort data by the currently displayed summary values.

    
    fieldProductName.SortBySummaryInfo.Field = fieldExtendedPrice;
    
  • Assign the data field name to the PivotGridFieldSortBySummaryInfo.FieldName property.

    In this instance, data will be sorted by summary values calculated with the function whose type is specified by the PivotGridFieldSortBySummaryInfo.SummaryType property, regardless of which summary values are currently displayed.

    
    fieldProductName.SortBySummaryInfo.FieldName = "Extended Price";
    

After you specified a data field, Pivot Grid sorts field values by a Grand Total column/row that corresponds to this data field.

The image below shows the Pivot Grid sorted by the Extended Price's Grand Total values:

pivot-sorting-by-summary-data-field

To sort field values by any other column/row or their totals, identify this field by adding sort conditions using the PivotGridFieldSortBySummaryInfo.Conditions property. Each condition is a PivotGridFieldSortCondition object that identifies a field value so that the whole collection identifies a column/row.

Create a sort condition to sort field values by the 2016 Total column total.


fieldProductName.SortBySummaryInfo.Field = fieldExtendedPrice;
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldYear, 2016));

The image below demonstrates the corresponding result:

pivotgrid_sortconditions

Create additional conditions to sort data by the specified column/row:


fieldProductName.SortBySummaryInfo.Field = fieldPrice;
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldYear, 2016));
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldQuarter, 2));
fieldProductName.SortBySummaryInfo.Conditions.Add(new PivotGridFieldSortCondition(fieldMonth, 5));

The highlighted column on the image below is identified by three sort conditions:

pivot-sorting-by-summary-3-conditions

To sort data by a custom total column/row, specify its type using the PivotGridFieldSortBySummaryInfo.CustomTotalSummaryType property.

Use the PivotGridFieldBase.SortOrder property to specify whether to sort values in ascending or descending order.

Sorting in OLAP

In OLAP mode, create sort conditions using a PivotGridFieldSortCondition constructor overload that takes a unique OLAP member name as a parameter.

To obtain an OLAP member for a field value, use the PivotGridControl.GetFieldValueOLAPMember method. This method returns an object that implements the IOLAPMember interface. To access the unique OLAP member name, use its IOLAPMember.UniqueName property.

Limitations

The following Pivot Grid features are not supported or ignored when you sort data by summary:

Examples