Unbound Fields

• Nov 13, 2018
• 8 minutes to read

The Pivot Grid supports bound and unbound fields:

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

• Unbound fields can be supplied with data using custom logic. For instance, if the underlying data source contains employees’ first and last names, you can create an unbound field providing full names.

  

Unbound fields support the same data-related operations as bound fields: they can be sorted, grouped and filtered.

Note

Note that unbound fields are not supported in OLAP mode.

Tip

If you have the Demo Center installed, see the Unbound Expressions and Unbound Fields demos for more examples.

This topic consists of the following sections:

• Add an Unbound Field
• Provide Data Using the Expression
• Provide Data Using the Event
• Change Unbound Expression Mode
• Enable End-Users to Change Expressions
• Member Table

Add an Unbound Field

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.

Provide Data Using the Expression

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.NET

  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.NET

  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.

Provide Data Using the Event

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.

Note

The PivotGridControl.CustomUnboundFieldData event is not supported in the server mode.

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)

C#

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);
            }
        }
    }
}

VB.NET

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

Change Unbound Expression Mode

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:

Unbound Expression Mode	Description
UnboundExpressionMode.DataSource	The UnboundExpressionMode.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 summarizes its data using the specified summary function. In this case, unbound field values are calculated before summarization.
UnboundExpressionMode.UseSummaryValues	The UnboundExpressionMode.UseSummaryValues mode enables you to compute unbound field values on a visualization (or summary) level. In this case, the Pivot Grid computes unbound field values using displayed data. For instance, if you want to calculate a margin for different products, you need to divide Profit values by the corresponding ExtendedPrice values. In this case, unbound field values are calculated after summarization. Note Note that data source fields participating in the calculation should not be hidden or placed in the Filter Header area because their summary values are not calculated. To overcome this restriction, you can use the UnboundExpressionMode.UseAggregateFunctions mode, which allows you to apply various aggregate functions on a data source level.
UnboundExpressionMode.UseAggregateFunctions	The UnboundExpressionMode.UseAggregateFunctions mode expands the UnboundExpressionMode.UseSummaryValues mode by introducing a specified set of aggregate functions (Sum, Min, Max, etc.) that can be used to perform the required calculations on a data source level. For instance, if you want to calculate a margin, you need to divide the sum of Profit values by the sum of ExtendedPrice values. Note If you enable the UnboundExpressionMode.UseAggregateFunctions mode, the Aggregate function group is added to the Expression Editor available to end-users.

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.

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

Member Table

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

Property	Description
PivotGridFieldBase.UnboundType	Gets or sets the field’s data type and binding mode.
PivotGridFieldBase.UnboundFieldName	Gets or sets the name of a column in a summary data source that corresponds to the current unbound field.
PivotGridFieldBase.UnboundExpression	Gets or sets an expression used to evaluate the current unbound field’s values.
PivotGridFieldBase.IsUnboundExpressionValid	Gets whether the PivotGridFieldBase.UnboundExpression property’s value specifies a valid expression.
PivotGridFieldBase.ExpressionFieldName	Gets the field’s name in unbound expressions.
PivotGridFieldBase.UnboundExpressionMode	Gets or sets how the specified data field’s unbound expression is calculated.
PivotGridOptionsData.DataFieldUnboundExpressionMode	Gets or sets whether unbound expressions for pivot grid’s data fields are calculated based on the data source records or summary values.
PivotGridFieldOptionsEx.ShowUnboundExpressionMenu	Gets or sets whether an end-user can open an Expression Editor for the current unbound field using a context menu.
PivotGridField.CanShowUnboundExpressionMenu	Gets whether a menu used to open an Expression Editor for unbound fields is available.
PivotGridFieldOptions.ShowInExpressionEditor	Gets or sets whether the current field is available in the Expression Editor‘s field list.
Event	Description
PivotGridControl.CustomUnboundFieldData	Enables providing data to unbound fields.
PivotGridControl.FieldUnboundExpressionChanged	Fires after the PivotGridFieldBase.UnboundExpression property’s value has been changed.
Method	Description
PivotGridControl.ShowUnboundExpressionEditor	Invokes the Expression Editor that enables editing a specified unbound field’s expression.