PivotGridControl.RetrieveFields(PivotArea, Boolean) Method

Creates PivotGridField objects for all columns in the bound data source, and moves the fields to the specified area, making them visible or hidden.

Namespace : DevExpress.XtraPivotGrid

Assembly : DevExpress.XtraPivotGrid.v25.2.dll

NuGet Package : DevExpress.Win.PivotGrid

csharp

public void RetrieveFields(
    PivotArea area,
    bool visible
)

Public Sub RetrieveFields(
    area As PivotArea,
    visible As Boolean
)

Parameters

Name	Type	Description
area	PivotArea

A PivotArea value that specifies the area to which the created fields are moved.

| | visible | Boolean |

true if the created fields are visible; otherwise, false.

Remarks

This overload clears the PivotGridControl.Fields collection and populates it with new PivotGridField objects, created for all columns in a data source.

The RetrieveFields method generates DataSourceColumnBinding objects for each Pivot Grid field in OLAP, Server, and Optimized modes. The Pivot Grid fields obtain their values from columns in the data source. The DataSourceColumnBindingBase.ColumnName property is set to the name of the data source column. The created fields are moved to the area specified by the area parameter. The visible parameter specifies whether these fields are created as visible or hidden.

Use the PivotGridControl.Fields property to add or remove fields. For instance, you can add a calculated field to a collection that contains arbitrary data in the Pivot Grid control. Refer to the following topic for more information about calculated fields: Bind Pivot Grid Fields to Calculated Expressions.

Use the PivotGridControl.RetrieveFieldsAsync method to create fields asynchronously.

Specify a 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.

Refer to the following topic for more information about Pivot Grid Fields: Pivot Grid Fields.

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:

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.AreaGets or sets the area in which the field is displayed.PivotGridFieldBase.AreaIndexGets or sets the field’s index from among the other fields displayed within the same area.PivotGridFieldBase.VisibleGets or sets whether the field is visible.

Use the invoked Customization Form to arrange fields.

View Example: How to Connect a Pivot Grid to an OLAP Data Source

C#
VB.NET

csharp

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();
        }
    }
}

Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraPivotGrid.Customization

Namespace WinOlapRetrieveFieldsExample

    Public Partial Class Form1
        Inherits DevExpress.XtraEditors.XtraForm

        Public Sub New()
            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()
        End Sub

        Private Sub btnRetrieveFields_Click(ByVal sender As Object, ByVal e As System.EventArgs)
            ' Retrieve fields.
            pivotGridControl1.RetrieveFields(PivotArea.ColumnArea, False)
            For Each field As PivotGridField In pivotGridControl1.Fields
                field.Name = "field" & TryCast(field.DataBinding, DataSourceColumnBinding).ColumnName
            Next

            ' 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()
        End Sub

        Private Sub btnCreateFields_Click(ByVal sender As Object, ByVal e As System.EventArgs)
            pivotGridControl1.BeginUpdate()
            pivotGridControl1.Fields.Clear()
            ' Create a field, specify its settings and bind the field to a measure or dimension.
            Dim fieldCountry As PivotGridField = pivotGridControl1.Fields.Add("Country", PivotArea.RowArea)
            fieldCountry.DataBinding = New DataSourceColumnBinding("[Customer].[Country].[Country]")
            fieldCountry.Name = "fieldCountry"
            Dim fieldCity As PivotGridField = pivotGridControl1.Fields.Add("City", PivotArea.RowArea)
            fieldCity.DataBinding = New DataSourceColumnBinding("[Customer].[City].[City]")
            fieldCity.Name = "fieldCity"
            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)
            Dim fieldTop10 As PivotGridFieldBase = 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()
        End Sub
    End Class
End Namespace

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the RetrieveFields(PivotArea, Boolean) method.

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.

C#
VB.NET

winforms-pivot-grid-connect-to-an-olap-datasource/CS/WinOlapRetrieveFieldsExample/Form1.cs#L25

csharp

// Retrieve fields.
pivotGridControl1.RetrieveFields(PivotArea.ColumnArea, false);
foreach (PivotGridField field in pivotGridControl1.Fields){

winforms-pivot-grid-connect-to-an-olap-datasource/VB/WinOlapRetrieveFieldsExample/Form1.vb#L26

' Retrieve fields.
pivotGridControl1.RetrieveFields(PivotArea.ColumnArea, False)
For Each field As PivotGridField In pivotGridControl1.Fields

PivotGridControl.RetrieveFields(PivotArea, Boolean) Method

PivotGridControl.RetrieveFields(PivotArea, Boolean) Method

Declaration

Parameters

Remarks

Example

Related GitHub Examples