Master/Multi-Detail Report Writer Techniques

                         Document #: US-17326,EN
------------------------------------------------------------------------------

Major subject: analysis    Minor subjects: tech_notes

Keywords: report

Abstract:
Master/Multi-Detail Report Writer Techniques - Equivalent 
to INGRES Release 5 Technical Note #76 but applies to Release 
6 also. In Release 6 you can use .SETUP and .CLEANUP to 
create the temporary tables. How to create a report that 
has multiple details.


Expert note:
Master/Multi-Detail Report Writer Techniques
============================================

o	Overview
o	Creating Temporary Tables for Master / 2 Details Reports
o	Using Block Mode in Report Writer
o	Technique to Avoid Spanning Pages in Block Mode
o	Simulating the .NEED Command When You Have Variable Length Blocks
o	The Difference in Using SQL as the Query Language
o	Technique to Avoid Spanning Pages in Block Mode
o	Master with 3 Details
o	Summary


Overview
--------
Many INGRES users would  like  to  generate  a  report  that  shows  the
relationship between a master and multiple details.  We will look at a
method of reporting this type of relationship using a  library  example.
The desired report in this example looks like this:

        TITLE OF BOOK
        -------------
                Author1         Subject1
                Author2         Subject2
                                Subject3

This information comes from three different tables.  One containing  the
title  of  the  book,  one  with author information and one with subject
information.  Each table has an id that will be used to join the  tables
properly.

Book Table

|id           |title                         |
|--------------------------------------------|
|         1001|The C Programming Language    |
|--------------------------------------------|

Author Table

|id           |name           |
|-----------------------------|
|         1001|Ritchie        |
|         1001|Kernighan      |
|-----------------------------|

Subject Table

|id           |subject        |
|-----------------------------|
|         1001|C              |
|         1001|programming    |
|         1001|language       |
|-----------------------------|

The Report Writer can get the data being reported from 3 places, a  sin-
gle  table, a view based on one or more tables, or several tables joined
in a retrieve(QUEL) or select (SQL) statement.

Since multiple queries are not allowed in the  Report  Writer,  we  must
organize  our  data so that all of the data is contained in single table
or view or a single query.

The retrieve that you think would work in this instance  gives  a  cross
product.
retrieve (book.title, author.name, subject.subject)
        where   book.id = author.id and
                book.id = subject.id
        sort by title, name, subject

|title                         |name           |subject        |
|--------------------------------------------------------------|
|The C Programming Language    |Kernighan      |C              |
|The C Programming Language    |Kernighan      |language       |
|The C Programming Language    |Kernighan      |programming    |
|The C Programming Language    |Ritchie        |C              |
|The C Programming Language    |Ritchie        |language       |
|The C Programming Language    |Ritchie        |programming    |
|--------------------------------------------------------------|

The Report Writer will not generate the desired  report  with  the  data
organized in this manner.  The Report Writer can print only columns from
the current row of data that it has read from the data table.  That  is,
Report Writer reads one row of data at a time and once it goes on to the
next row, it can not reach back and use  values  from  a  previous  row.
Similarly,  you  can not easily suppress the printing of a column in the
detail section of a report if duplicates exist in the data table.



Creating Temporary Tables for Master / 2 Details Reports
--------------------------------------------------------
The solution is to create a table that has the data organized in  a  way
the  Report  Writer  can  use.  The table will be temporary in nature in
that it will have to be recreated each time the report is re-run if  the
underlying  data changes.  The trick is to join each detail table to the
master table without joining the detail tables to each other.  The  tem-
porary  table  will also need a code to help the Report Writer know when
to break.

Here are the queries that produce the temporary  table  needed  for  the
desired report:

QUEL:

range of b is book,a is author,s is subject
retrieve into tempreport (b.all,a.name,
                subject=ascii("               "),code=1)
         where b.id=a.id
append to tempreport (b.all,s.subject,code=2)
        where b.id=s.id


SQL:

create table tempreport as
        select b.id,b.title,a.name,
                subject=ascii("               "),code=1
        from book b,author a
        where b.id=a.id;
insert into tempreport (id,title,subject,code)
        select b.id,b.title,s.subject,code=2
        from book b,subject s
        where b.id=s.id;

The resulting table looks like this:

|id      |title                         |name       |subject      |code  |
|------------------------------------------------------------------------|
|    1001|The C Programming Language    |Kernighan  |             |     1|
|    1001|The C Programming Language    |Ritchie    |             |     1|
|    1001|The C Programming Language    |           |C            |     2|
|    1001|The C Programming Language    |           |language     |     2|
|    1001|The C Programming Language    |           |programming  |     2|
|------------------------------------------------------------------------|
v



Using Block Mode in Report Writer
---------------------------------
The report specs would look like this:

.NAME tempreport
.DATA tempreport
.SORT title,code        /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.HEADER title
        .LEFTMARGIN 0
        .ULCHARACTER "="
        .UNDERLINE
        .PRINT title .NEWLINE
        .NOUNDERLINE
        .BLOCK  /*  start a block after printing the master info  */
.HEADER code
        .TOP    /*  goto the top of the block each time code changes  */
        .ULCHARACTER "-"
/*  test the value of code and set the margin appropriately  */
        .IF code = 1 .THEN
                .LEFTMARGIN 5
                .UNDERLINE
                .PRINT "Authors" .NEWLINE
                .NOUNDERLINE
        .ELSEIF code = 2 .THEN
                .LEFTMARGIN 20
                .UNDERLINE
                .PRINT "Subjects" .NEWLINE
                .NOUNDERLINE
        .ENDIF
.DETAIL
/*  test the value of code to see which column to print  */
        .IF code = 1 .THEN
                .PRINT name .NEWLINE
        .ELSEIF code = 2 .THEN
                .PRINT subject .NEWLINE
        .ENDIF
.FOOTER title
        .ENDBLOCK       /* end the block at the end of the master info */
        .NEWLINE


The final report looks like this:

The C Programming Language
=== = =========== ========
     Authors        Subjects
     -------        --------
     Kernighan      C
     Ritchie        language
                    programming



Technique to Avoid Spanning Pages in Block Mode
-----------------------------------------------
If we add more data to our base tables so that they look like this:

Book Table

|id           |title                         |
|--------------------------------------------|
|         1001|The C Programming Language    |
|         1002|Computer Programming and Arch.|
|         1003|The INGRES Papers             |
|         1004|Database Systems              |
|--------------------------------------------|

Author Table

|id           |name           |
|-----------------------------|
|         1001|Ritchie        |
|         1001|Kernighan      |
|         1002|Levy           |
|         1002|Eckhouse       |
|         1003|Stonebraker    |
|         1004|Ullman         |
|-----------------------------|

Subject Table

|id           |subject        |
|-----------------------------|
|         1001|C              |
|         1001|programming    |
|         1001|language       |
|         1002|programming    |
|         1002|assembler      |
|         1002|architecture   |
|         1002|computer       |
|         1003|Database       |
|         1003|INGRES         |
|         1003|computer       |
|         1004|Database       |
|         1004|management     |
|-----------------------------|

The same queries as before:

QUEL:

destroy tempreport
range of b is book,a is author,s is subject
retrieve into tempreport (b.all,a.name,
                subject=ascii("               "),code=1)
         where b.id=a.id
append to tempreport (b.all,s.subject,code=2)
        where b.id=s.id


SQL:

drop tempreport
create table tempreport as
        select b.id,b.title,a.name,
                subject=ascii("               "),code=1
        from book b,author a
        where b.id=a.id;
insert into tempreport (id,title,subject,code)
        select b.id,b.title,s.subject,code=2
        from book b,subject s
        where b.id=s.id;

Will generate this temporary table:

Tempreport Table

|id      |title                         |name        |subject      |code  |
|-------------------------------------------------------------------------|
|    1001|The C Programming Language    |Kernighan   |             |     1|
|    1001|The C Programming Language    |Ritchie     |             |     1|
|    1002|Computer Programming and Arch.|Eckhouse    |             |     1|
|    1002|Computer Programming and Arch.|Levy        |             |     1|
|    1003|The INGRES Papers             |Stonebraker |             |     1|
|    1004|Database Systems              |Ullman      |             |     1|
|    1001|The C Programming Language    |            |C            |     2|
|    1001|The C Programming Language    |            |language     |     2|
|    1001|The C Programming Language    |            |programming  |     2|
|    1002|Computer Programming and Arch.|            |architecture |     2|
|    1002|Computer Programming and Arch.|            |assembler    |     2|
|    1002|Computer Programming and Arch.|            |computer     |     2|
|    1002|Computer Programming and Arch.|            |programming  |     2|
|    1003|The INGRES Papers             |            |Database     |     2|
|    1003|The INGRES Papers             |            |INGRES       |     2|
|    1003|The INGRES Papers             |            |computer     |     2|
|    1004|Database Systems              |            |Database     |     2|
|    1004|Database Systems              |            |management   |     2|
|-------------------------------------------------------------------------|



Simulating the .NEED Command When You Have Variable Length Blocks
-----------------------------------------------------------------
When your report will contain many blocks of data (books), it  is  often
desirable  to insure that a block of data does not span a page boundary.
The following Report Writer code shows changes to the .QUERY section and
the the page, and determine if the next block of data will fit.

.NAME tempreport
.QUERY
        range of r is tempreport
        retrieve (r.all,
                  num_sub=count(r.subject by r.id where r.subject != ""),
                  num_auth=count(r.name by r.id where r.name != ""))
.SORT title,code        /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.HEADER title
/*  The calculation below contains the following variables and constants:*/
/*  line_number is the current line number on the page  (RW variable)    */
/*  page_length is the current page length in the report (RW variable)   */
/*  num_sub is the number of subjects for this book (calculated above)   */
/*  num_auth is the number of authors for this book (calculated above)   */
/*  4 is the number of lines taken up by the headers in each block       */
/*  These variables are used to determine if there is enough             */
/*  room left on the page to print the next block of data.               */
        .IF num_sub > num_auth and
                line_number + num_sub + 4 > page_length .THEN
                        .NEWPAGE
        .ELSEIF  num_auth > num_sub and
                line_number + num_auth + 4 > page_length .THEN
                        .NEWPAGE
        .ENDIF
                .LEFTMARGIN 0
                .ULCHARACTER "="
                .UNDERLINE
                .PRINT title .NEWLINE
                .NOUNDERLINE
                .BLOCK  /*  start a block after printing the master info */
.HEADER code
        .TOP    /*  goto the top of the block each time code changes  */
        .ULCHARACTER "-"
/*  test the value of code and set the margin appropriately  */
        .IF code = 1 .THEN
                .LEFTMARGIN 5
                .UNDERLINE
                .PRINT "Authors" .NEWLINE
                .NOUNDERLINE
        .ELSEIF code = 2 .THEN
                .LEFTMARGIN 20
                .UNDERLINE
                .PRINT "Subjects" .NEWLINE
                .NOUNDERLINE
        .ENDIF
.DETAIL
/*  test the value of code to see which column to print  */
        .IF code = 1 .THEN
                .PRINT name .NEWLINE
        .ELSEIF code = 2 .THEN
                .PRINT subject .NEWLINE
        .ENDIF
.FOOTER title
        .ENDBLOCK       /* end the block at the end of the master info */
        .NEWLINE



The Difference in Using SQL as the Query Language
-------------------------------------------------
To produce the same report using SQL as the  query  language,  you  will
need  to  create 2 additional tables before running the report.  This is
because SQL handles aggregates differently.  The calculations of num_sub
and  num_auth in the query section above can not be done the same way in
SQL.  When you wish to perform an aggregation on a set of data (called a
set function in SQL),and group some rows together the select clause can-
not include any column not also listed in the group by clause, except as
an argument to a set function.  When a select statement contains a group
by clause, any columns listed in the select clause must be single-valued
per  group.   So you must create the following tables before running the
report.  These tables will be joined back to the first  temporary  table
to produce the report.

create table sub as
        select id,num_sub=count(subject)
        from tempreport
        where subject != ""
        group by id;

create table auth as
        select id,num_auth=count(name)
        from tempreport
        where name != ""
        group by id;


select * from sub;

|id           |num_sub      |
|---------------------------|
|         1001|            3|
|         1002|            4|
|         1003|            3|
|         1004|            2|
|---------------------------|

select * from auth;

|id           |num_auth     |
|---------------------------|
|         1001|            2|
|         1002|            2|
|         1003|            1|
|         1004|            1|
|---------------------------|


The query section of the above report would look like this in SQL:

        select t.id,t.title,t.subject,t.name,t.code,s.num_sub,a.num_auth
                from tempreport t,sub s,auth a
                where t.id = s.id
                and   t.id = a.id

The remainder of the report spec would be the same.



Master with 3 Details
---------------------
Suppose we want to include an abstract of the book in our report.

Here is an example of the abstract table:

Abstract Table

|id           |abstract
|-------------------------------------------------------------------------|
|         1001|C is a general-purpose programming language which features |
|             | economy of expression, modern control flow and data       |
|             | structures, a rich set of operators.                      |
|         1002|This book is for those who wish to understand the          |
|             | architecture and operation of computer systems.  The      |
|             | best way to understand a computer's architecture is to    |
|             | use it, and the best way to use the architecture is to    |
|             | program at the assembly level.                            |
|         1003|This book summarizes the work of the INGRES project at     |
|             | the University of California, Berkeley over the years     |
|             |1973-1983.                                                 |
|         1004|The principle concepts are concerned with relations and    |
|             | with concurrency.  The book describes relations, their    |
|             | algebra and calculus, and the query languages that have   |
|             | been designed using these concepts.                       |
|-------------------------------------------------------------------------|

