Unbound Fields

• Oct 12, 2020
• 9 minutes to read

Important

This article applies to the Legacy and LegacyOptimized calculation modes. The Optimized mode uses the Data Binding API.

The Pivot Grid supports bound and unbound fields:

• Bound fields retrieve data from the pivot grid’s underlying data source.

• Unbound fields get data by evaluating an expression or handling an event. You can use unbound fields to construct and display user-friendly values in field headers:

  

You can sort, group and filter unbound fields in the same manner as bound fields.

Run Demo: Unbound Expressions

Create an Unbound Field in Visual Studio Designer

Follow the steps below to create a new unbound field in the PivotGrid control.

1. Click the Fields (Collection) ellipsis button in the PivotGridControl Properties window. In the invoked PivotGridField Collection Editor: Fields window click Add to add a new field.
2. Specify the PivotGridField.UnboundFieldName property value. Make sure that it does not match any existing PivotGridField.Name or PivotGridField.FieldName values.
3. Change the value of the field’s PivotGridField.UnboundType property from Bound to a value type from the drop-down list.

4. Specify an expression to fill the field with data. Click the ellipsis button next to the PivotGridField.UnboundExpression property and enter the expression in the invoked Expression Editor:

   

   Refer to the Pivot Grid Expression Syntax document for more information about the expression operators and functions.

   Tip

   You can handle the CustomUnboundFieldData event to supply data to unbound fields.

Create an Unbound Field in XAML

This example creates a field with full names generated from the first and last names stored in the underlying data source. The code snippet below demonstrates how to create a field in markup:

<Grid>
    <dxpg:PivotGridControl x:Name="pivotGridControl1">
        <dxpg:PivotGridControl.Fields>
            <!-- ... -->
            <dxpg:PivotGridField x:Name="fieldFullName" Caption="Full Name"  Area="RowArea" AreaIndex="0" 
                                 UnboundFieldName="fieldFullName" UnboundType="String"
                                 UnboundExpression="[FirstName]+ ' ' + [LastName]" />
        </dxpg:PivotGridControl.Fields>
    </dxpg:PivotGridControl>
</Grid>

The UnboundFieldName should be unique across field names. Use the PivotGridField.UnboundExpression property to populate a field with data. Refer to the Pivot Grid Expression Syntax topic for more information about expressions.

For OLAP, use the OlapExpression property to set the unbound expression in XAML:

    <dxpg:x:Name="fieldPriceWithDiscount" Caption="Price with Discount"  Area="DataArea" AreaIndex="1"
                         OlapExpression="[Measures].[Internet Standard Product Cost] * 0.9" />

Create an Unbound Field in Code

This example creates a field with full names generated from the first and last names stored in the underlying data source. The code snippet below demonstrates how to create a field in code:

C#

using DevExpress.Xpf.PivotGrid;

public MainWindow() {
    // ... 
    var fieldFullName= new PivotGridField() { Caption = "Full Name", Area = FieldArea.RowArea }; 

    fieldFullName.UnboundFieldName = "fieldFullName";
    fieldFullName.UnboundType = FieldUnboundColumnType.Decimal;
    fieldFullName.UnboundExpression = "["+ fieldFirstName.ExpressionFieldName + "]+["+ fieldLastName.ExpressionFieldName + "]";

    pivotGridControl1.Fields.Add(fieldFullName);
}

VB.NET

Imports DevExpress.Xpf.PivotGrid

Public Sub New()
    ' ... 
    Dim fieldFullName As New PivotGridField() With {.Caption = "Full Name", .Area = FieldArea.RowArea}

    fieldFullName.UnboundFieldName = "fieldFullName" 
    fieldFullName.UnboundType = FieldUnboundColumnType.Decimal 
    fieldFullName.UnboundExpression = "[" & fieldFirstName.ExpressionFieldName & "]*[" & fieldLastName.ExpressionFieldName & "]" 

    pivotGridControl1.Fields.Add(fieldFullName)
End Sub

The UnboundFieldName should be unique across field names. Use the PivotGridField.UnboundExpression property to populate a field with data. Refer to the Pivot Grid Expression Syntax topic for more information on expressions.

Tip

To refer to a Pivot Grid field within an expression, use the name obtained from the PivotGridField.ExpressionFieldName property.

For OLAP, use the OlapExpression property to set an unbound expression:

C#

using DevExpress.Xpf.PivotGrid;

public MainWindow() {
    // ... 
    var fieldPriceWithDiscount= new PivotGridField() { Caption = "Price with Discount", Area = FieldArea.DataArea }; 

    fieldPriceWithDiscount.OlapExpression = "[Measures].[Internet Standard Product Cost] * 0.9";

    pivotGridControl1.Fields.Add(fieldPriceWithDiscount);
}

VB.NET

