Follow the steps below to create a new unbound field in the PivotGrid control.
- Click 'Run Designer' on the PivotGrid's smart tag panel. In the PivotGrid Designer window, select the Fields page and click
or 
to add a new field. You can also use the Field collection editor from the PivotGridControl Properties window.
- The PivotGridFieldBase.UnboundFieldName property is assigned automatically. You can change this value, but make sure that its value is not equal to existing PivotGridFieldBase.Name or PivotGridFieldBase.FieldName values.
- Change the value of the field's PivotGridFieldBase.UnboundType property from Bound to a value type from the drop-down list.
-
Specify an expression to fill the field with data. Click the PivotGridField.UnboundExpression property's ellipsis button and enter the expression in the invoked Expression Editor:
Refer to the Pivot Grid Expression Syntax document for more information about the expression operators and functions.
To create an unbound field in code, create the PivotGridField instance, specify the field name, type, and other settings, and add the resulting object to the PivotGridControl.Fields collection.
The following code snippet demonstrates how to create an unbound field in code and use the PivotGridField.UnboundExpression property to supply data.
In this example, extended price values are calculated as follows:
[Quantity] * [UnitPrice] * (1 - [Discount]).
Pivot Grid field names are obtained using the PivotGridFieldBase.ExpressionFieldName property.
| C# |
PivotGridField fieldExtendedPrice = new PivotGridField() { Caption = "Extended Price", Area = PivotArea.DataArea };
fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice";
fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal;
fieldExtendedPrice.UnboundExpression = string.Format("[{0}] * [{1}] * (1 - [{2}])",
fieldQuantity.ExpressionFieldName, fieldUnitPrice.ExpressionFieldName, fieldDiscount.ExpressionFieldName);
pivotGridControl1.Fields.Add(fieldExtendedPrice);
}
|
| VB |
Dim fieldExtendedPrice As New PivotGridField() With {.Caption = "Extended Price", .Area = PivotArea.DataArea}
fieldExtendedPrice.UnboundFieldName = "fieldExtendedPrice"
fieldExtendedPrice.UnboundType = DevExpress.Data.UnboundColumnType.Decimal
fieldExtendedPrice.UnboundExpression = String.Format("[{0}] * [{1}] * (1 - [{2}])", fieldQuantity.ExpressionFieldName, fieldUnitPrice.ExpressionFieldName, fieldDiscount.ExpressionFieldName)
pivotGridControl1.Fields.Add(fieldExtendedPrice)
|
For OLAP, use the PivotGridFieldBase.OLAPExpression property to set the unbound expression:
| C# |
dimensionField.OLAPExpression = "topcount( [Product].[Product].[Product].members, 5, [Measures].[Sales Amount] ) ";
measureField.OLAPExpression = "[Measures].[Sales Amount] * 0.9";
pivotGridControl1.Fields.Add(dimensionField);
pivotGridControl1.Fields.Add(measureField);
|
| VB |
dimensionField.OLAPExpression = "topcount( [Product].[Product].[Product].members, 5, [Measures].[Sales Amount] ) "
measureField.OLAPExpression = "[Measures].[Sales Amount] * 0.9"
pivotGridControl1.Fields.Add(dimensionField);
pivotGridControl1.Fields.Add(measureField);
|
You can handle the PivotGridControl.CustomUnboundFieldData event for complex calculations that cannot be performed using expressions. The PivotGrid control fires this event for each unbound field and row in the data source.
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 using the formula 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 |
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
|
The PivotGridFieldBase.UnboundExpressionMode property allows you to change the unbound field expression's calculation mode. The following table lists the available modes:
|
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 the underlying UnitPrice and Quantity values.
After you add the created unbound field to the Data Header area, the Pivot Grid uses the specified summary function to summarize its data.
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 uses the displayed data to compute unbound field values.
To calculate a margin for a product, you have to divide its Profit value by its ExtendedPrice value.
In this mode, an expression uses summaries.
Note
Hidden data source fields or fields in the Filter Header area are not summarized and cannot participate in the calculation. To avoid this restriction, use the UnboundExpressionMode.UseAggregateFunctions mode. This mode , which allows you to include aggregate functions in an expression and use them on a data source level.
|
|
UnboundExpressionMode.UseAggregateFunctions
|
The UnboundExpressionMode.UseAggregateFunctions mode introduces a specified set of aggregate functions (Sum, Min, Max, etc.). These functions can be used to perform summary calculations on a data source level.
The following example illustrates the margin calculation. The sum of Profit values is divided by the sum of ExtendedPrice values.
|
|
UnboundExpressionMode.Default
|
The UnboundExpressionMode.Default setting indicates that the PivotGridOptionsData.DataFieldUnboundExpressionMode property value specifies the calculation mode. This setting allows you use the Pivot Grid's global option to control how unbound fields are calculated.
|
End-users can use the Expression Editorto edit unbound field expressions. To make this editor available to users, set the PivotGridFieldOptionsEx.ShowUnboundExpressionMenu property to true. This adds the Expression Editor... command to a field's context menu.
In code, use the PivotGridControl.ShowUnboundExpressionEditor method to invoke the editor.
Use the PivotGridOptionsBehavior.ExpressionEditorMode property to specify the Expression Editor’s version.