Unbound Fields

• Jun 21, 2022
• 9 minutes to read

Important

This article applies to the Legacy and LegacyOptimized calculation modes. The Optimized mode uses the Data Binding API.

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

Demo: Calculated Fields

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

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:

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

• Example 2

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.

• XML

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

• XAML
• C#
• VB.NET

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

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: