Binding to Data Overview
- 8 minutes to read
The PivotGridControl should be connected to an external data source that provides data you wish to display and process. You can bind a PivotGridControl to various types of data sources (an SQL database, an XML file, an OLAP cube, etc.).
To display data within the PivotGridControl, create Pivot Grid fields that are used to visualize data from specific data source fields.
Note
The PivotGridControl is not automatically updated when the data source is modified. You need to call the PivotGridControl.RefreshData method that will forcibly recalculate summaries.
Bind a Pivot Grid to Data at Design Time
Important
You cannot bind the Pivot Grid to data at design time in .NET 5+ projects.
To bind the PivotGridControl to data, click its smart tag and select one of the following options.
- The Items Source Wizard command allows you to invoke the Items Source Configuration Wizard, which can be used to create various types of data sources.
The DataSource option allows you to bind the PivotGridControl.DataSource property to the required object. To do this, click the
button, which opens the following Binding Dialog.
To learn more about this dialog, see Quick Actions.
Bind a Pivot Grid to Data in Code
The PivotGridControl exposes the PivotGridControl.DataSource property used to specify the data source for the Pivot Grid. The following topic lists typical data sources to which the data-aware DevExpress controls can be bound: Traditional Data Binding Methods.
To learn how to bind the PivotGridControl to various types of data sources in code, see examples from the Items Source Configuration Wizard section.
The following example demonstrates how to bind the PivotGridControl to a “SalesPerson” view in the nwind.mdb database, which ships with the installation.
Follow the steps below to connect the Pivot Grid to a database.
- Create an OleDbConnection object and specify the connection string in its constructor.
- Create an
OleDbDataAdapterinstance to select records from the data source. - Create a new
DataSetobject and populate it with data. - Use the PivotGridControl.DataSource property to assign the resulting data source to the Pivot Grid.
Follow the steps below to create and configure Pivot Grid fields.
- Create a PivotGridField object and add it to the PivotGridControl.Fields collection.
- Specify the field’s area and position within this area. For this, use the PivotGridFieldBase.Area and PivotGridField.AreaIndex properties.
AreaIndexcan be set only after the field is added to the control’s field collection. - Create a DataSourceColumnBinding object for each field.
- Set the DataSourceColumnBinding.ColumnName property to the name of the column in the data source. The Pivot Grid fields obtain their values from columns in the data source.
- Assign the
DataSourceColumnBindingobject to the field’s PivotGridField.DataBinding property.
Note that if you want to see an example of how to add pivot grid fields in XAML, please refer to the following tutorial: How to: Bind a PivotGrid to an MS Access Database.
using System.Data;
using System.Data.OleDb;
using System.Windows;
using DevExpress.Xpf.PivotGrid;
namespace HowToBindToMDB {
public partial class MainWindow : Window {
public MainWindow() {
InitializeComponent();
}
private void Window_Loaded(object sender, RoutedEventArgs e) {
// Create a connection object.
OleDbConnection connection =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB");
// Create a data adapter.
OleDbDataAdapter adapter =
new OleDbDataAdapter("SELECT * FROM SalesPerson", connection);
// Create and fill a dataset.
DataSet sourceDataSet = new DataSet();
adapter.Fill(sourceDataSet, "SalesPerson");
// Assign the data source to the PivotGrid control.
pivotGridControl1.DataSource = sourceDataSet.Tables["SalesPerson"];
pivotGridControl1.DataProcessingEngine = DataProcessingEngine.Optimized;
pivotGridControl1.BeginUpdate();
// Create a row pivot grid field bound to the Country data source column.
PivotGridField fieldCountry = new PivotGridField();
fieldCountry.Caption = "Country";
fieldCountry.Area = FieldArea.RowArea;
DataSourceColumnBinding countryBinding = new DataSourceColumnBinding("Country");
fieldCountry.DataBinding = countryBinding;
// Create a row pivot grid field bound to the Sales Person data source column.
PivotGridField fieldCustomer = new PivotGridField();
fieldCustomer.Caption = "Customer";
fieldCustomer.Area = FieldArea.RowArea;
DataSourceColumnBinding customerBinding = new DataSourceColumnBinding("Sales Person");
fieldCustomer.DataBinding = customerBinding;
// Create a column pivot grid field bound to the OrderDate data source column.
PivotGridField fieldYear = new PivotGridField();
fieldYear.Caption = "Year";
fieldYear.Area = FieldArea.ColumnArea;
DataSourceColumnBinding fieldOrderDate1Binding = new DataSourceColumnBinding("OrderDate");
fieldOrderDate1Binding.GroupInterval = FieldGroupInterval.DateYear;
fieldYear.DataBinding = fieldOrderDate1Binding;
// Create a column pivot grid field bound to the CategoryName data source column.
PivotGridField fieldCategoryName = new PivotGridField();
fieldCategoryName.Caption = "Product Category";
fieldCategoryName.Area = FieldArea.ColumnArea;
DataSourceColumnBinding categoryNameBinding = new DataSourceColumnBinding("CategoryName");
fieldCategoryName.DataBinding = categoryNameBinding;
// Create a filter pivot grid field bound to the ProductName data source column.
PivotGridField fieldProductName = new PivotGridField();
fieldProductName.Caption = "Product Name";
fieldProductName.Area = FieldArea.FilterArea;
DataSourceColumnBinding productNameBinding = new DataSourceColumnBinding("ProductName");
fieldProductName.DataBinding = productNameBinding;
// Create a data pivot grid field bound to the 'Extended Price' data source column.
PivotGridField fieldExtendedPrice = new PivotGridField();
fieldExtendedPrice.Area = FieldArea.DataArea;
DataSourceColumnBinding extendedPriceBinding = new DataSourceColumnBinding("Extended Price");
fieldExtendedPrice.DataBinding = extendedPriceBinding;
// Specify the formatting setting to format summary values as integer currency amount.
fieldExtendedPrice.CellFormat = "c0";
// Add the fields to the control's field collection.
pivotGridControl1.Fields.AddRange(fieldCountry, fieldCustomer,
fieldCategoryName, fieldProductName, fieldYear, fieldExtendedPrice);
// Arrange the row fields within the Row Header Area.
fieldCountry.AreaIndex = 0;
fieldCustomer.AreaIndex = 1;
// Arrange the column fields within the Column Header Area.
fieldCategoryName.AreaIndex = 0;
fieldYear.AreaIndex = 1;
pivotGridControl1.EndUpdate();
}
}
}
Run the project and see the result:
Populate Pivot Grid Fields with Data
To visualize data in the Pivot Grid, create Pivot Grid fields, populate them with data from the connected data source, and position them in the header areas.
The technique of binding Pivot Grid fields to data depends on the data source to which the Pivot Grid is connected or data processing mode that the Pivot Grid uses to process data. The data processing mode determines how Pivot Grid executes data aggregation and filter operations. The following modes are available: In-Memory, Server, and OLAP modes.
Topics in the following sections describe how to create and bind Pivot Grid fields to data in different data processing modes:
- Sever Mode
- Shows how to connect the Pivot Grid to server mode data sources and populate Pivot Grid fields with data.
- OLAP Mode
- Describes how to connect the Pivot Grid to an OLAP data source and populate Pivot Grid fields with data.
- In-Memory Mode
- Describes how to populate Pivot Grid fields with data when Pivot Grid uses In-Memory mode.
Refer to the following topic for more information about data processing modes: Pivot Grid Data Processing Modes.