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

Sample Hierarchy Definitions

The Data Shaping Service for OLE DB allows you to build and manipulate hierarchies using fetched data obtained from your data provider. It applies specific commands to relate disparate rowsets in one of two distinct hierarchical groups: relation-based (including parameter-based) hierarchies and group-based hierarchies created by the COMPUTE clause.

The following examples pertain to the Sales database with a Customer table and Orders table.

Hierarchy definition	Sample
Aggregated group hierarchy	Group Hierarchy with Aggregation
Hierarchy with multiple groupings	Multiple Groupings
Hierarchy showing grand totals	Grand Totals
One parent with two child rowsets, a group detail and a parameterized hierarchy	Multifaceted Hierarchy
Nested hierarchies	Grouped Customers Related to Grouped Orders Grouped Customers Related to Grouped Orders with Aggregation
Grandchild grouping in order to skip one level when summing	Grandchild Aggregations
Computed rowsets that have a one-to-one or one-to-many relationship and are members of multiple groups	Parameterized Computed Child Rowsets

The following table schema is used for the sample hierarchy definitions:

CREATE TABLE Customers
         ( cust_id     INTEGER,
         cust_name     VARCHAR(20),
         city          VARCHAR(20),
         state         VARCHAR(20),
         PRIMARY KEY   (cust_id) )

CREATE TABLE Contacts
         ( cust_id     INTEGER,
         contact_id    INTEGER,
         city          VARCHAR(20),
         state         VARCHAR(20),
         region        INTEGER,
         PRIMARY KEY   (contact_id),
         FOREIGN KEY   (cust_id) REFERENCES customer_table )

CREATE TABLE Orders
         ( cust_id     INTEGER, 
         order_id      INTEGER,
         order_date    DATE,
         order_amt     DECIMAL(9,2),
         PRIMARY KEY   (order_id),
         FOREIGN KEY   (cust_id) REFERENCES customer_table ) 

CREATE TABLE OrderItems
         ( order_id    INTEGER, 
         item_id       INTEGER,
         description   VARCHAR(30),
         quantity      SMALLINT,
         unit_price    DECIMAL(6,2), 
         PRIMARY KEY   (order_id, item_id),
         FOREIGN KEY   (orderid, REFERENCES order_table )