Ingres 11.0 | LABEL Example

Character-based Querying and Reporting Tools User Guide > Character-based Querying and Reporting Tools User Guide > F. Report-Writer Report Examples > LABEL Example

Was this helpful?

LABEL Example

The LABEL example shows a report that prints mailing labels three across the page. The base table, subscriber, is a mailing list containing the name, post office box, address, city, state and zip code for each label. If there is no post office box for the label, the field is left blank.

Additional details on the base table layout are provided in the following tables:

Subscriber Table Definition

Column Name	Type	Length	Nulls	Defaults
name	char	20	yes	no
po_box	char	20	yes	no
address	char	20	yes	no
city	char	20	yes	no
state	char	2	yes	no
zip	integer	4	yes	no

Subscriber Data for the Sample Report

name	po_ box	Address	city	state	zip
Betty Clark		2556 Carey Rd	Boston	MA	01002
Ming Ho		1020 The Parkway	Mamaroneck	NY	10543
Pat McTigue		Route 146	Trumbell	CT	04239
T. Shigio	1234	201 Emperor Lane	Rye	NY	10101
Marvin Blumbert		17 Saville Row	Carmel	CA	93001
Carlos Ramos		2459 39th Ave	San Francisco	CA	94121
AnastassiosVasos		722 Fourth St.	Gualala	CA	95035
Mario Verducci	x-207	General Delivery	Middletown	WA	98112

The report formatting statements are described here:

• Report-Writer sorts the data by zip code.

• The report first begins a block so that the labels can be printed across the page.

• The labels are assumed to be $lbl_width wide and $lbl_length number of lines long. The value of $lbl_width must be greater than the actual number of characters printed and less than the page width, $pg_columns. The number of lines actually printed depends on the number of .newline statements in the report. Because $lbl_length is used to determine page breaks, it must be less than the number of lines per page, $pg_lines.

• Report-Writer creates a label by printing all fields of the table across four lines. If the field for the post office box is blank, the corresponding line cannot be printed.

• Some labels are best printed one set per page. This is useful if FF is used and the lines per page on the printer is set to the number of lines on the label. To print one set per page, adjust the value of $pg_lines to be equal to the $lbl_lines + 1.

• Report-Writer moves the left margin for the next label one label's width to the right of the previous left margin, if doing so does not cause the label to move beyond the right margin (for example, if only 1 or 2 labels have been formatted for a line which can fit three). When no more room exists on the line, the block of labels ends, the .endblock statement moves the report to the top of the next block of labels, the left margin is reset to 0 and a new block of labels begins. When the report runs out of data the block automatically ends, whether or not there is space left in the block buffer.

• This report uses declared variables without prompts. This means that the values of the variables must be passed in from the command line. The following report was run using these commands typed on a single line:

Windows:

report rwsqldb labels (pg_lines=11, pg_columns=79,lbl_width=25, lbl_length=5)

UNIX:

report rwsqldb labels "(pg_lines=11, pg_columns=79, lbl_width=25, lbl_length=5)"

** LABEL. Write out three across mailing labels
** with suppression of blank PO boxes.
*/
.NAME labels
.OUTPUT labels.out
.SHORTREMARK Prints data in mailing label format:
.LONGREMARK
Requires parameters pg_columns, pg_lines, lbl_width, and lbl_length
passed in on the command line. They will not be prompted for. The
following values will result in three labels across and two down:
(pg_lines=11,pg_columns=79,lbl_width=25,lbl_length=5) By varying the
values you can vary the layout of the labels.
.ENDREMARK
.QUERY select name, po_box, address, city, state, zip
        from subscriber
.SORT zip
.DECLARE
        pg_columns = int, /* number of characters across the page */
        pg_lines = int, /* number of lines per page */
        lbl_width = int, /* the number of characters across one label*/
        lbl_length = int /* the number of lines in one label */
.PL $pg_lines
.PW $pg_columns
.RM $pg_columns
.LM 0
.HEADER report
        .NEED $lbl_length
        .BLOCK
.HEADER page
        .NEED $lbl_length
        .BLOCK
.DETAIL
        .TOP
        .LINESTART
        .PRINTLN name
        .IF po_box != ' ' .THEN
                    .PRINTLN 'PO Box ', po_box
        .ENDIF
        .PRINTLN address(cf0.30)
        .PRINTLN city (c0),',',state(c0),' ',zip('nnnnn')
        .IF left_margin + ($lbl_width * 2) < right_margin .THEN
                .LM + $lbl_width
            .ELSE
                .ENDBLOCK
                .NL
                .NEED $lbl_length
                .BLOCK
                .LM 0
        .ENDIF
.FOOTER page
        .ENDBLOCK
.FOOTER report
        .ENDBLOCK

Completed Report

Betty Clark            Pat McTigue            Ming Ho
2556 Carey Rd.         Route 146              1020 The Parkway
Boston, MA 01002       Trumbell, CT 04239     Mamaroneck, NY 10012
T. Shigio              Marvin Blumbert        Carlos Ramos
PO Box 1234            17 Saville Row         2459 39th Ave
201 Emperor Lane       Carmel, CA 93001       San Francisco, CA 94121
Rye, NY 10101
Anastassios Vasos      Mario Verducci
722 Fourth St.         PO Box X‑207
Gualala, CA 95035      General Delivery
                       Middletown, WA 98112

Last modified date: 01/30/2023