794.TXT
=====================================================================
Creating a Many-to-Many Report
=====================================================================
PRODUCT: R:BASE VERSION: 4.5++ or Higher
=====================================================================
CATALOG: Forms, Reports & Labels AREA : REPORTS
=====================================================================
A relational database depends on storing descriptive information
in separate tables and then relating those tables together to
display all the relevant data. The relationship between tables is
vital. A relationship is defined by linking columns, which are
columns that are from different tables and contain matching data.
Linking columns often have the same name in both tables. There are
three basic relationships between tables in a relational database:
One-to-one_there is only one row in each table where the
linking column(s) contain matching data. The linking
column in each table is unique. When two tables with a
one-to-one relationship are joined, the result is a "one"
table.
One-to-many_in one of the tables, the linking column is
unique, there is only one row for each value. In the
other table, there can be one row or many rows for the
linking column. When the tables are joined, the result is
a "many" table. The number of rows returned is the number
of rows from the "many" table.
Many-to-many_in both tables, there can be many rows for
the linking column(s). The linking column value is not
unique in either table. The result of a join between two
"many" tables is a "many" table. The number of rows
returned is the number of rows in one table multiplied by
the number of rows in the other table.
Relationships are usually defined between two tables at a time.
Relationships between more than two tables are more easily defined
when taken two at a time. For example, a one-to-many-to-many join is
really a one-to-many join and a many-to-many join.
The one-to-many is the most common relationship in a relational
database. For example, the relationship between customers and orders
is a one-to-many relationship. Each customer has a row of data in a
customer table, but can have many associated rows in an orders table.
An example of a many-to-many relationship is the relationship between
orders and payments. An order can have many payments associated with
it, and a payment can be associated with many orders. When orders
and payments are connected to customers, the relationship becomes a
one-to-many-to-many. Each customer has a row of data in a customer
table, many associated rows in an orders table, and many associated
rows in a payments table. The payments and orders are both associated
to the customer, but not necessarily to each other.
Reports are easy to create on one-to-one or one-to-many relationships.
It is difficult to create a report on tables with a many-to-many or
one-to-many-to-many relationship. The desired report layout is often
one with multiple types of detail information associated with one
identifying record.
For example:
header information about the customer - the "one" table
all the order data - from a "many" table
sub-total of orders
all the payment - from the other "many" table
sub-total of payments
footer information for the customer - total of orders and payments
header information about the customer - the "one" table
all the order data - from a "many" table
sub-total of orders
all the payment - from the other "many" table
sub-total of payments
footer information for the customer - total of orders and payments
etc.
This layout is very difficult to achieve. It is essentially a report
with two different detail sections having different data layouts for
each section. The R:BASE report writer can't do this. You can get
creative with views or manipulate the data and store it in a temporary
table to get close to this type of a report. But, with the addition
of PAGEMODE in R:BASE 4.5++, this type of report is easy to create
using the R:BASE programming language. You have complete control over
the order of the data and how it is displayed. Following is an
example of a PAGEMODE report printing both order and payment
information for customers.
Sample page of report output:
204 Electronic City
123 College Way
Amherst, MA 01002
Order# PO# Order Date Order Amount Sold By
------ ------ ---------- ------------ ----------
-
H002 2022 07/24/94 $7,110.00 Malone
H010 2056 08/28/94 $3,510.00 Malone
------------
$10,620.00
Payment Date Order# Payment Amount
------------ ------ --------------
09/15/94 H010 $1,755.00
09/15/94 H002 $6,740.00
10/10/94 H010 $1,755.00
-------------
$10,250.00
Balance Due: $370.00
*(MANY.CMD - Report with two detail sections )
CON hifi
CLS
SET HEADING OFF
SET PAGEMODE OFF
SET LINES 40
SET MESSAGE OFF
SET VAR vstoreid INT, vrow INT
DECLARE c1 CURSOR FOR SELECT StoreID, StoreName, Address, +
(City + ',' & State & Zipcode) FROM storelist
DECLARE c2 CURSOR FOR SELECT OrderNumber, EmployeeID,
PONumber, +
OrderDate, TotalSale FROM orders WHERE StoreID = .vstoreid+
ORDER BY OrderDate
DECLARE c3 CURSOR FOR SELECT OrderNumber, PayAmount, PayDate+
FROM payments WHERE StoreID = .vstoreid ORDER BY PayDate
SET PAGEMODE ON
OPEN c1
FETCH c1 INTO vstoreid vi1, vcompany vi2, vaddress vi3, +
vcitystzip vi4
OUTPUT many.out
WHILE SQLCODE <> 100 THEN
WRITE .vstoreid=4 AT 3,3
WRITE .vcompany AT 3,9
WRITE .vaddress AT 4,9
WRITE .vcitystzip AT 5,9
WRITE 'Order# PO# Order Date Order Amount Sold By ' +
AT 7 3
WRITE '------ ------ ---------- ------------ +
-----------' +AT 8,3
SET VAR vrow = 9
OPEN c2 RESET
FETCH c2 INTO vOrder# i1, vEmpiD i2, vPONum i3, vOrderDate i4, +
vTotalSale i5
WHILE SQLCODE <> 100 THEN
SET VAR vname = LastName IN salespeople WHERE employeeid=.vempid
WRITE .vorder# AT .vrow,4
WRITE .vponum AT .vrow,13
WRITE .vorderdate AT .vrow,23
WRITE .vtotalsale AT .vrow,36 USING '$999,999.00'
WRITE .vname AT .vrow, 54
SET VAR vrow = (.vrow + 1)
FETCH c2 INTO vOrder# i1,vEmpiD i2, vPONum i3,vOrderDate i4, +
vTotalSale i5
ENDWH
SELECT SUM(totalsale) INTO vtotal FROM orders WHERE +
storeid=.vstoreid
SET VAR vrow = (.vrow + 1)
WRITE '------------' AT .vrow,36
SET VAR vrow = (.vrow + 1)
WRITE .vtotal AT .vrow,36 USING '$999,999.00'
SET VAR vrow = (.vrow + 2)
WRITE 'Payment Date Order# Payment Amount ' +
AT .vrow,8
SET VAR vrow = (.vrow + 1)
WRITE '------------ ------ --------------' AT .vrow ,8
SET VAR vrow = (.vrow + 1)
OPEN c3 RESET
FETCH c3 INTO vOrderNumber i1, vpayamount i2, vpaydate i3
WHILE sqlcode <> 100 THEN
WRITE .vpaydate AT .vrow,9
WRITE .vordernumber AT .vrow,25
WRITE .vpayamount AT .vrow,36 USING '$999,999.00'
SET VAR vrow = (.vrow + 1)
FETCH c3 INTO vOrderNumber i1, vpayamount i2, vpaydate i3
ENDWH
SET VAR vrow = (.vrow + 1)
SEL SUM(payamount) INTO vtotalpaid FROM payments WHERE +
storeid=.vstoreid
IF vtotalpaid <> 0 THEN
WRITE '-------------' AT .vrow,36
SET VAR vrow = (.vrow + 1)
WRITE .vtotalpaid AT .vrow,36 USING '$999,999.00'
ENDIF
SET VAR vbalance = (.vtotal - .vtotalpaid)
SET VAR vrow = (.vrow + 2)
WRITE 'Balance Due:' AT .vrow ,24
WRITE .vbalance AT .vrow,36 USING '$999,999.00'
NEWP
SET VAR vrow = 9
FETCH c1 INTO vstoreid vi1, vcompany vi2, vaddress vi3, +
vcitystzip vi4
ENDWH
OUTPUT SCREEN
SET PAGEMODE OFF
SET HEADINGS ON
SET LINES 20
SET MESSAGE ON
TYPE many.out
Setup the report environment.
Initialize variables.
Declare the cursors to retrieve the data. The storelist table is
the "one" table, the orders and payments tables are the "many"
tables. The cursors are related, only data associated with the
corresponding row in the storelist table is retrieved
Get the first row of customer data and write the heading.
The heading for the orders data
Get the detail information for orders.
Write the detail data for orders.
Calculate and display the total order amount for the customer.
The heading for the payments data.
Get the detail information for payments.
Write the detail data for payments. Note that although the amounts
line up with the orders detail, the other data is formatted
differently.
Calculate and display the total payment amount for the customer.
Calculate and display the total balance amount for the customer.
Send the page and fetch the next customer.
Close the report and reset the environment.