PivotGridFieldBase.OLAPExpression Property
Gets or sets an expression used to evaluate values for the current unbound field in OLAP.
Namespace: DevExpress.XtraPivotGrid
Assembly: DevExpress.PivotGrid.v24.1.Core.dll
NuGet Packages: DevExpress.PivotGrid.Core, DevExpress.Win.Navigation
Declaration
[PivotAPIMemberCategory(APICategory.LegacyBinding)]
[PivotAPIMemberCategory(APICategory.OLAPSource)]
[XtraSerializableProperty]
public string OLAPExpression { get; set; }Property Value
| Type | Description |
|---|---|
| String | A string that represents an expression used to evaluate values for the current field in OLAP. |
Example
This example demonstrates how to specify connection settings and create fields that are bound to measures and dimensions of the cube on the OLAP server.
Follow the steps below to bind the Pivot Grid control to an OLAP cube in code.
- Set the PivotGridControl.OLAPDataProvider property to ADOMD.
Specify connection settings in the PivotGridControl.OLAPConnectionString property. The following connection string is used in this example:
Provider=MSOLAP;Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial catalog=Adventure Works DW Standard Edition;Cube name=Adventure Works;Query Timeout=100;Note that a valid connection string should include the following parameters: Provider, Data Source, Initial Catalog, Cube Name.
You can create Pivot Grid fields and bind them to measures and dimensions in the OLAP cube in the following ways:
Call the PivotGridControl.RetrieveFields method to create Pivot Grid fields for all columns in a data source and add them to the field’s collection. Use the PivotGridControl.GetFieldList method to obtain a list of fields available in a bound data source.
Create a PivotGridField object and add it to the PivotGridControl.Fields collection. Then, use DataSourceColumnBinding and OLAPExpressionBinding to bind the field to data.
Specify the value of the PivotGridFieldBase.Name property for each field when you create Pivot Grid fields. You can use this value to determine fields in a stored layout.
Use the following properties to specify field settings:
- PivotGridFieldBase.Area
- Gets or sets the area in which the field is displayed.
- PivotGridFieldBase.AreaIndex
- Gets or sets the field’s index from among the other fields displayed within the same area.
- PivotGridFieldBase.Visible
- Gets or sets whether the field is visible.
Use the invoked Customization Form to arrange fields.
using DevExpress.XtraPivotGrid;
using DevExpress.XtraPivotGrid.Customization;
namespace WinOlapRetrieveFieldsExample {
public partial class Form1 : DevExpress.XtraEditors.XtraForm {
public Form1() {
InitializeComponent();
// Specify the OLAP connection settings.
pivotGridControl1.OLAPDataProvider = OLAPDataProvider.Adomd;
pivotGridControl1.OLAPConnectionString =
@"Provider=MSOLAP;
Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;
Initial catalog=Adventure Works DW Standard Edition;
Cube name=Adventure Works;
Query Timeout=100;";
// Set the Customization Forms style.
pivotGridControl1.OptionsCustomization.CustomizationFormStyle = CustomizationFormStyle.Excel2007;
// Invoke the Customization Form.
pivotGridControl1.FieldsCustomization();
}
private void btnRetrieveFields_Click(object sender, System.EventArgs e) {
// Retrieve fields.
pivotGridControl1.RetrieveFields(PivotArea.ColumnArea, false);
foreach (PivotGridField field in pivotGridControl1.Fields){
field.Name = "field" + (field.DataBinding as DataSourceColumnBinding).ColumnName;
}
// Add fields from the Field List to the specified area to create a report.
pivotGridControl1.BeginUpdate();
pivotGridControl1.Fields["[Customer].[Country].[Country]"].Area = PivotArea.RowArea;
pivotGridControl1.Fields["[Customer].[Country].[Country]"].Visible = true;
pivotGridControl1.Fields["[Customer].[City].[City]"].Area = PivotArea.RowArea;
pivotGridControl1.Fields["[Customer].[City].[City]"].Visible = true;
pivotGridControl1.Fields["[Date].[Fiscal].[Fiscal Year]"].Area = PivotArea.ColumnArea;
pivotGridControl1.Fields["[Date].[Fiscal].[Fiscal Year]"].Visible = true;
pivotGridControl1.Fields["[Measures].[Internet Sales Amount]"].Visible = true;
pivotGridControl1.EndUpdate();
// Resize columns automatically.
pivotGridControl1.BestFit();
// Invoke the Customization Form.
pivotGridControl1.FieldsCustomization();
}
private void btnCreateFields_Click(object sender, System.EventArgs e) {
pivotGridControl1.BeginUpdate();
pivotGridControl1.Fields.Clear();
// Create a field, specify its settings and bind the field to a measure or dimension.
PivotGridField fieldCountry = pivotGridControl1.Fields.Add("Country", PivotArea.RowArea);
fieldCountry.DataBinding = new DataSourceColumnBinding("[Customer].[Country].[Country]");
fieldCountry.Name = "fieldCountry";
PivotGridField fieldCity = pivotGridControl1.Fields.Add("City", PivotArea.RowArea);
fieldCity.DataBinding = new DataSourceColumnBinding("[Customer].[City].[City]");
fieldCity.Name = "fieldCity";
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);
PivotGridFieldBase fieldTop10 = pivotGridControl1.Fields.Add("Top10", PivotArea.ColumnArea);
fieldTop10.DataBinding = new OLAPExpressionBinding("TOPCOUNT([Date].[Date].[Date].MEMBERS, 10, " +
"[Measures].[Internet Sales Amount])");
fieldTop10.Visible = false;;
fieldTop10.Name = "fieldTopcount";
pivotGridControl1.EndUpdate();
// Invoke the Customization Form.
pivotGridControl1.FieldsCustomization();
}
}
}
Related GitHub Examples
The following code snippet (auto-collected from DevExpress Examples) contains a reference to the OLAPExpression property.
Note
The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.