Unbound Fields

Dec 04, 2020
9 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.

asp-pivot-unbound-fields-example

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.

Add an Unbound Field

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

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.
Assign a unique field name to the PivotGridFieldBase.UnboundFieldName property. Note that the PivotGridFieldBase.UnboundFieldName property value should not match any existing PivotGridFieldBase.FieldName.
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.
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.

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:

asp-pivot-unbound-fields-set-expression

Then enter the required expression in the invoked Expression Editor:

asp-pivot-unbound-fields-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.

C#
VB.NET

using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
// ...
protected void Page_Load(object sender, EventArgs e) {
    // ...  
    var fieldFullName = new PivotGridField() { Caption = "Sum", Area = PivotArea.DataArea }; ;

    fieldFullName.UnboundFieldName = "fieldFullName1";
    fieldFullName.UnboundType = DevExpress.Data.UnboundColumnType.Decimal;
    fieldFullName.UnboundExpression = "["+ fieldFirstName.ExpressionFieldName + "]" + "+" + "' '" + "+" + "["+ fieldLastName.FieldName + "]";

    ASPxPivotGrid1.Fields.Add(fieldFullName);
}

Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
' ... 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    ' ...  
    Dim fieldFullName = New PivotGridField() With {.Caption = "Sum", .Area = PivotArea.DataArea}

    fieldFullName.UnboundFieldName = "fieldFullName1"
    fieldFullName.UnboundType = DevExpress.Data.UnboundColumnType.Decimal
    fieldFullName.UnboundExpression = "[" & fieldFirstName.ExpressionFieldName & "]" & "+" & "' '" & "+" & fieldLastName.ExpressionFieldName & "]" 

    ASPxPivotGrid1.Fields.Add(fieldFullName)
End Sub

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

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server"> 
    <Fields> 
        <!-- ... --> 
        <dx:PivotGridField ID="fieldFullName" Caption="Sum"  Area="DataArea" AreaIndex="1" 
                           UnboundFieldName="fieldFullName1" UnboundType="Decimal"
                           UnboundExpression="[FirstName] + ' ' + [LastName]" /> 
    </Fields> 
</dx:ASPxPivotGrid >

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

C#
VB.NET

using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
// ...
protected void Page_Load(object sender, EventArgs e) {
    // ...  
    var fieldExtendedPrice = new PivotGridField() { Caption = "Sum", Area = PivotArea.DataArea };

    fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice1";
    fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal;
    fieldExtendedPrice.UnboundExpression = "["+ fieldUnitPrice.ExpressionFieldName + "] * [" + fieldQuantity.ExpressionFieldName + "] * (1 - [" + fieldDiscount.ExpressionFieldName + "]";

    ASPxPivotGrid1.Fields.Add(fieldExtendedPrice);
}

Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
' ... 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    ' ...  
    Dim fieldExtendedPrice = New PivotGridField() With {.Caption = "Sum", .Area = PivotArea.DataArea}

    fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice1"
    fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal
    fieldExtendedPrice.UnboundExpression = "[" & fieldUnitPrice.ExpressionFieldName & "]*[" & fieldQuantity.ExpressionFieldName & "] * (1 - [" & fieldDiscount.ExpressionFieldName & "]"

    ASPxPivotGrid1.Fields.Add(fieldExtendedPrice)
End Sub

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

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server"> 
    <Fields> 
        <!-- ... --> 
        <dx:PivotGridField ID="fieldExtendedPrice" Caption="Sum"  Area="DataArea" AreaIndex="1" 
                           UnboundFieldName="fieldExtendedPrice1" UnboundType="Decimal"
                           UnboundExpression="[UnitPrice] * [Quantity] * (1 - [Discount])" /> 
    </Fields> 
</dx:ASPxPivotGrid >

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

C#
VB.NET

dimensionField.OLAPExpression = "topcount( [Product].[Product].[Product].members, 5, [Measures].[Sales Amount] ) ";
measureField.OLAPExpression = "[Measures].[Sales Amount] * 0.9";

ASPxPivotGrid1.Fields.Add(dimensionField);
ASPxPivotGrid1.Fields.Add(measureField);

dimensionField.OLAPExpression = "topcount( [Product].[Product].[Product].members, 5, [Measures].[Sales Amount] ) "
measureField.OLAPExpression = "[Measures].[Sales Amount] * 0.9"

ASPxPivotGrid1.Fields.Add(dimensionField)
ASPxPivotGrid1.Fields.Add(measureField)

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

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server"> 
    <Fields> 
        <!-- ... --> 
        <dx:PivotGridField ID="dimensionField" Caption="Top Count"  Area="RowArea" AreaIndex="1" 
                           OLAPExpression = "topcount( [Product].[Product].[Product].members, 5, [Measures].[Sales Amount] ) " />  
        <dx:PivotGridField ID="measureField" Caption="Discount Price"  Area="DataArea" AreaIndex="1" 
                           OLAPExpression = "[Measures].[Sales Amount] * 0.9" /> 
    </Fields> 
</dx:ASPxPivotGrid >

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

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="SqlDataSource1"
     OnCustomUnboundFieldData="CustomUnboundFieldData">
    <Fields>
            </dx:PivotGridField>
        <dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0" 
              FieldName="CategoryName" Name="fieldCategoryName">
            </dx:PivotGridField>
        <dx:PivotGridField ID="fieldExtendedPrice" Area="DataArea"
              AreaIndex="0" FieldName="Extended Price">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldDiscount" Area="DataArea"
              AreaIndex="1" FieldName="Discount">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldPriceWithDiscount" Area="DataArea"
              AreaIndex="3" UnboundType="Decimal"
              FieldName="PriceWithDiscount"
              Caption="Price with Discount">
        </dx:PivotGridField>
    </Fields>
</dx:ASPxPivotGrid>

using System;
using System.Web.UI;
using DevExpress.Web.ASPxPivotGrid;

namespace AddUnboundField {
    public partial class _Default : Page {
        protected void Page_Load(object sender, EventArgs e) {
        }
        protected void CustomUnboundFieldData(object sender, CustomFieldDataEventArgs e) {
            if (e.Field.FieldName != "PriceWithDiscount") return;
            decimal extPrice = Convert.ToDecimal(e.GetListSourceColumnValue("Extended Price"));
            decimal discount = Convert.ToDecimal(e.GetListSourceColumnValue("Discount"));
            e.Value = extPrice * (1 - discount);
        }
    }
}

Imports Microsoft.VisualBasic
Imports System
Imports System.Web.UI
Imports DevExpress.Web.ASPxPivotGrid

Namespace AddUnboundField
    Partial Public Class _Default
        Inherits Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        End Sub
        Protected Sub CustomUnboundFieldData(ByVal sender As Object, ByVal e As CustomFieldDataEventArgs)
            If e.Field.FieldName <> "PriceWithDiscount" Then
                Return
            End If
            Dim extPrice As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue("Extended Price"))
            Dim discount As Decimal = Convert.ToDecimal(e.GetListSourceColumnValue("Discount"))
            e.Value = extPrice * (1 - discount)
        End Sub
    End Class
End Namespace

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

asp-pivot-unbound-fields-mode

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.

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

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Add an Unbound Field

Calculate Unbound Data Using the Expression

Provide Data Using the Event

Unbound Expression Mode

Member Table