This topic describes how to create and provide data for unbound fields. The following subsections comprise the topic.
To create an unbound field, follow the steps below.
Create a new Pivot Grid field (a PivotGridField object).
Set a unique field name using the PivotGridFieldBase.UnboundFieldName property.
To make the created field unbound, specify its PivotGridFieldBase.UnboundType property. To do this, select one of the UnboundColumnType values that correspond to the field's data type. Note, that if the field's PivotGridFieldBase.UnboundType property is set to UnboundColumnType.Bound, it's assumed that this field is not unbound, but rather bound to a specific field in the data source specified by the PivotGridFieldBase.FieldName property.
Add the created field to the PivotGridControl.Fields collection.
Now the unbound field is ready for use and you need to provide data for this field.
To calculate custom values based on other field values, you need to populate unbound fields with data. You can do this using two ways.
NoteProviding data for unbound fields is not supported in OLAP mode.
Providing Data using the CustomUnboundFieldData Event
The PivotGridControl.CustomUnboundFieldData event allows you to implement complex algorithms and provide data for unbound fields. The PivotGrid control fires the PivotGridControl.CustomUnboundFieldData event for each unbound field and for each row in the data source. The currently processed row is identified by the CustomFieldDataEventArgsBase<T>.ListSourceRowIndex property, which specifies the row's index in the data source. The current unbound field is identified by the CustomFieldDataEventArgsBase<T>.Field property. To provide a custom value, set the CustomFieldDataEventArgsBase<T>.Value property.
You may want to retrieve values from specific fields and rows in the data source. To do this, use the CustomFieldDataEventArgsBase<T>.GetListSourceColumnValue method.
How to: Add an Unbound Field to Supply Additional Data
Providing Data using an Unbound Expression
You can provide data for unbound fields by creating an expression using the PivotGridFieldBase.UnboundExpression property. You can use constants, various functions and operators in expressions. To see a full list of expression operators, refer to the Expression Operators, Functions and Constants topic. To refer to a bound field when creating an unbound expression, use the PivotGridFieldBase.FieldName property.
An end-user can edit a field's expression using the Expression Editor at runtime. For this, ensure that the PivotGridFieldOptionsEx.ShowUnboundExpressionMenu option is enabled. When changing the PivotGridFieldBase.UnboundExpression property, the PivotGridControl.FieldUnboundExpressionChanged event fires.
The following code demonstrates how to create a simple unbound field and populate it with data using an unbound expression.
| C# |
PivotGridField unboundField = new PivotGridField() { Caption = "Sum", Area = PivotArea.DataArea};
unboundField.UnboundFieldName = "unboundField1";
unboundField.UnboundType = UnboundColumnType.Decimal;
unboundField.UnboundExpression = "["+ fieldUnitPrice.FieldName + "]*["+ fieldQuantity.FieldName + "]";
pivotGridControl1.Fields.Add(unboundField);
|
Unbound expressions can be calculated in a different mode. To learn more, see the Unbound Expression Mode section.
Unbound expressions can be calculated in different ways.
- Against summary values
- Against values stored in the underlying data source
- Using aggregates
To specify the mode for a particular field, use the PivotGridFieldBase.UnboundExpressionMode property. By default, the unbound expression is calculated against each data source record, and then the resulting values are summarized. To calculate the expression against summary values, set the UnboundExpressionMode.UseSummaryValues mode. If you want to calculate an unbound expression using aggregates, set the PivotGridFieldBase.UnboundExpressionMode property to UnboundExpressionMode.UseAggregateFunctions.
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 'ExtendedPrice' and 'Quantity' are the names of the corresponding data source columns. The unbound expression is calculated against each data source record, and then the resulting values are summarized. The 'Price Per Unit' column value in the 'Gravad lax' row will be calculated as follows:
[ExtendedPrice] / [Quantity] = 20.8 / 1 + 582.4 / 28 + 208 / 10 + 421.2 / 18 + 1248 / 60 + 208 / 8 = 132.6
|
| UnboundExpressionMode.UseSummaryValues |
The 'fieldExtendedPrice' and 'fieldQuantity' are the names of the data fields shown in the image above. The expression is calculated against summary values, 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 expression is calculated using aggregates. The ExtendedPrice and Quantity are the names of corresponding data source columns.
Sum([ExtendedPrice]) / Sum([Quantity]) = (20.8 + 582.4 + 208 + 421.2 + 1248 + 208) / (1 + 28 + 10 + 18 + 60 + 8) = 2688.4 / 125 = 21.5
|