Bind Pivot Grid Fields to Calculated Expressions

• Oct 20, 2023
• 4 minutes to read

The Pivot Grid uses the Binding API to bind Pivot Grid fields to data. You can use calculated expressions, data source columns, or window calculations as data binding sources.

Calculated fields display the result of calculated expressions. Each calculated field has a binding expression that can be a formula or aggregate function. In addition to the ability to retrieve values from a field in the data source, the expression allows you to specify how data is calculated (for example, aggregate it).

Note

This article describes how to use calculated fields in Optimized, OLAP, and Server data processing modes. If you use Legacy or LegacyOptimized mode, refer to the following article: Unbound Fields.

Expressions are computed at the data source level. This means that if you specify a field in the expression, the Pivot Grid uses the field values from the data source. Use the PivotGridFieldBase.Name property to specify the field.

If you want to use aggregated field values, wrap a field in the corresponding aggregate function. The PivotGridFieldBase.SummaryType property is not in effect for the field that is bound to such expression.

For example, the following expression is calculated based on underlying values of the Unit Price and Quantity fields:

[Unit Price] * [Quantity]

If you want to calculate the average sales, use the following expression:

Avg([Unit Price]) * Avg([Quantity])

Create a Calculated Field in Visual Studio Designer

Optimized and Server Modes

Follow the steps below to bind a Pivot Grid field to a data source column:

1. Click the Pivot Grid’s smart tag icon and then click Designer… to invoke the ASPxPivotGrid Designer.

2. Add a new field to one of the four areas: Data Header Area, Column Header Area, Row Header Area, Filter Header Area.

3. Set the field’s DataBinding property to Expression and specify the expression.

The image below shows the First Sold Product field that is bound to the FirstValue([ProductName]) expression.

OLAP Mode

Follow the steps below to bind a Pivot Grid field to a measure or dimension:

1. Click the Pivot Grid’s smart tag icon and then click Designer… to invoke the ASPxPivotGrid Designer.

2. Add a new field to one of the four areas: Data Header Area, Column Header Area, Row Header Area, Filter Header Area.

3. Set the field’s DataBinding property to OLAP Expression and specify the expression.

Create a Calculated Field in Code

Optimized and Server Modes

Optimized and Server modes support ExpressionDataBinding.

Follow the steps below to create a calculated field:

1. Create an ExpressionDataBinding instance and pass the expression to its constructor as a parameter.
2. Assign the created object to the PivotGridFieldBase.DataBinding property.

View Example

The following code snippet shows how to use ExpressionDataBinding to display the total discount amount and discounted sales:

Default.aspx

<dx:PivotGridField ID="fieldDiscountAmount" Area="DataArea"
      AreaIndex="2"
      Caption="Discount Amount">
    <DataBindingSerializable>
        <dx:ExpressionDataBinding Expression="[ExtendedPrice]*[Discount]" />
    </DataBindingSerializable>
</dx:PivotGridField>
<dx:PivotGridField ID="fieldPriceWithDiscount" Area="DataArea"
      AreaIndex="3" 
      Caption="Price with Discount"
      CellFormat-FormatType ="Numeric" CellFormat-FormatString = "c0">
    <DataBindingSerializable>
        <dx:ExpressionDataBinding Expression="[fieldExtendedPrice] * (1 - [fieldDiscount])" />
    </DataBindingSerializable>
</dx:PivotGridField>

OLAP Mode

Follow the steps below to create a calculated field in OLAP mode.

1. Create an OLAPExpressionBinding instance and pass the expression to its constructor as a parameter. You can also use the object’s OLAPExpressionBindingBase.Expression property to specify the expression.
2. Assign the created object to the PivotGridFieldBase.DataBinding property.

The following code snippet shows how to bind the fieldSales field to the MDX expression:

WebForm1.aspx.cs

using System;
using DevExpress.XtraPivotGrid;
using DevExpress.Web.ASPxPivotGrid;
//...
PivotGridField fieldSales = new PivotGridField();
fieldSales.Caption = "Cleared Amount";
fieldSales.Area = PivotArea.DataArea;
fieldSales.DataBinding = new OLAPExpressionBinding("[Measures].[Internet Sales Amount] * 0.87");
ASPxPivotGrid1.Fields.Add(fieldSales);

WebForm1.aspx.vb

Imports System
Imports DevExpress.XtraPivotGrid
Imports DevExpress.Web.ASPxPivotGrid
'...
Private fieldSales As New PivotGridField()
fieldSales.Caption = "Cleared Amount"
fieldSales.Area = PivotArea.DataArea
fieldSales.DataBinding = New OLAPExpressionBinding("[Measures].[Internet Sales Amount] * 0.87")
ASPxPivotGrid1.Fields.Add(fieldSales)

Use Custom Functions in Expressions

You can create functions with custom logic to build an expression that executes complex calculations for a Pivot Grid field.

Refer to the following articles for information about custom functions: Custom Aggregate Functions

The following example specifies a custom summary for the First Product Sold field. The custom summary’s expression FirstValue([ProductName]) uses a custom aggregate function FirstValue to return the first product sold by a sales person in each product category.

View Example

WebForm1.aspx

<dx:PivotGridField ID="FirstSoldValue" Area="DataArea" AreaIndex="0" FieldName="FirstSoldProduct" 
    Caption ="First Sold Product" Options-ShowGrandTotal ="false " Options-ShowTotals ="False">
    <DataBindingSerializable>
        <dx:ExpressionDataBinding Expression= "FirstValue([ProductName])" />
    </DataBindingSerializable>
</dx:PivotGridField>