Bound Mode

• Aug 02, 2022
• 4 minutes to read

The intent of the Bound Mode is to persist the pivot grid’s data in the database. The TcxDBPivotGrid control was designed to serve the Bound Mode.

For working in Bound Mode, set up a connection between the TcxDBPivotGrid control and the underlying database, as shown below (the description of the project is based on the OrderReportsDemo that ships with this product):

1. Drop the TcxDBPivotGrid control (located on the DevExpress page) onto a form from the Component Palette.

2. Drop three TDataSource controls (located on the Data Access page) and three TTable controls (located on the BDE page) onto a form from the Component Palette.

3. Using the Object Inspector, name TTable controls as tblCars, tblOrders and tblCustomers. Specify in the TTable.TableName properties of the above controls the path to the folder where the Cars.DB, Orders.DB and Customers.DB tables are stored. The tables that are used in this topic were created with the BDE toolset in the Paradox® format.

4. Using the Object Inspector, name TDataSource controls as dsCars, dsOrders and dsCustomers. Then, bind the tblCars, tblOrders and tblCustomers datasets to the related data sources via the TDataSource.DataSet properties.

5. Using the Object Inspector, bind the pivot grid to the dsOrders data sources (as the underlying tblOrders is a master dataset) through the TcxDBPivotGrid.DataSource property.

6. Create persistent field components for the above datasets:

• Double-click the tblCars component to open the Fields editor. Add tblCarsID, tblCarsTrademark, tblCarsModel and tblCarsPrice fields. Additionally, create the new tblCarsCarName field. Set its FieldKind property to fkCalculated.

• Double-click the tblCustomers component to open the Fields editor. Add tblCustomersID and tblCustomersCompany fields.

• Double-click the tblOrders component to open the Fields editor. Add tblOrdersID, tblOrdersCustomerID, tblOrdersProductID, tblOrdersPurchaseDate, tblOrdersPaymentType, tblOrdersQuantity and tblOrdersPaymentAmount fields. Set the PaymentAmount‘s FieldKind property to fkCalculated. Additionally, create three new lookup fields: tblOrdersCarName, tblOrdersUnitPrice and tblOrdersUnitPrice.

• In the tblOrdersCarName, assign the tblCarsCalcFields procedure to OnCalcFields property (code is shown later in this topic), set the KeyFields property to tblOrdersProductID and the LookupDataSet property to tblCars. In the assigned dataset set its LookupKeyFields and the LookupResultField properties to tblCarsID and tblCarsCarName, respectively.

• In the tblOrdersUnitPrice, set its KeyFields property to tblOrdersProductID and the LookupDataSet property to tblCars. In the assigned dataset set its LookupKeyFields and the LookupResultField properties to tblCarsID and tblCarsPrice, respectively.

• In the tblOrdersCompanyName, set its KeyFields property to tblCustomersID and the LookupDataSet property to tblCustomers. In the assigned dataset set its LookupKeyFields and the LookupResultField properties to tblCustomersID and tblCustomersCompany, respectively.

• Create the tblCarsCalcFields procedure to handle the tblOrdersCarName field (code is shown later in this topic).

7. Double-click the pivot grid control to invoke the Designer Dialog. In the Designer Dialog, add pgfPurchaseDate, pgfPaymentType, pgfQuantity, pgfCarName, pgfUnitPrice, pgfCompanyName and pgfPaymentAmount fields to the pivot grid. Using the DataBinding.FieldName property, map the above fields to the related dataset fields. Through the DataBinding.ValueType property, set the appropriate type for each field.

8. Specify the drop areas for the pivot grid’s fields. Set the TcxDBPivotGridField.Area property of the following fields: the pgfQuantity and pgfPaymentAmount fields to faData to show them in the data header area, the pgfCompanyName and pgfCarName fields to faRow to show them in the row header area, the pgfPaymentType field to faColumn to show it in the column header area, the pgfUnitPrice and pgfPurchaseDate fields to faFilter to show them in the filter header area.

Below is an implementation of the tblCarsCalcFields procedure. The following demonstrates how a compound field value is produced for the tblCarsCarName field.

// ...
procedure tblCarsCalcFields(DataSet: TDataSet);
begin
  tblCarsCarName.Value := tblCarsTrademark.Value + ': ' + tblCarsModel.Value;
end;

Now the application is ready to start. The image below shows the pivot grid when the application runs:

Easy Steps to Connect

Whenever you have active datasets in a form that are accessible via the TDataSource components, all you have to do to connect the TcxDBPivotGrid control to a data source is invoke the pivot grid’s context menu and select the required data source via the “Link to DataSource” item. This will automatically create pivot grid fields bound to dataset fields. The created pivot grid fields are placed into the filter header area. You can move them to other areas using drag and drop or a field’s Area property.