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:
End-User Sorting by Summary
End-users can sort by summary through the context menu by right-clicking an innermost column or row header.
The 
and 
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 displayed within the Column Header Area or Row Header Area. There are two ways you can disable this functionality for end-users:
| Property | Description | Note |
|---|---|---|
| PivotGridControl.AllowSortBySummary | Gets or sets whether end-users can sort the row/column field values by other column/row summary values. This is a dependency property. | This property affects all fields. |
| PivotGridField.AllowSortBySummary | Gets or sets whether end-users can sort the current row/column field values by other column/row summary values. This is a dependency property. | This property affects individual fields. |
Note that in this instance, individual fields’ settings take priority over PivotGridControl settings.
Sorting by Summary in Code
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 PivotGridField.SortByField property.
This allows you to sort data by the currently displayed summary values.
Code-Behind:
Markup:
<dxpg:PivotGridField Name="fieldProductName" SortByField="{Binding ElementName=fieldExtendedPrice}"> </dxpg:PivotGridField>Assign the data field name to the PivotGridField.SortByFieldName property.
In this instance, data is sorted by summary values calculated with the function whose type the PivotGridField.SortBySummaryType property specifies, regardless of which summary values are currently displayed.
For example, the code sample below displays sorting by the average of the Extended Price‘s values:
Code-Behind:
fieldProductName.SortBySummaryType = FieldSummaryType.Average; fieldProductName.SortByFieldName = "Extended Price";Markup:
<dxpg:PivotGridField Name="fieldProductName" SortByFieldName="Extended Price" SortBySummaryType="Average"> </dxpg:PivotGridField>
After you specified a data field, the PivotGrid control 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:
To sort field values by any other column/row or their totals, identify this field by adding sorting conditions using the PivotGridField.SortByConditions property. Each condition is a SortByCondition object that identifies a field value so that the whole collection identifies a column/row.
Create two sort conditions to sort field values by the Qtr 2 Total column total.
fieldProductName.SortBySummaryField = fieldExtendedPrice;
fieldProductName.SortByConditions.Add(new SortByCondition(fieldYear, 2016));
fieldProductName.SortByConditions.Add(new SortByCondition(fieldQuarter, 2));
The image below demonstrates the corresponding result:
Create an additional condition to sort data by the specified column/row:
fieldProductName.SortBySummaryField = fieldExtendedPrice;
fieldProductName.SortByConditions.Add(new SortByCondition(fieldYear, 2016));
fieldProductName.SortByConditions.Add(new SortByCondition(fieldQuarter, 2));
fieldProductName.SortByConditions.Add(new SortByCondition(fieldMonth, 5));
Three sort conditions identify the highlighted column on the image below:
You can sort data by a custom total column/row by specifying its type using the PivotGridField.SortByCustomTotalSummaryType property.
Use the PivotGridField.SortOrder property to specify whether to sort values in ascending or descending order.
Sorting in OLAP
In an OLAP mode, create sort conditions via a SortByCondition constructor overload that takes an OLAP member’s unique name as a parameter.
To obtain an OLAP member for a field value, use the PivotGridControl.GetFieldValueOlapMember method. This method returns a PivotOlapMember object. To access the OLAP member’s unique name, use its PivotOlapMember.UniqueName property.
Limitations
The following Pivot Grid features are not supported or ignored when you sort data by summary:
- Running totals are not supported.
- The custom summary representation (PivotGridField.SummaryDisplayType) is ignored.
- The custom values obtained in the PivotGridControl.CustomCellValue and PivotGridControl.CustomCellDisplayText events are ignored.
You cannot use the CalculationBinding descendants to apply summary filter or sorting by summary when Pivot Grid operates with Optimized calculation engine.