Sample Hierarchy Definitions
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 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 |
|
Hierarchy with multiple groupings |
|
Hierarchy showing grand totals |
|
One parent with two child rowsets, a group detail and a parameterized 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 |
|
Computed rowsets that have a one-to-one or one-to-many relationship and are members of multiple groups |
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 (order_id) REFERENCES order_table )