How to: Implement Custom Group Intervals

• Dec 17, 2020

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>