Grand Totals

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 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: