Unbound Fields

To create a new unbound field in the PivotGrid control, perform the steps below:

1. Add a new field to the PivotGridControl. For this, click the or button on the PivotGrid Designer's Fields page. You can also use the 'Add Field To ...' commands on the PivotGrid's smart tag panel.

2. Assign a unique field name to the PivotGridFieldBase.UnboundFieldName property. Note that the PivotGridFieldBase.UnboundFieldName property value should not match any existing PivotGridFieldBase.Name or PivotGridFieldBase.FieldName.

3. Change the value of the field's PivotGridFieldBase.UnboundType property from Bound to the required value type (String, Decimal, etc.).

4. Supply data to the created field (select one):

   • calculate unbound field values using an expression;
   • provide unbound data using the CustomUnboundFieldData event.

To add the unbound field in code, create the PivotGridField object, specify settings mentioned above and add the resulting object to the PivotGridControl.Fields collection.

After you have created a new unbound field, you can supply it with data by specifying a string expression. Specify the expression at design time by clicking the ellipsis button next to the PivotGridField.UnboundExpression property and entering the required expression in the invoked Expression Editor:

In the image above, the employee's full name is generated by concatenating the first and last names. In the Expression Editor, you can use other fields, constants, functions, and operators.

The examples below show how to create unbound fields in code and supply them with data using expressions. Note that the PivotGridFieldBase.ExpressionFieldName property is utilized for other fields within an expression.

• Example 1

  The following code snippet demonstrates how to create an unbound field in code and supply this field with data using the PivotGridField.UnboundExpression property. In this example, full names are generated based on the first and last names stored in the underlying data source.

  C#

  using DevExpress.XtraPivotGrid; public Form1() { // ... PivotGridField fieldFullName = new PivotGridField() { Caption = "Full Name", Area = PivotArea.RowArea }; fieldFullName.UnboundFieldName = "fieldFullName"; fieldFullName.UnboundType = DevExpress.Data.UnboundColumnType.String; fieldFullName.UnboundExpression = "[" + fieldFirstName1.ExpressionFieldName + "]" + "+" + "' '" + "+" + "[" + fieldLastName1.ExpressionFieldName + "]"; pivotGridControl1.Fields.Add(fieldFullName); }

  VB

  Imports DevExpress.XtraPivotGrid Public Sub New() ' ... Dim fieldFullName As New PivotGridField() With {.Caption = "Full Name", .Area = PivotArea.RowArea} fieldFullName.UnboundFieldName = "fieldFullName"; fieldFullName.UnboundType = DevExpress.Data.UnboundColumnType.String fieldFullName.UnboundExpression = "[" & fieldFirstName1.ExpressionFieldName & "]" & "+" & "' '" & "+" & "[" & fieldLastName1.ExpressionFieldName & "]" pivotGridControl1.Fields.Add(fieldFullName) End Sub

• Example 2

  The following code snippet demonstrates how to create an unbound field in code and supply it with data using the PivotGridField.UnboundExpression property. In this example, extended price values are calculated according to the following expression: [Quantity] * [UnitPrice] * (1 - [Discount])

  C#

  using DevExpress.XtraPivotGrid; public Form1() { // ... PivotGridField fieldExtendedPrice = new PivotGridField() { Caption = "Extended Price", Area = PivotArea.DataArea }; fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice"; fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal; fieldExtendedPrice.UnboundExpression = "[" + fieldQuantity.ExpressionFieldName + "] * [" + fieldUnitPrice.ExpressionFieldName + "] * (1 - [" + fieldDiscount.ExpressionFieldName + "])"; pivotGridControl1.Fields.Add(fieldExtendedPrice); }

  VB

  Imports DevExpress.XtraPivotGrid Public Sub New() ' ... Dim fieldExtendedPrice As New PivotGridField() With {.Caption = "Extended Price", .Area = PivotArea.DataArea} fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice" fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal fieldExtendedPrice.UnboundExpression = "[" & fieldQuantity.ExpressionFieldName & "] * [" & fieldUnitPrice.ExpressionFieldName & "] * (1 - [" & fieldDiscount.ExpressionFieldName & "])" pivotGridControl1.Fields.Add(fieldExtendedPrice) End Sub

Note that unbound fields can be calculated at different levels such as data source or summary levels. See Unbound Expression Modes for more information.

If you need complex a calculation that cannot be performed using expressions, handle the PivotGridControl.CustomUnboundFieldData event. The PivotGrid control fires this event for each unbound field and each row in the data source.

The code snippet below demonstrates how to create an unbound field in code and supply it with data using the PivotGridControl.CustomUnboundFieldData event. In this example, extended price values are calculated manually as follows: UnitPrice * Quantity * (1-Discount)

using DevExpress.XtraPivotGrid;

// ...
        public Form1() {
            // ...
            pivotGridControl1.CustomUnboundFieldData += PivotGridControl1_CustomUnboundFieldData;

            PivotGridField fieldExtendedPrice = new PivotGridField() { Caption = "Extended Price", Area = PivotArea.DataArea };
            fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice";
            fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal;

            pivotGridControl1.Fields.Add(fieldExtendedPrice);
        }

        private void pivotGridControl1_CustomUnboundFieldData(object sender, CustomFieldDataEventArgs e) {
            if (e.Field.UnboundFieldName == "fieldExtendedPrice") {
                decimal unitPrice = Convert.ToDecimal(e.GetListSourceColumnValue(fieldUnitPrice.ExpressionFieldName));
                int qty = Convert.ToInt32(e.GetListSourceColumnValue(fieldQuantity.ExpressionFieldName));
                decimal discount = Convert.ToDecimal(e.GetListSourceColumnValue(fieldDiscount.ExpressionFieldName));
                e.Value = unitPrice * qty * (1 - discount);
            }
        }
    }
}

