MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB

Grand Totals

The Data Shaping Service for OLE DB can be used to group a hierarchical structure to produce a grand total. In this example, certain columns are selected by cust_id from the Customers table, including the order amount; the order information is selected from the Orders table. All data is fetched before it is related and totaled. 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, rsCustGrp, is the parent of rsDetail and contains the summary information of all child rowsets. By eliminating the BY clause in the outer summary, the COMPUTE SUM statement produces the grand total for order summaries of the parent rows.

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

This results in the following hierarchy with a grand total: