Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]Welcome to DevExpress .NET Documentation
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Collapse]Pivot Grid
   [Expand]Getting Started
   [Expand]Fundamentals
   [Collapse]Binding to Data
     Binding to Data Overview
    [Expand]Items Source Configuration Wizard
    [Expand]OLAP Data Source
    [Expand]Database Server Mode
     Unbound Fields
     Local Data Stores
     Asynchronous Mode
   [Expand]Data Shaping
   [Expand]Data Analysis
   [Expand]Layout
   [Expand]Printing and Exporting
   [Expand]Appearance
   [Expand]MVVM Enhancements
   [Expand]Miscellaneous
   [Expand]UI Elements
   [Expand]End-User Capabilities
   [Expand]Examples
  [Expand]Scheduler
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Diagram Control
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Unbound Fields

The PivotGrid control 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 example, 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 and can be sorted, grouped and filtered. Note that the OLAP mode does not support unbound fields.

Tip

If you have the Demo Center installed, see the Unbound Expressions demo for more examples.

This topic consists of the following sections:

Expanded Add Unbound Fields

To add an unbound field to the PivotGridControl.Fields collection, follow the steps below:

  1. Create a new PivotGridField.
  2. Assign a unique field name to the PivotGridField.UnboundFieldName property. Note that the PivotGridField.UnboundFieldName property value should not match any existing PivotGridField.Name or PivotGridField.FieldName.
  3. Change the value of the field's PivotGridField.UnboundType property from Bound to the required value type to indicate this field's data type (String, Decimal, etc.).
  4. Supply data to the created field (select one):

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

Expanded Provide Data Using the Expression

You can set specific expressions to provide data to an unbound field. For details on syntax, refer to the Expression Operators, Functions, and Constants topic.

At design-time, you can specify the expression using the built-in Expression Editor. To invoke it, open the field's smart tag menu (the icon) and click the PivotGridField.UnboundExpression property's ellipsis button. In the invoked editor you can use constants, various functions and operators.

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

  • Example 1

    In this example, full names are generated based on the first and last names stored in the underlying data source. The code snippet below demonstrates how to set this expression in markup:

    The following code snippet shows how to set the same expression in code:

  • Example 2

    In this example, extended price values are calculated according to the following expression: [Quantity] * [UnitPrice] * (1 - [Discount]). The code snippet below demonstrates how to set this expression in markup:

    The following code snippet shows how to set the same expression in code:

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

Expanded Provide Data Using the Event

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

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=E2122.

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

Here the PivotGridControl is bound to the Order Details data table (from the nwind sample database), which contains UnitPrice, Quantity and Discount fields; there is no field which represents the total sum. However this can be manually calculated as follows: UnitPrice*Quantity*(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 manually populate the field with data.

Note

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

Expanded Change Unbound Expression Mode

The unbound expression is calculated against each data source record, and then the resulting values are summarized, by default. Change the PivotGridField.UnboundExpressionMode property value if you need to calculate an unbound expression against summary values or using aggregates.

Use the PivotGridControl.DataFieldUnboundExpressionMode property to specify the unbound expressions mode for all data fields.

The image below shows the PivotGrid control with the drill down form demonstrating underlying data source records corresponding to a processed cell. The 'Price Per Unit' column is unbound, and the expression is specified as [Extended Price] / [Quantity].

The following table demonstrates how the unbound expression is calculated depending on the unbound expression mode:

UnboundExpressionMode Enumeration Value Description
FieldUnboundExpressionMode.DataSource

The FieldUnboundExpressionMode.DataSource mode allows you to compute field values on the data source level. The unbound field is calculated based on underlying bound field values used in the expression.

For example, the 'Price Per Unit' column value in the 'Gravad lax' row in this mode is calculated as follows:

[ExtendedPrice] / [Quantity] = 20.8 / 1 + 582.4 / 28 + 208 / 10 + 421.2 / 18 + 1248 / 60 + 208 / 8 = 132.6

FieldUnboundExpressionMode.UseSummaryValues

The FieldUnboundExpressionMode.UseSummaryValues mode allows 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.

In this case, the result of the expression above is calculated as follows:

[fieldExtendedPrice] / [fieldQuantity] = (20.8 + 582.4 + 208 + 421.2 + 1248 + 208) / (1 + 28 + 10 + 18 + 60 + 8) = 2688.4 / 125 = 21.5

FieldUnboundExpressionMode.UseAggregateFunctions The FieldUnboundExpressionMode.UseAggregateFunctions mode expands the FieldUnboundExpressionMode.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 example, to calculate the price per unit, you can divide the sum of ExtendedPrice values by the sum of Quantity values.

Sum([ExtendedPrice]) / Sum([Quantity]) = Sum(20.8 + 582.4 + 208 + 421.2 + 1248 + 208) / Sum(1 + 28 + 10 + 18 + 60 + 8) = 2688.4 / 125 = 21.5

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

If the PivotGridField.UnboundExpressionMode property is set to FieldUnboundExpressionMode.UseSummaryValues, unbound expressions evaluated against hidden fields and fields in the Filter Area return the 'Error' value because summary values for these fields are not calculated.

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

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

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

Is this topic helpful?​​​​​​​