[Expand]General Information
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
   Getting Started
  [Expand]Grid View
  [Expand]Tree List
  [Expand]Card View
  [Expand]Reporting
  [Expand]Chart Control
  [Collapse]Pivot Grid
   [Expand]Getting Started
   [Expand]Fundamentals
   [Collapse]Binding to Data
     Binding to Data Overview
    [Expand]OLAP Data Source
    [Expand]Database Server Mode
     Unbound Fields
   [Expand]Data Shaping
   [Expand]Data Analysis
   [Expand]Data Formatting
   [Expand]Layout
   [Expand]Appearance
   [Expand]Export
   [Expand]Design-Time Features
   [Expand]UI Elements
   [Expand]Examples
   [Expand]End-User Capabilities
   [Expand]Member Tables
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Scheduler
  [Expand]Site Navigation and Layout
  [Expand]HTML Editor
  [Expand]Gauges
  [Expand]Vertical Grid
  [Expand]Data Editors
  [Expand]Docking and Popups
  [Expand]File Management
  [Expand]Data and Image Navigation
  [Expand]Multi-Use Site Controls
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Localization
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Unbound Fields

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.

Tip

See the ASP.NET Pivot Grid Unbound Expressions demo to get more examples.

This topic consists of the following sections:

Expanded Add an Unbound Field

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

  1. Add a new field to the ASPxPivotGrid. For this, select the required area and click the button on the ASPxPivotGrid Designer's Fields and Groups page.

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

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

  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 ASPxPivotGrid.Fields collection.

Expanded Calculate Unbound 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 PivotGridFieldBase.UnboundExpression property:

Then enter 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. For details on syntax, refer to the Pivot Grid Expression Syntax topic.

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 PivotGridFieldBase.UnboundExpression property. In this example, full names are generated based on the first and last names stored in the underlying data source.

    The snippet below shows how to set the same expression in XAML.

  • Example 2

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

    The snippet below shows how to set the same expression in XAML.

For OLAP, use the PivotGridFieldBase.OLAPExpression property to set the unbound expression:

The snippet below shows how to set the same expression in XAML.

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

Expanded Provide Data Using the Event

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

Note

The ASPxPivotGrid.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 ASPxPivotGrid.CustomUnboundFieldData event. In this example, price values with discount are calculated manually as follows: Extended Price * (1-Discount).

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

To specify the unbound expressions mode for all data fields, use the PivotGridOptionsData.DataFieldUnboundExpressionMode property.

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 will be calculated depending on unbound expression mode.

UnboundExpressionMode Enumeration Value Description
UnboundExpressionMode.DataSource The UnboundExpressionMode.DataSource mode allows you to compute field values on a 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

As you may notice, the resulting value does not make sense. The 'Price Per Unit' value should not be summarized, instead it should be calculated against summarized values.

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.

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

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 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]) = (20.8 + 582.4 + 208 + 421.2 + 1248 + 208) / (1 + 28 + 10 + 18 + 60 + 8) = 2688.4 / 125 = 21.5

UnboundExpressionMode.Default The UnboundExpressionMode.Default setting applies the summary or data source mode, as specified by thePivotGridOptionsData.DataFieldUnboundExpressionMode property accessible using pivotGrid.OptionsData.DataFieldUnboundExpressionMode notation. This setting allows you to control how unbound fields are calculated using a global Pivot Grid's option.

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

Expanded 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 unbound field's value.
PivotGridFieldBase.IsUnboundExpressionValid Gets whether the 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.
PivotGridFieldOptions.ShowInExpressionEditor Gets or sets whether the current field is available in the Expression Editor's field list.

Event

Description

ASPxPivotGrid.CustomUnboundFieldData Enables providing data to unbound fields.
ASPxPivotGrid.FieldUnboundExpressionChanged Occurs after an unbound field expression has been changed.

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