Data extraction examples

These examples illustrate the results of reference link, summary link, summary report, and detail report queries.

Grocery store

This table shows customer purchases against a grocery store inventory in a database.

Product category Product ID Product Quantity Cost
DAIRY 10001 Cheese 1 3
DAIRY 10001 Cheese 3 9
DAIRY 10002 Milk 2 6
FRUIT 20001 Apple 10 15
FRUIT 20001 Apple 8 12
FRUIT 20002 Orange 6 10
FRUIT 20003 Pineapple 5 4
FRUIT 20004 Cherries 20 6
FRUIT 20004 Cherries 30 10
BAKERY 30001 Sandwich Loaf 10 20
BAKERY 30001 Sandwich Loaf 8 15
BAKERY 30001 Sandwich Loaf 2 6
BAKERY 30001 Sandwich Loaf 3 9
BAKERY 30002 Toast Loaf 20 25
BAKERY 30002 Toast Loaf 10 25
BAKERY 30003 Wholemeal Loaf 15 35
BAKERY 30003 Wholemeal Loaf 10 25
BAKERY 30003 Wholemeal Loaf 5 10
BAKERY 30004 Cakes 6 12
BAKERY 30004 Cakes 12 24

Reference Link

A reference link returns the value of a single record.

In this example:

  • The Data Filter item is Product ID = 20002
  • The Output item is Product.

This table shows the result as Orange:

Product category Product ID Product Quantity Cost
FRUIT 20002 Orange 6 10

Summary Link

A summary link returns the balance of a filtered range. For example, the total cost of Bakery goods. You can output the Bakery item with the highest cost by specifying the 'Maximum' Extraction Type.

In this example:

  • The Data Filter item is Product Category = BAKERY
  • Cost is selected twice as an output item. In one instance, the Extraction Type is Sum. In the second, the Extraction Type is Maximum.

This table shows that the displayed results are $206 (Sum) and $35 (Maximum).

Product category Product ID Product Quantity Cost
BAKERY 30001 Loaf 10 20
BAKERY 30001 Sandwich Loaf 8 15
BAKERY 30001 Loaf 2 6
BAKERY 30001 Sandwich Loaf 3 9
BAKERY 30002 Toast Loaf 20 25
BAKERY 30002 Toast Loaf 10 25
BAKERY 30003 Wholemeal Loaf 15 35
BAKERY 30003 Wholemeal Loaf 10 25
BAKERY 30003 Wholemeal Loaf 5 10
BAKERY 30004 Cakes 6 12
BAKERY 30004 Cakes 12 24

Summary Report

A summary report provides a summarized listing. Microsoft® Office Excel displays a row that contains all Output Data Items for each summary level. For example, a list of fruit goods and quantities with the ID 20002 to 20004.

In this example:

  • The Data Filter item is Product ID = 20002 to 20004
  • The Output Data items are Product and Quantity.

This table shows the displayed results:

Product Quantity
Cherries 50
Orange 6
Pineapple 5
Total 61

Detail report

A Detailed Report provides a detailed listing. There is one row in Excel for each record in the database. For example, all products and respective quantities that include the term 'Loaf'.

In this example:

The Data Filter item is Product = %Loaf%

The Output Data items are Product and Quantity.

This table shows the displayed results:

Product Quantity
Sandwich Loaf 2
Sandwich Loaf 3
Sandwich Loaf 8
Sandwich Loaf 10
Toast Loaf 10
Toast Loaf 20
Wholemeal Loaf 5
Wholemeal Loaf 10
Wholemeal Loaf 15