Lesson 1 - Bind a Pivot Grid to an MDB Database

• Jan 23, 2019
• 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 appropriate fields in a database. In this example, we will use a Microsoft Access database (.mdb), although the pivot grid can be bound to a variety of other data providers.

Note

Download the completed project: Bind a Pivot Grid to an MDB Database (Runtime sample)

Watch the video: DevExpress WinForms Pivot Grid: Getting Started (YouTube)

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

• Steps 1-4. Create a New Project and Add PivotGridControl
• Steps 5-16. Prepare a Data Source
• Steps 17-18. Create and Arrange Pivot Grid Fields
• Result

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

1. Run MS Visual Studio.
2. Create a new Windows Forms Application project.

3. Place the PivotGridControl on the main form. You can do this by dragging the PivotGridControl item from the DX.18.2: Data & Analytics toolbox tab onto the form.

   

4. Click the smart tag icon () 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.

5. In the PivotGridControl Tasks popup window, select the Data Source Wizard item.

   

6. 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.

   

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

   

8. Then, choose Dataset as a Database Model and proceed.

   

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

   

   The Add Connection dialog will be invoked.

10. 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 18.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.

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

    

12. 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.

13. 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.

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

    

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

    

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

    

Steps 17-18. Create and Arrange Pivot Grid Fields

17. 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.

    

18. 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.

    

Note

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.

Result

Run the project to see the result.