Imports DevExpress.Xpf.PivotGrid

Public Sub New()
    ' ... 
    Dim fieldPriceWithDiscount As New PivotGridField() With {.Caption = "Price with Discount", .Area = FieldArea.DataArea}

    fieldPriceWithDiscount.OlapExpression = "[Measures].[Internet Standard Product Cost] * 0.9";

    pivotGridControl1.Fields.Add(fieldPriceWithDiscount)
End Sub

Provide Data Using the Event

You can handle the PivotGridControl.CustomUnboundFieldData event for complex calculations that cannot be performed using expressions. The PivotGrid control fires this event for each unbound field and row in the data source.

This example demonstrates how to add an unbound field to the PivotGridControl, to show the total sum of an order.

The PivotGridControl is bound to the Order Details data table (from the nwind sample database), which contains UnitPrice, Quantity and Discount fields. The total sum is calculated as follows: UnitPriceQuantity(1-Discount).

To solve this task, create a PivotGrid’s field and set its PivotGridField.UnboundType property to FieldUnboundColumnType.Decimal. Then, handle the PivotGridControl.CustomUnboundFieldData event and populate the field with data.

View Example

MainWindow.xaml

<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
        Title="MainWindow" Height="600" Width="800" 
        x:Class="UnboundFieldExample.MainWindow"
        Loaded="Window_Loaded">
    <Grid>
        <dxpg:PivotGridControl CustomUnboundFieldData="pivotGridControl1_CustomUnboundFieldData" 
                               CustomCellValue="PivotGridControl1_CustomCellValue" 
                               Name="pivotGridControl1">
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Name="fieldOrderID" FieldName="OrderID" Area="RowArea"/>
                <dxpg:PivotGridField Name="fieldProductName" FieldName="ProductName" Area="RowArea"/>
                <dxpg:PivotGridField Name="fieldUnitPrice" FieldName="UnitPrice" Area="DataArea"/>
                <dxpg:PivotGridField Name="fieldQuantity" FieldName="Quantity" Area="DataArea"/>
                <dxpg:PivotGridField Name="fieldDiscount" FieldName="Discount" Area="DataArea" 
                                     CellFormat="p"/>
                <dxpg:PivotGridField Name="fieldTotalSum" UnboundType="Decimal" Area="DataArea"
                                     Caption="Total Sum"/>
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

MainWindow.xaml.vb

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid
Imports System

Namespace UnboundFieldExample
    Partial Public Class MainWindow
        Inherits Window

        Private dataTable As New DataSet1.OrderDetailsDataTable()
        Private dataAdapter As New DataSet1TableAdapters.OrderDetailsTableAdapter()

        Public Sub New()
            InitializeComponent()
            pivotGridControl1.DataSource = dataTable
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            dataAdapter.Fill(dataTable)
        End Sub

        Private Sub pivotGridControl1_CustomUnboundFieldData(ByVal sender As Object, ByVal e As PivotCustomFieldDataEventArgs)
            If e.Field Is fieldTotalSum Then
                Dim unitPrice As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue("UnitPrice"))
                Dim qty As Integer = Convert.ToInt32(e.GetListSourceColumnValue("Quantity"))
                Dim discount As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue("Discount"))
                e.Value = unitPrice * qty * (1 - discount)
            End If
        End Sub

        Private Sub PivotGridControl1_CustomCellValue(ByVal sender As Object, ByVal e As PivotCellValueEventArgs)
            If e.DataField Is fieldDiscount AndAlso e.RowValueType = FieldValueType.GrandTotal Then
                e.Value = String.Empty
            End If
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

using System.Windows;
using DevExpress.Xpf.PivotGrid;
using System;

namespace UnboundFieldExample
{
    public partial class MainWindow : Window {
        DataSet1.OrderDetailsDataTable dataTable = new DataSet1.OrderDetailsDataTable();
        DataSet1TableAdapters.OrderDetailsTableAdapter dataAdapter = new DataSet1TableAdapters.OrderDetailsTableAdapter();

        public MainWindow() {
            InitializeComponent();
            pivotGridControl1.DataSource = dataTable;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e) {
            dataAdapter.Fill(dataTable);
        }

        private void pivotGridControl1_CustomUnboundFieldData(object sender, 
                PivotCustomFieldDataEventArgs e) {
            if(e.Field == fieldTotalSum) {
                decimal unitPrice = Convert.ToDecimal(e.GetListSourceColumnValue("UnitPrice"));
                int qty = Convert.ToInt32(e.GetListSourceColumnValue("Quantity"));
                decimal discount = Convert.ToDecimal(e.GetListSourceColumnValue("Discount"));
                e.Value = unitPrice * qty * (1 - discount);
            }
        }