We would like our report to look like this:

The C Programming Language
=== = =========== ========
     Authors        Subjects
     -------        --------
     Kernighan      C
     Ritchie        language
                    programming
Abstract
--------
C is a general-purpose programming
language with features economy of
expression, modern control flow and data
structures, and a rich set of operators.

  .

  .

  .

The queries to create the temporary table would look like this:

IN QUEL:

range of b is book,a is author,s is subject,ab is abstract
retrieve into tempabsrep (b.all,ab.abstract,name=ascii("               "),
                        subject=ascii("               "),code=3)
        where b.id=ab.id
append to tempabsrep (b.all,s.subject,code=2)
        where b.id=s.id
append to tempabsrep (b.all,a.name,code=1)
        where b.id=a.id


In SQL:

create table tempabsrep as
        select b.id,b.title,ab.abstract,name=ascii("               "),
                subject=ascii("               "),code=3
        from book b,abstract ab
        where b.id=ab.id;
insert into tempabsrep (id,title,subject,code)
        select b.id,b.title,s.subject,code=2
        from book b,subject s
        where b.id=s.id;
insert into tempabsrep (id,title,author,code)
        select b.id,b.title,a.name,code=1
        from book b,author a
        where b.id=a.id;

The report specs would look like this:

.NAME tempabsrep
.QUERY
        range of t is tempabsrep
        retrieve (t.all,
                  num_sub=count(t.subject by t.id where t.subject != ""),
                  num_auth=count(t.name by t.id where t.name != ""),
                        len_abs=length(t.abstract))
.SORT title,code        /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.HEADER title
/* The calculation below contains the following variables and constants: */
/* line_number is the current line number on the page   (RW variable)    */
/* page_length is the current page length in the report (RW variable)    */
/* num_sub is the number of subjects for this book (calculated above)    */
/* num_auth is the number of authors for this book (calculated above)    */
/* len_abs is the total number of characters in the abstract (calculated)*/
/* 6 is the number of lines taken up by the headers                      */
/* 40 is the number of character per line when abstract is printed       */
/* These variables are used to determine if there is enough              */
/* room left on the page to print the next block of data.                */
        .IF num_sub > num_auth and
                line_number + num_sub + 6 + len_abs/40 > page_length .THEN
                        .NEWPAGE
        .ELSEIF  num_auth > num_sub and
                line_number + num_auth + 6 + len_abs/40 > page_length .THEN
                        .NEWPAGE
        .ENDIF
                .LEFTMARGIN 0
                .ULCHARACTER "="
                .UNDERLINE
                .PRINT title .NEWLINE
                .NOUNDERLINE
                .BLOCK  /*  start a block after printing the master info */
.HEADER code
        .ULCHARACTER "-"
/*  test the value of code and set the margin appropriately  */
        .IF code = 1 .THEN
                .TOP    /*  goto the top of the block when code is 1  */
                .LEFTMARGIN 5
                .UNDERLINE
                .PRINT "Authors" .NEWLINE
                .NOUNDERLINE
        .ELSEIF code = 2 .THEN
                .TOP    /*  goto the top of the block when code is 2  */
                .LEFTMARGIN 20
                .UNDERLINE
                .PRINT "Subjects" .NEWLINE
                .NOUNDERLINE
        .ELSEIF code = 3 .THEN
                .BOTTOM /*  goto the bottom of the block when code is 3  */
                .NEWLINE
                .LEFTMARGIN 0
                .UNDERLINE
                .PRINT "Abstract" .NEWLINE
                .NOUNDERLINE
        .ENDIF
.DETAIL
/*  test the value of code to see which column to print  */
        .IF code = 1 .THEN
                .PRINT name .NEWLINE
        .ELSEIF code = 2 .THEN
                .PRINT subject .NEWLINE
        .ELSEIF code = 3 .THEN
                .PRINT abstract(cf0.40) .NEWLINE
        .ENDIF
.FOOTER title
        .ENDBLOCK       /* end the block at the end of the master info */
        .NEWLINE



Summary
-------
In this document we have seen how to create  temporary  tables  to
produce  reports  on  master/multi detail data.  We have seen how to use
block mode effectively to position the detail data  in  the  appropriate
places.  We have seen a technique to avoid spanning pages while in block
mode.  A discussion of  the  differences  in  reporting  using  SQL  was
presented.



Releases affected:     all(all.all) -   Releases not affected: 
Errors:                                                             
Bugs/SIRS:                                                             
------------------------------------------------------------------------------
Ingres Database Reference
To William's Home Page

© William Yuan 2000

Email William