F. Report-Writer Report Examples : Report Creation Using Several Tables : How to Avoid Awkward Page Breaks
Share this page                  
How to Avoid Awkward Page Breaks
Assume you have invested in a new bookcase and have expanded the size of your personal library by many volumes. Now when you combine your three tables, you create a much larger union than before:
The C Programming Language
The C Programming Language
The C Programming Language
The C Programming Language
The C Programming Language
|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|
|1005|The Quiet American                  |Greene        |               |      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|
|1005|The Quiet American                  |              |Vietnam        |      2|
If you create a report from such variable blocks of data, you must issue specific instructions to Report-Writer about where and where not to place page breaks; otherwise, you find that some of your data has been incongruously parceled across two pages. In cases where you use Report-Writer to generate a report from a single, unjoined table, you would use a simple .need statement to establish proper page breaks..
In this case, when generating a report from a joined table, you must use variable parameters to the .need statement to assure proper page breaks, as follows:
1. Change the selection statement in your .query section to a creation of a view in your .setup section.
2. Create two new tables in your .setup section based on that view which contain information about the number of authors or subjects per book.
3. Join the two new tables with the view in the .query section.
4. Alter the report specifications to use the new information for paging.
Note:  The .setup and .cleanup sections can only use the SQL language. For the QUEL implementation of this example, see the QUEL Users Notes section below.
The reason for the three-step process is that SQL requires a special method for the calculation of num_sub and num_auth. In SQL, when you perform a set function on a set of data and group rows together, you cannot place in the select clause any column not also listed in the group by clause, except as an argument to a set function. When a select statement includes a group by clause, any columns listed in the select clause must be single‑valued per group.
Here is the revised specification file for the report, with the new .setup, .cleanup and .query sections:
.NAME books2
.OUTPUT books2.out
      The BOOKS2 report demonstrates using setup and cleanup to
      produce temporary tables.
      create view tempbooks as
            select b.id, b.title, a.name, '' as subject, 1 as code
                  from book b, author a
                  where b.id = a.id
            select b.id, b.title, '' as name, s.subject, 2 as code
                  from book b, subject s
                  where b.id = s.id;
      create table subj_count as
            select id, num_sub=count(subject)
            from tempbooks
            where code = 2
            group by id;
      create table auth_count as
            select id, num_auths=count(name)
            from tempbooks
            where code = 1
            group by id;
      drop tempbooks;
      drop subj_count;
      drop auth_count;
      select      b.id, b.title, b.name, b.subject, b.code,
                  a.num_auths, s.num_sub
      from tempbooks b, subj_count s, auth_count a
      where b.id = a.id and b.id = s.id
.SORT title, code
.BREAK title, code /* title and code will be break columns */
.RIGHTMARGIN 80    /* it is important to set the right margin here */
      lines_in_title = integer,
      authors_column = integer,
      subject_column = integer,
      title_string = varchar(8)
.HEADER report
        /* Initialize variables */
        .LET lines_in_title = 4
        .LET authors_column = 5
        .LET subject_column = 20
.HEADER title
        /* Request the maximum number of lines needed to print all */
        /* book information on one page. */
        .IF num_sub num_auths .THEN
                .need num_sub + $lines_in_title
                .need num_sub + $lines_in_title
        /* Reset margin and print title of book */
        /* start a block after printing the master info */
        .LEFTMARGIN 0
        .ULCHARACTER '='
        .PRINT title .NEWLINE
.HEADER code
        /* goto the top of the block each time code changes */
        /* set the margin to the correct column for the code type */
        .ULCHARACTER '-'
        .IF code = 1 .THEN
                .LEFTMARGIN $authors_column
                    .LET title_string = 'Authors'
        .ELSEIF code = 2 .THEN
                .LEFTMARGIN $subject_column
                    .LET title_string = 'Subject'
        .PRINT $title_string .NEWLINE
        /* 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
.FOOTER title
        .ENDBLOCK /* end the block at the end of the master info */
Computer Programming and Arch.
======== =========== === ====
    Authors    Subject
    -------    -------
    Eckhouse   architecture
    Levy       assembler
Database Systems
======== =======
    Authors    Subject
    -------    -------
    Ullman     Database
The C Programming Language
=== = =========== ========
    Authors    Subject
    -------    -------
    Kernighan  C
    Ritchie    language
The INGRES Papers
=== ====== ======
    Authors      Subject
    -------      -------
    Stonebraker  Database
The Quiet American
=== ===== ========
    Authors    Subject
    -------    -------
    Greene     Vietnam