Grouping
- 3 minutes to read
By default, all unique field values of column and row fields are listed along the pivot grid’s top and left edges. These values represent headers of the columns and rows - the report’s axes.
The Pivot Grid provides the grouping feature, allowing field values to be grouped into categories (ranges). This is useful for representing date/time and numeric data. For instance, values of a column or row field which displays date/time data, can be grouped by years, months, quarters, etc. Numeric values can be grouped into numeric ranges. String values can be grouped by the starting characters.
The following image shows two ASPxPivotGrid controls. The first control displays the original data before it’s grouped. The second control groups values of the ‘Order Date’ column field by years.
To specify the grouping mode, use the field’s PivotGridFieldBase.GroupInterval property. If the field contains numeric values, use the PivotGridFieldBase.GroupIntervalNumericRange property to specify the length of the intervals. For instance, if this property is set to 10, the values are arranged into the following intervals: 0-9, 10-19, 20-29, etc.
Multiple fields in the ASPxPivotGrid control can be bound to the same field in the underlying data source. The values of these fields can be grouped independently. For instance, it’s possible to create two fields and bind them to the ‘OrderDate’ field in the data source. The first field can group data by years, the second field - by quarters, as shown in the image below:
If you want to group field values using a custom rule, handle the ASPxPivotGrid.CustomGroupInterval event and implement the required grouping algorithm.
Example: How to Group Date-Time Values
Assume that the ASPxPivotGrid control is bound to a data table that contains an 'OrderDate' field. In the following example, two PivotGridField fields are created and displayed in the Column Header Area, each of them bound to the 'OrderDate' field. The first pivot grid field's values will be grouped by years, the second field's values will be grouped by months. To group data, the PivotGridFieldBase.GroupInterval property is used.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-group-date-time-values-e1875
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="GroupDateTimeValues._Default" %>
<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v9.3, Version=9.3.1.0,
Culture=neutral, PublicKeyToken=b88d1754d700e49a"
Namespace="DevExpress.Web.ASPxPivotGrid"
TagPrefix="dx" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v9.3, Version=9.3.1.0,
Culture=neutral, PublicKeyToken=b88d1754d700e49a"
namespace="DevExpress.Web.ASPxEditors"
tagprefix="dx" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server"
DataSourceID="AccessDataSource1">
<Fields>
<dx:PivotGridField ID="fieldUnitPrice" Area="DataArea" AreaIndex="0"
FieldName="UnitPrice">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldQuantity" Area="DataArea" AreaIndex="1"
FieldName="Quantity">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="0"
FieldName="ProductName">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldOrderDate" AreaIndex="0" FieldName="OrderDate"
Visible="False">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldYear" Area="ColumnArea" AreaIndex="0"
GroupInterval="DateYear" Caption="Year"
FieldName="OrderDate" Visible="True">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldMonth" Area="ColumnArea"
AreaIndex="1" GroupInterval="DateMonth"
Caption="Month" FieldName="OrderDate" Visible="True">
</dx:PivotGridField>
</Fields>
</dx:ASPxPivotGrid>
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/nwind.mdb"
SelectCommand="SELECT [Region], [City], [Country], [UnitPrice],
[Quantity], [ProductName], [OrderDate]
FROM [Invoices]">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>