        private void PivotGridControl1_CustomCellValue(object sender, PivotCellValueEventArgs e)
        {
            if (e.DataField == fieldDiscount && e.RowValueType == FieldValueType.GrandTotal)
                e.Value = String.Empty;
        }
    }
}

Note

The PivotGridControl.CustomUnboundFieldData event is not supported in server and OLAP modes.

Change Unbound Expression Mode

The PivotGridField.UnboundExpressionMode property allows you to change the unbound field expression’s calculation mode. The default mode is that an expression is calculated against each data source record, and resulting values are summarized. Another option is calculation on a summary (visualization) level, when an expression uses the summarized (displayed in a pivot table) values. A sort of summary calculations involves a set of summary functions (such as Sum, Avg, etc.) to summarize data source records. The following table lists the available modes:

Unbound Expression Mode	Description
FieldUnboundExpressionMode.DataSource	The FieldUnboundExpressionMode.DataSource mode allows you to compute unbound field values on a data source level. In the image below, the ExtendedPrice field is calculated based on underlying UnitPrice and Quantity values. After you add the created unbound field to the Data Header area, the Pivot Grid uses the summary function to summarize its data. In this case, unbound field values are calculated before summarization.
FieldUnboundExpressionMode.UseSummaryValues	The FieldUnboundExpressionMode.UseSummaryValues mode enables you to compute unbound field values on a visualization (or summary) level. In this case, the Pivot Grid uses the displayed data to compute unbound field values. To calculate a margin for a product, you have to divide its Profit value by its ExtendedPrice value. In this mode, an expression uses summaries. Note Hidden data source fields or fields located in the Filter Header area are not summarized and cannot participate in the calculation. To overcome this restriction, you can use the FieldUnboundExpressionMode.UseAggregateFunctions mode. This mode allows you to include aggregate functions in an expression and use them on a data source level.
FieldUnboundExpressionMode.UseAggregateFunctions	The FieldUnboundExpressionMode.UseAggregateFunctions mode introduces a specified set of aggregate functions (Sum, Min, Max, etc.). These functions can be used to perform summary calculations on a data source level. The following example illustrates the margin calculation. The sum of Profit values is divided by the sum of ExtendedPrice values. Note If you enable the FieldUnboundExpressionMode.UseAggregateFunctions mode, the Aggregate function group appears in the Expression Editor available to end-users.
FieldUnboundExpressionMode.Default	The UnboundExpressionMode.Default setting indicates that the PivotGridOptionsData.DataFieldUnboundExpressionMode property value specifies the calculation mode. This setting allows you use the Pivot Grid’s global option to control how unbound fields are calculated.

Enable End-Users to Edit Expressions

End-users can use the Expression Editorto edit unbound field expressions. To make this editor available to users, set the PivotGridFieldOptionsEx.ShowUnboundExpressionMenu property to true. This adds the Expression Editor… command to a field’s context menu.

In code, use the PivotGridControl.ShowUnboundExpressionEditor method to invoke the editor.

Use the PivotGridOptionsBehavior.ExpressionEditorMode property to specify the Expression Editor’s version.

Enable End-Users to Change Expressions

You can supply end-users with the capability to change specific unbound field expressions using the Expression Editor. To do this, set the PivotGridField.AllowUnboundExpressionEditor property to true. This adds the Expression Editor… command to this field’s context menu.

You can also call the PivotGridControl.ShowUnboundExpressionEditor method to invoke the Expression Editor.

Member Table

The table below lists members related to creating and using unbound fields:

Property	Description
PivotGridField.UnboundType	Gets or sets the data type and binding mode of the field. This is a dependency property.
PivotGridField.UnboundFieldName	Gets or sets the name of a column in a summary data source that corresponds to the current unbound field. This is a dependency property.
PivotGridField.ExpressionFieldName	Gets the name by which the field is referred to in unbound expressions.
PivotGridField.UnboundExpressionMode	Gets or sets how the unbound expression for the specified data field is calculated.
PivotGridControl.DataFieldUnboundExpressionMode	Gets or sets whether unbound expressions for data fields are calculated based on the data source records, or summary values. This is a dependency property.
PivotGridField.AllowUnboundExpressionEditor	Gets or sets whether end-users can invoke the Expression Editor for the field. This is a dependency property.
PivotGridField.ShowInExpressionEditor	Gets or sets whether the current field is available in the field list in the Expression Editor.
Event	Description
PivotGridControl.CustomUnboundFieldData	Enables providing data to unbound fields.
PivotGridControl.FieldUnboundExpressionChanged	Fires after the PivotGridField.UnboundExpression property’s value has been changed.
PivotGridControl.UnboundExpressionEditorCreated	Fires after an Expression Editor has been created for an unbound field.
Method	Description
PivotGridControl.ShowUnboundExpressionEditor	Invokes the Expression Editor used to create expressions for unbound fields.