Multiple Groupings

Important

This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.

The Data Shaping Service for OLE DB can be used to group any number of times within a hierarchical structure to produce totals at different levels. In this example, the summary of the first group, the Customer Group (rsCustGrp), is calculated by computing all customer details, and then the Customer Group is summmed by state, which forms the second grouping. Two SHAPE commands are used here: One builds the Customer Group hierarchy; the other builds the State hierarchy using the results of the Customer Group hierarchy.

Shaping the Customer Group Hierarchy

In this example, certain columns are selected by cust_id from the Customers table, including the order amount, and the order information is selected from the Orders table. All data is fetched before it is related and summed. The resulting child rowset is named rsDetail. RsDetail is then summed using the ordered amount for any customer ID within the scope. The resulting rowset is called rsCustGrp and is the parent of rsDetail. It contains the summary information of all child rowsets. When accessing a parent row, the internally created index relates the parent to its child rowsets.

Shaping the State Hierarchy

After the Customer group has been built into a relational hierarchy, with parent rows associated with their child rows through the index, the State group is built. The Customer group is the child rowset associated with its parent the State row. Again, the relation is made by creating the index or filter.

SHAPE
   (SHAPE {select customer.*, orders.order_id, name,
            orders.order_amt from Customers, Orders
            where customer.cust_id = orders.cust_id}
         AS rsDetail
      COMPUTE ANY(rsDetail.cust_name)
         AS cust_name,
      ANY(rsDetail.state)
         AS state,
      SUM(rsDetail.order_amt)
         AS ord_amt, rsDetail
      BY cust_id )
      AS rsCustGrp
   COMPUTE rsCustGrp
   BY state

This results in the following aggregations within this multiple grouped hierarchy: