Visual Basic Concepts

Scenarios For Using the Data Environment Designer

See Also

This section presents scenarios that illustrate how you can use the Data Environment designer. Among the features highlighted are creating a hierarchy, adding aggregates, grouping, and binding to a Hierarchical FlexGrid.

Creating a Relation Hierarchy Between a Customer and an Order Table

To create a relation hierarchy between a Customer and an Order table

1. Create Command object, connected to the Northwind database.
   
   
2. Select the Customers table as the Command object's source. Change the Command object name to "Customers."
   
   
3. Create a child Command off the Customer Command object. Select the Orders table as the child's source. Change the child Command object's name to "Orders."
   
   
4. Click the Relation tab, and then link the parent and child Command objects using their CustomerID fields.

To view the results, see "Binding Your Data Environment to a Hierarchical FlexGrid Control" later in this topic.

Adding an Aggregate to the Customer Command Object

To add an aggregate to the Customer Command object to obtain the SUM of the order amounts for all orders per customer

1. Edit the Customer Command object's properties.
   
   
2. Click the Aggregate tab, and then click Add.
   
   
3. Name the aggregate "OrdersTotal."
   
   
4. Select Sum as the function.
   
   
5. Select the Aggregate On item "Orders".
   
   
6. Select OrderID as the Field to Sum.

The OrdersTotal aggregate is added to the DataEnvironment object as a new Field object. At run time, the OrdersTotal aggregate will contain a calculated value for each customer.

To view the results, see "Binding Your Data Environment to a Hierarchical FlexGrid Control" later in this topic.

Grouping the Customer Command Object by State

To group the customer Command object by state

1. Edit the Customer Command object's properties.
   
   
2. Click the Grouping tab, and select Group Command Object.
   
   
3. Name the grouping Command object "ByCountry."
   
   
4. Select "Country" from the Fields in Command list, and then click > to move it into the Fields Used for Grouping list.

An additional Recordset object, ByCountry, is exposed on top of the Customers Command object. When binding to the Hierarchical FlexGrid, you should bind to the ByCountry Recordset, not to the Customers Command object.

To view the results, see "Binding Your Data Environment to a Hierarchical FlexGrid Control" later in this topic.

Adding an Aggregate to Count the Number of Customers per State

To add an aggregate to count the number of customers per state

1. Edit the Customer Command object's properties.
   
   
2. Click the Aggregates tab, and click Add.
   
   
3. Name the aggregate "CustCount."
   
   
4. Select Count as the function.
   
   
5. Select the Aggregate On item "Grouping".
   
   
6. Select CustID as the Field to Count.

Another field is added to the summary (grouping) Command object. This Field object contains a calculated value that counts the number of customers per state.

To view the results, see "Binding Your Data Environment to a Hierarchical FlexGrid Control" later in this topic.

Adding a Grand Total Aggregate to Count the Number of States with Customers

To add a Grand Total Aggregate to count the number of states with customers

1. Edit the Customer Command object's properties.
   
   
2. Click the Aggregates tab, and then click Add.
   
   
3. Name the aggregate "NumStatesWithCustomers."
   
   
4. Select Count as the function.
   
   
5. Select GrandTotal as the Aggregate On item.
   
   
6. Select the "State" as the Field.
   
   
7. Name the Grand Total "CustomerGrandTotal."

An additional Recordset object, NumStatesWithCustomers, is exposed on top of the Customers Command object. This new object contains the number of states that have customers.

To view the results, see the following section.

Binding Your Data Environment to a Hierarchical FlexGrid Control

The following scenario is provided so you can view the results of the previous scenarios. See the Hierarchical FlexGrid Control for more information.

To bind your Data Environment to a Hierarchical FlexGrid control

1. Once you have completed the previous Data Environment scenarios, drag an MSHFlexGrid control onto a Visual Basic form.
   
   
2. From the Visual Basic property sheet, set the DataSource property to the DataEnvironment object that contains the Command object that you want to bind to the Hierarchical FlexGrid control. For example, specify DataEnvironment1.
   
   
3. From the Visual Basic Properties window, set the DataMember property to a parent Command object in your Data Environment. Your data is now bound to a Hierarchical FlexGrid control.
   
   
4. To view the data in the Hierarchical FlexGrid, select Start from the Run menu or press F5. The structure is retrieved and displays in the Hierarchical FlexGrid.