Binding to Data Overview

  • 4 min to read

The ASPxPivotGrid control should be connected to an external data source that provides data you wish to display and process. Like other data-bound web server controls, ASPxPivotGrid can be bound to any standard data source type, including SqlDataSource, ObjectDataSource, XmlDataSource and AccessDataSource. As an alternative, you can use specific DevExpress components to bind the Pivot Grid to data (such as the EntityServerModeDataSource or LinqServerModeDataSource components that can be used to perform data processing server side).

Bind a Pivot Grid to Data at Design Time

To bind an ASPxPivotGrid to a data source at design time, click its smart tag and select one of the following options.

ProvidingData_ChooseDatasource

  • The Choose Data Source combo box allows you to select the existing project data source.

    If necessary, you can create a new data source using the New Data Source... command.

  • Choose OLAP Data Source allows you to connect to a cube in a Microsoft Analysis Services server. To learn more, see OLAP Data Source.

After connecting the pivot grid to a data source, create the required fields. To do this, invoke the Fields and Groups page and click the Retrieve Fields button (the ASPxPivotGrid Designer - Retrieve Fields Button icon).

ASPxPivotGrid Designer - Fields and Groups Page

Automatically created fields are located in the Filter Header Area by default. To rearrange fields and locate them in the required areas, use the PivotGridFieldBase.Area property.

To learn more about pivot grid fields, see Fields.

Bind a Pivot Grid to Data in Code

To bind ASPxPivotGrid to a data source at runtime, assign the data source (for example, an AccessDataSource instance) to the ASPxPivotGrid.DataSource property.

You can create pivot grid fields automatically for all data source fields using the ASPxPivotGrid.RetrieveFields method, or populate the ASPxPivotGrid.Fields collection manually with PivotGridField instances bound to specific data source fields.

To learn more about how to create and arrange pivot grid fields, see Fields.

This example demonstrates how to create an ASPxPivotGrid and bind it to data in code.

In this example, the ASPxPivotGrid and System.Web.UI.WebControls.AccessDataSource instances are created and initialized in code. The Pivot Grid is bound to data by assigning the AccessDataSource instance to the ASPxPivotGrid.DataSource property. Then, the ASPxPivotGrid.RetrieveFields method is used to create pivot grid fields for all data source fields.

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;

namespace ASPxPivotGrid_RuntimeDataBinding {
    public partial class _Default : Page {
        private AccessDataSource ds;
        private ASPxPivotGrid ASPxPivotGrid1;
        protected override void OnLoad(EventArgs e) {
            base.OnLoad(e);

            // Initializes a data source.
            ds = new AccessDataSource("~/nwind.mdb", "SELECT [CategoryName]," +
                "[ProductName], [ProductSales], [ShippedDate] FROM [ProductReports]");

            // Initializes ASPxPivotGrid.
            ASPxPivotGrid1 = new ASPxPivotGrid();

            // Binds ASPxPivotGrid to the data source.
            ASPxPivotGrid1.DataSource = ds;

            // Places the Pivot Grid onto a page.
            form1.Controls.Add(ASPxPivotGrid1);

            if (ASPxPivotGrid1.Fields.Count != 0) 
                return;

            // Creates pivot grid fields for all data source fields.
            ASPxPivotGrid1.RetrieveFields();

            // Locates the pivot grid fields in appropriate areas.
            ASPxPivotGrid1.Fields["CategoryName"].Area = PivotArea.RowArea;
            ASPxPivotGrid1.Fields["ProductName"].Area = PivotArea.RowArea;
            ASPxPivotGrid1.Fields["ShippedDate"].Area = PivotArea.ColumnArea;
            ASPxPivotGrid1.Fields["ProductSales"].Area = PivotArea.DataArea;

            ASPxPivotGrid1.Fields["ShippedDate"].GroupInterval = PivotGroupInterval.DateYear;
        }
    }
}