Bind Pivot Grid Fields to Calculated Expressions

• Aug 05, 2022
• 2 minutes to read

This topic describes how to use the Binding API to create calculated fields in OLAP mode.

Calculated fields display the result of calculated expressions. Each calculated field has a binding expression that can be a formula or an aggregate function. The expression allows you to not only obtain values from a field in the data source, but specify exactly how to calculate the data (for example, aggregate it).

Create a Calculated Field in Visual Studio Designer

Follow the steps below to create a calculated field in the Pivot Grid:

1. Add a new data field in any of the following ways:

   • Click ‘Run Designer’ in the PivotGrid’s smart tag menu. In the Pivot Grid Designer window, select the Fields page and click or to add a new field.

   • Use one of the ‘Add Field to…’ options in the PivotGrid’s smart tag menu to add a new field to the required area directly. Then, you can specify its properties in the property grid or in the field’s smart tag menu.

2. Set the field’s PivotGridFieldBase.DataBinding property to OLAP Expression.

3. Specify the MDX expression.

   

   For information about expression operators and functions, refer to the following topic: Pivot Grid Expression Syntax.

Note

You cannot use the Expression Editor dialog in OLAP mode.

Create a Calculated Field in Code

OLAP mode supports OLAPExpressionBinding.

Follow the steps below to create a calculated field in OLAP mode:

1. Create an OLAPExpressionBinding instance and pass the expression in its constructor as a parameter. You can also use the object’s OLAPExpressionBindingBase.Expression property to specify the expression.
2. Assign the created object to the PivotGridFieldBase.DataBinding property.

The following code snippet shows how to bind measureField to the MDX expression:

C#

PivotGridField measureField = new PivotGridField() { Caption = "Cleared Amount", 
   Area = PivotArea.DataArea };
measureField.DataBinding = new OLAPExpressionBinding("[Measures].[Internet Sales Amount] * 0.87");
measureField.Name = "fieldInternetSalesAmount";
pivotGridControl1.Fields.Add(measureField);

VB

Dim measureField As PivotGridField = New PivotGridField() With {.Caption = "Cleared Amount", .Area = PivotArea.DataArea}
measureField.DataBinding = New OLAPExpressionBinding("[Measures].[Internet Sales Amount] * 0.87")
measureField.Name = "fieldInternetSalesAmount"
pivotGridControl1.Fields.Add(measureField)

View Example