Grouping

• Feb 20, 2023
• 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

The 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 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 Pivot Grid can be bound to the same field in the underlying data source. The values of these fields can be grouped independently. The image below shows two column fields that are bound to the Order Date data source field, and grouped by years and quarters, respectively.

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 ASPxPivotGrid Designer.

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 Year 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-9, 10-19, 20-29, 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 10 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:

1. Create a Pivot Grid field.
2. Create an ExpressionDataBinding instance.
3. Initialize the ExpressionDataBinding object with an expression that calculates aggregated field values. You can address Pivot Grid fields in this expression by their names (PivotGridFieldBase.Name).
4. Assign the created object to the field’s PivotGridFieldBase.DataBinding property.

Example: How to Group Date-Time Values

This example shows how to group date-time values.

View Example

Multiple Pivot Grid fields are bound to the same data field - OrderDate. The DataSourceColumnBindingBase.GroupInterval property is used to create a Year → Quarter → Month hierarchy.

ASPX

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" 
    DataSourceID="SqlDataSource1" ClientIDMode="AutoID" IsMaterialDesign="False" 
    OptionsView-RowTotalsLocation="Tree">
    <Fields>
        <dx:PivotGridField ID="fieldSales" Area="DataArea" AreaIndex="0" Name="fieldSales">
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="ExtendedPrice" />
            </DataBindingSerializable>
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldYear" Area="RowArea" Caption="Year" Name="fieldYear" AreaIndex="0">
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="OrderDate" GroupInterval="DateYear" />
            </DataBindingSerializable>
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldQuarter" Area="RowArea" Caption="Quarter" Name="fieldQuarter" AreaIndex="1">
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="OrderDate" GroupInterval="DateQuarter" />
            </DataBindingSerializable>
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldMonth" Area="RowArea" AreaIndex="2" Name="fieldMonth" Caption="Month">
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="OrderDate" GroupInterval="DateMonth" />
            </DataBindingSerializable>
        </dx:PivotGridField>
    </Fields>

    <OptionsView RowTotalsLocation="Tree"></OptionsView>
    <OptionsData DataProcessingEngine="Optimized" />
</dx:ASPxPivotGrid>

Example: Custom Group Intervals

The following example implements custom group intervals in the Pivot Grid to group the 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')

The image below shows the result:

View Example

ASPX

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="ProductReportsDataSource"
    CssClass=""  OnDataBound="ASPxPivotGrid1_DataBound"
    Width="100%">
    <Fields>
        <dx:PivotGridField Area="RowArea" AreaIndex="0" Caption="Product Group" 
            ID="fieldProductName0" Visible="True" GroupInterval="Custom">
                <DataBindingSerializable>
                <dx:ExpressionDataBinding Expression="Iif(Substring([fieldProductName1], 0, 1) &lt; 'F', 'A-E', Substring([fieldProductName1], 0, 1) &lt; 'T', 'F-S', 'T-Z')" />
            </DataBindingSerializable>
                </dx:PivotGridField>
        <dx:PivotGridField Area="RowArea" AreaIndex="1" Caption="Product"
            ID="fieldProductName1">
                <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="ProductName" />
            </DataBindingSerializable>
                </dx:PivotGridField>
        <dx:PivotGridField Area="DataArea" AreaIndex="0" Caption="Sales"
            ID="fieldProductSales" >
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="ProductSales" />
            </DataBindingSerializable>
                </dx:PivotGridField>
        <dx:PivotGridField Area="ColumnArea" AreaIndex="0" Caption="Year - Quarter" 
            ID="fieldShippedDate0" Visible="False">
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="ShippedDate" />
            </DataBindingSerializable>
                </dx:PivotGridField>
        <dx:PivotGridField Area="ColumnArea" AreaIndex="1" Caption="Shipped Date"
            ID="fieldShippedDate1">
            <DataBindingSerializable>
                <dx:DataSourceColumnBinding ColumnName="ShippedDate" GroupInterval="DateYear" />
            </DataBindingSerializable>
                </dx:PivotGridField>
    </Fields>
    <OptionsView ShowFilterHeaders="False" HorizontalScrollBarMode="Auto" />
    <OptionsFilter NativeCheckBoxes="False" />
    <OptionsData DataProcessingEngine="Optimized" />
</dx:ASPxPivotGrid>