Lesson 1 - Bind a Pivot Grid to an MDB Database

  • 4 minutes to read

This tutorial will help you learn how to add the Pivot Grid Control to your WinForms application, bind it to a data source and map its fields to the database fields. In this example, we use a Microsoft Access database (.mdb), although the pivot grid can be bound to a variety of other data providers.

View Example Watch Video

To create a simple WinForms application with a pivot grid, do the following.

Steps 1-4. Create a New Project and Add PivotGridControl

  1. Run Microsoft Visual Studio.
  2. Create a new Windows Forms Application project.
  3. Drag the PivotGridControl item from the DX.21.2: Data & Analytics toolbox tab onto the form.


  4. Click the smart tag icon (SmartTag) displayed at the top-right of the pivot grid to invoke the PivotGridControl Tasks popup window.


    In this window, click Dock in Parent Container to let the pivot grid fill the entire window’s client area.


Steps 5-16. Prepare a Data Source

Now you need to add a data source to your WinForms Application. To do this, follow the steps below.

  1. Click the Data Source Wizard panel:


    or in the PivotGridControl Tasks popup window, select the Data Source Wizard item.


  2. In the invoked wizard, select ADO.NET Typed DataSet technology and click the New Data Source… button.


    Click OK to create a new data source.


  3. On the first page of the wizard, choose Database as the type of a data source and click Next.


  4. Choose Dataset as a Database Model and proceed.


  5. On the Choose Your Data Connection page, click New Connection… to create a connection to a database.


    The Add Connection dialog will be invoked.

  6. In this dialog, do the following.

    • In the Data source field, specify Microsoft Access Database File (OLE DB) as the database type. If necessary, use the Change… button to select between different types.
    • Specify the path to the Northwind database file in the Database file name field. By default, this database is stored in the following path:

      C:\Users\Public\Documents\DevExpress Demos 21.2\Components\Data\nwind.mdb

      You can use the Browse… button to locate this file.


    Click OK to close the dialog and then click Next in the wizard.

  7. A message box that asks whether to copy the database file to the project appears. Click Yes.


  8. On the next step, you can choose whether to save the connection settings to a connection string or not.


    Make sure that the Yes, save the connection as check box is checked and click Next.

  9. On the Choose Your Database Objects page, choose the database columns that you need to include in the data source.

    To do this, expand the Views and SalesPerson nodes and select the following columns in the SalesPerson view.

    • Country
    • ProductName
    • CategoryName
    • Extended Price
    • Sales Person


    After this, click Finish to close the wizard.

  10. Invoke the Data Source Configuration Wizard again, select the ADO.NET Typed DataSet technology and choose the previously created data source. Click Next.


  11. In the next window, select Direct Binding to Data Source and click Next.


  12. Select a table and click Finish to close the Data Source Configuration Wizard.


Steps 17-18. Create and Arrange Pivot Grid Fields

  1. Click the pivot grid’s smart tag icon and then click Retrieve Fields in the popup window.


    The Pivot Grid Control will create fields for all columns in the data source and locate the created fields in the Filter Header Area.


  2. Put the Country and Sales Person fields into the Column Header Area, the Category Name and Product Name fields into the Row Header Area, and the Extended Price field into the Data Header Area.

    You can drag fields between areas and change their order within areas with drag-and-drop.


    After you have finished, the pivot grid fields should be arranged as illustrated below.



Alternatively, you can create fields manually using the Fields Page in the PivotGrid Designer. This page allows you to add new fields and customize their properties. To learn which properties you will need to set in order to map a field to a data source column and locate it in the appropriate area, see Pivot Grid Fields.


Run the project to see the result.



You can convert standard Windows form to XtraForm for style consistency. In Visual Studio Desgner, click the smart tag ans select the Convert to Skinable Form option.


See Also