Imports DevExpress.XtraPivotGrid

' ...
        Public Sub New()
            ' ...

            AddHandler pivotGridControl1.CustomUnboundFieldData, AddressOf PivotGridControl1_CustomUnboundFieldData
            
            Dim fieldExtendedPrice As New PivotGridField() With {.Caption = "Extended Price", .Area = PivotArea.DataArea}
            fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice"
            fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal

            pivotGridControl1.Fields.Add(fieldExtendedPrice)
        End Sub

        Private Sub pivotGridControl1_CustomUnboundFieldData(ByVal sender As Object, ByVal e As CustomFieldDataEventArgs)
            If e.Field.UnboundFieldName = "fieldExtendedPrice" Then
                Dim unitPrice As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue(fieldUnitPrice.ExpressionFieldName))
                Dim qty As Integer = Convert.ToInt32(e.GetListSourceColumnValue(fieldQuantity.ExpressionFieldName))
                Dim discount As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue(fieldDiscount.ExpressionFieldName))
                e.Value = unitPrice * qty * (1 - discount)
            End If
        End Sub
    End Class
End Namespace

By default, the unbound expression is calculated against each data source record, and then the resulting values are summarized. If necessary, you can calculate unbound field values on a visualization (or summary) level. You can also use a specified set of summary functions (such as Sum, Avg, etc.) to summarize data source records. The PivotGridFieldBase.UnboundExpressionMode property allows you to specify one of the following modes used to calculate unbound field values:

Use the PivotGridOptionsData.DataFieldUnboundExpressionMode property to specify the unbound expressions mode for all data fields. Note that the PivotGridFieldBase.UnboundExpressionMode property takes priority over the global PivotGridOptionsData.DataFieldUnboundExpressionMode.

You can supply end-users with the capability to change specific unbound field expressions using the Expression Editor. To do this, set the PivotGridFieldOptionsEx.ShowUnboundExpressionMenu 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.

The PivotGrid control supports two versions of the Expression Editor. To specify which Expression Editor's type is used to modify expressions for unbound fields by end-users, use the PivotGridOptionsBehavior.ExpressionEditorMode property.

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