Grouping
- 5 minutes to read
This topic describes how to group values of column and row Pivot Grid fields bound to data source columns. These techniques are applicable in In-Memory and Server data processing modes. Grouping is not supported in OLAP mode.
Grouping Overview
Pivot Grid allows you to group field values into bigger categories (ranges). For example, values of a column/row field that displays date/time data can be grouped by years, months, quarters, and so on. Numeric values can be grouped into numeric ranges. String values can be grouped by the initial characters of the values.
The following image demonstrates how the Pivot Grid groups values of the Order Date column field by month:
You can bind multiple Pivot Grid fields to the same data source field, and group values of these Pivot Grid fields independently of each other. The image below shows two column fields that are bound to the Order Date data source field, and grouped by years and months, respectively.
Refer to the following article for information on how to use group intervals to create hierarchies and display data of the same source field at different detail levels: Hierarchical Value Presentation.
Predefined Group Intervals
To group values of column or row fields, set the field’s group interval. For example, you can set this option in the Properties grid:
Use the following properties to set the field’s group interval in code:
DataSourceColumnBindingBase.GroupInterval - is used when the Pivot Grid operates in Server or In-Memory mode with the Optimized calculation engine (DataProcessingEngine is set to Optimized).
PivotGridFieldBase.GroupInterval - is used when the Pivot Grid operates in In-Memory mode with the Legacy or LegacyOptimized data processing engines (DataProcessingEngine is set to Legacy, LegacyOptimized, or Default).
The Pivot Grid allows you to group date/time, numeric, and string field values.
Group Date-Time Values
When you group date-time field values, you can set the GroupInterval property to one of the following options:
- Date
- DateDay
- DateDayOfWeek
- DateDayOfYear
- DateWeekOfMonth
- DateWeekOfYear
- DateMonth
- DateQuarter
- DateYear
- YearAge
- MonthAge
- WeekAge
- DayAge
- Hour
- Minute
- Second
- DateMonthYear
- DateQuarterYear
- DateHour
- DateHourMinute
- DateHourMinuteSecond
- DateWeekYear
The following Pivot Grid control groups data in the OrderDate column by year:
You can use the DataSourceColumnBindingBase.GroupIntervalNumericRange(PivotGridFieldBase.GroupIntervalNumericRange for Legacy and LegacyOptimized) property to specify the length of the following intervals:
- YearAge
- MonthAge
- WeekAge
- DayAge
Group Numeric Values
Set the GroupInterval property to Numeric to group numeric values.
Use the GroupIntervalNumericRange property to specify the length of the intervals. For instance, if the GroupIntervalNumericRange property is set to 100, the values are combined into the following intervals: 0-100, 101-200, 201-300, and so on. The image below shows the Pivot Grid with two Order ID fields. The values of the first field are arranged into groups of 100 orders.
Group String Values (Alphabetically)
String values can be combined into groups alphabetically by the first letter. For this, set the GroupInterval property to Alphabetical.
Custom Group Intervals
If standard grouping modes do not suit your requirements, you can implement custom group intervals. Assign a calculated expression to a field as follows:
- Create a Pivot Grid field.
- Create an ExpressionDataBinding instance.
- Initialize the
ExpressionDataBindingobject with an expression that calculates aggregated field values. You can address Pivot Grid fields in this expression by their names (PivotGridFieldBase.Name). - Assign the created object to the field’s PivotGridFieldBase.DataBinding property.
Example
The following example implements custom group intervals in Pivot Grid to group Product Name field values into three ranges: A-E, F-S, and T-Z (according to the initial characters of the product names). The following expression is used:
Iif(Substring([Product Name], 0, 1) < 'F', 'A-E', Substring([Product Name], 0, 1) < 'T', 'F-S', 'T-Z')
using DevExpress.XtraPivotGrid;
using System.Windows.Forms;
namespace CustomGroupIntervals {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
// ...
PivotGridField pivotGridFieldProducts = new PivotGridField();
pivotGridFieldProducts.Area = PivotArea.RowArea;
DataSourceColumnBinding productNames = new DataSourceColumnBinding("ProductName");
pivotGridFieldProducts.DataBinding = productNames;
pivotGridFieldProducts.Name = "fieldProductName";
pivotGridControl1.Fields.Add(pivotGridFieldProducts);
PivotGridField pivotGridFieldGroups = new PivotGridField();
pivotGridFieldGroups.Area = PivotArea.RowArea;
pivotGridFieldGroups.Caption = "Product Groups";
pivotGridFieldGroups.Options.ShowExpressionEditorMenu = true;
ExpressionDataBinding customInterval = new ExpressionDataBinding("Iif(Substring([fieldProductName], 0, 1) < 'F'," +
" 'A-E', Substring([fieldProductName], 0, 1) < 'T', 'F-S', 'T-Z')");
pivotGridFieldGroups.DataBinding = customInterval;
pivotGridFieldGroups.AreaIndex = 0;
pivotGridControl1.Fields.Add(pivotGridFieldGroups);
// ...
}
}
}
Handle the PivotGridControl.CustomGroupInterval event to implement custom group intervals when the Pivot Grid uses In-Memory mode with the Legacy or LegacyOptimized data processing engine (DataProcessingEngine is set to Legacy, LegacyOptimized, or Default).
Demos
To run these demos, first install the DevExpress WinForms product library. Download.