Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : DB3-ART.ZIP
Filename : SNKCOL.ART

 
Output of file : SNKCOL.ART contained in archive : DB3-ART.ZIP

Snaked Column Reporting

BY SOFTWARE SUPPORT TECHNICIANS

If you need to print directories, parts lists, or catalogs you may have
wondered how to create a report in which the columns wrap like a standard
phone book. You probably realized quite quickly that REPORT FORM cannot wrap
from one column to the next, although it can wrap within a column. You may
then have viewed LABEL FORM with a hopeful eye, but found that although you
can create column reports, you cannot create a column of records and then
place beside it another column of records. What you need is a custom report
program that performs the application for you. The following program,
Snake.PRG, does exactly this.

Snaked column reporting if you are not familiar with the term refers to the
way column contents are laid out on the page. In a standard column-oriented
report such as REPORT FORM, columns are report fields where each row or group
of rows is derived from a record in your database file or View. Columns are
related by row. Snaked column reports, by contrast, are like newspaper
columns. Row order exists only within a column and not across columns. Page
columns are linked bottom to top, the last record in the first column is
followed by the first record in the second column giving you the snaking
effect. Figure 1 gives you a graphic illustration of the relationship of
columns and rows.


ÚÄÄÄÄÄÄ¿ ÚÄÄÄÄÄÄ¿
³  ³ 
xxxxxxxx ³ xxxxxxxxxx ³ xxxxxxxxxx
xxxxxxxx ³ xxxxxxxxxx ³ xxxxxxxxxx
ÀÄÄÄÄÄÙ ÀÄÄÄÄÄÄÙ

Figure 1.


Snake.PRG is designed to be as easy to use and automatic as possible.
Basically all you do to run it is to pass parameters for the data definition
(database file, index file, and filter condition), page format (header and
footer), and column definition (column heading, column contents expression,
and record number flag). The program figures out the rest and based on a
series of configurable memory variables listed at the top of Snake.PRG, prints
the report.

Each report page has standard dimensions you can change in the definition
memory variable table mentioned above. When the snake column report prints
each column is separated by a configurable delimiter string in addition to the
beginning and end of each row. The default value of this string is the
character "|", but you can make it any character or string of characters you
want. The delimiter variable is not a passed parameter but is initialized in
the user definition table at the top of Snake.PRG.

The column contents is an expression that outputs the display for each
column. This expression must be character type and can consist of fields,
memory variables, and literal strings. When Snake places the result of the
contents expression in the column, it always left-justifies it within the
column. If you need to print two fields within a column, one left-justified
and one right-justified, your contents expression must take this into
consideration.

For example, suppose you have two fields, Name and Phone. In your snaked
column report, you want to print Name left-justified and Phone
right-justified, separated by a series of dots like the following:

Jack Smith .......... 555-1212
Marilyn Smith ....... 555-1212

To accomplish this, pass the following as the column contents expression
parameter on the DO Snake command line:

TRIM(Name) + SPACE(1) + REPLICATE(".", 25 - LEN(TRIM(Name) +;
^------Column width
SPACE(1) + TRIM(Phone))) + SPACE(1) + Phone

The consideration here is that the column length must be known prior to
running the report since it is used to determine the number on intermediate
characters between the left-justified field and the right-justified field.
Snake fortunately has a feature that accounts for this. If you want to find
out the column width from the parameters you pass, create a numeric memory
variable "colwidth." Then DO Snake WITH your report parameters. If
"colwidth" exists, Snake calculates report column width placing it in
"colwidth" and RETURNs to the calling program without printing the report.
With this facility, you can DO Snake once to obtain the column width and then
use the value returned as a part of the report field expression if it needs
the column width as an argument. For example,

colwidth = 0
*
* ---Run Snake to determine the report width.
DO Snake WITH
*
* ---Assign "colwidth" to variable you can use in your report
* ---field expression and get rid of the "colwidth" so your
* ---report will print.
newidth = colwidth
RELEASE colwidth
*
* ---Run Snake with the real parameter list using "newidth"
* ---in the report field expression.
DO Snake WITH

Snake provides some other features. These include optional page, record
numbering, and column delimiter. Specifying the string "<#>" in either the
header or footer expression places the current page number in the position
where you placed the special symbol. Specifying 0, 1, or 2 for the record
number flag determines whether an optional record number is placed next to
each record's contents. Zero suppresses the automatic numbering, 1 places the
actual record number beside the record contents, and 2 places a number
representing the position of the record in the printed list. You can as well
specify a column heading that is placed left-justified above each column on
every page of the report.


Setup

To set up, create the file Snake.PRG using the text editor of your choice.
Then enter the program code for Snake and the procedures Reptproc, Spacing,
and Getpage into Snake.PRG. If this seems a little strange, don't be
alarmed. dBASE III PLUS permits a command file to SET PROCEDURE TO itself.

If you wish to change any of the fundamental behaviors of the report, you can
change any of the variables in the user definition table at the top of
Snake.PRG. For specific instructions as to the legal values for variables
refer to the comments within the table.


Usage

In order to begin the program from the dot prompt issue the following command:

DO Snake WITH , [], [],;
[
], [
],;
, [];
,

As you can see from this general syntax statement, there are a number of
optional parameters. If you do not have a value for a specific parameter,
pass a null string ("") in its place.

When you run Snake.PRG, it sends its output to the screen. To direct output
to the printer, SET PRINT ON before you run it and SET PRINT OFF after it
completes. Remember that since it outputs using ?, to suppress output to the
screen, SET CONSOLE ON before you SET PRINT ON. In the same way, you direct
output to the printer, you can direct it to an alternate file using SET
ALTERNATE. The only consideration here is that the number of lines Snake
prints per page is set for 66 (the standard print page length). If you want
to change this, change the memory variables, pf_pl, pf_tm, and pf_bm in the
user definition table. Moreover, if you plan to output with Snake to a number
of different devices, consider adding these memory variables to the parameters
list so you can pass them from the calling program.


* Program ...: Snake.PRG
* Author ....: Olivier Biggerstaff
* Date ......: June 1, 1987
* Note(s) ...: Parameters to be passed as:
*
* pq_dbf ::= database to be used, character string
* pq_ndx ::= index file to be used, character string
* pq_query ::= filter condition, character string
* pc_header ::= page header, character string
* pc_footer ::= page footer, character string
* disp_exp ::= expression to be printed, character string
* colhead ::= heading for each column, character string
* pc_cols ::= number of columns
* pc_recnum ::= status of record number printing, integer
* 0 for no record numbers
* 1 for actual record number
* 2 for ascending list
*
PARAMETERS pq_dbf, pq_ndx, pq_query, pc_header, pc_footer,;
disp_exp, colhead, pc_cols, pc_recnum
SET TALK OFF
SET PROCEDURE TO Snake
pq_ndx = IIF("" = pq_ndx, pq_ndx, " INDEX " + pq_ndx)
USE &pq_dbf.&pq_ndx
SET FILTER TO &pq_query
GOTO TOP
*
* -------------------- User definition table -------------------------
*
* ---Define print format settings.
pc_cdelim = "| " && Column delimiters.
pf_fp = 2 && Footer position, measured from the
bottom of page.
pf_hp = 2 && Header position, measured from the top
of the page.
*
* ---The next three items should add up to equal the length of your page in
lines.
pf_pl = 60 && Page length.
pf_tm = 3 && Top margin.
pf_bm = 3 && Bottom margin.
*
* ---The next two items should add up to equal the width of your page in
characters.
pf_lm = 0 && Left margin.
pf_ll = 79 && Line length.
* -----------------------------------------------------------------
*
* ---Define internal memory variables.
headspace = 1 && Lines between column header and data.
newline = CHR(13) + CHR(10) && New line characters.
recwidth = IIF(pc_recnum > 0,;
LEN(LTRIM(STR(RECCOUNT()))), -1) && Maximum width of record
numbers.
pagechar = "<#>" && Indication for page number.
*
* ---If there is a header, it must be accounted for.
items = IIF("" <> colhead, pf_pl - headspace - 1, pf_pl)
*
DO Reptproc WITH disp_exp, pc_recnum
CLOSE PROCEDURE
SET FILTER TO
SET INDEX TO
USE
RETURN
* EOP Snake


PROCEDURE Reptproc
PARAMETERS disp_exp, pc_recnum
*
* ---Figure the column width.
pf_colwdth = INT(pf_ll - (pc_cols + 1) * LEN(pc_cdelim)) / pc_cols - (recwidth
+ 1)
*
* ---If the variable "colwidth" has a numeric value, pass back the column
width
* ---into that variable and return.
IF TYPE("colwidth") = "N"
colwidth = pf_colwdth
RETURN
ENDIF
*
* ---Pad the display expression with spaces for later truncation.
disp_exp = disp_exp + " + SPACE(" + LTRIM(STR(pf_colwdth)) + ")"
page = 1 && The current page.
recnum = RECNO() && The current record number.
dopage = .T. && Indicates another page to
print.
DO WHILE dopage .AND. .NOT. EOF()
*
* ---Space down to header line, make sure that header will fit inside top
* ---margin, print the header if so, and then space down the correct number
of
* ---lines to get to the first line of data (which may be a column header).
IF pf_hp <= pf_tm
DO Spacing WITH pf_hp - 1
newheader = pc_header
DO Getpage WITH newheader, page
? SPACE((pf_lm + pf_ll - LEN(newheader)) / 2) + newheader
?
DO Spacing WITH pf_tm - pf_hp
ELSE
DO Spacing WITH pf_tm + 1
ENDIF
rownum = 1 && On the first row of the page.
itemnum = 1 && On the first item of the page.
*
* ---Place the column header above each column and then space down to the
* ---first line of data.
IF "" <> colhead
?? SPACE(pf_lm)
colnum = 1
colhead = LEFT(colhead, pf_colwdth + recwidth + 1)
headlen = LEN(colhead)
DO WHILE colnum <= pc_cols
?? SPACE(LEN(pc_cdelim)) + colhead + SPACE(recwidth + 1 + pf_colwdth
- headlen)
colnum = colnum + 1
ENDDO
DO Spacing WITH headspace + 1
rownum = rownum + headspace + 1
ENDIF
*
* ---Now put all the data in for each row.
DO WHILE rownum <= pf_pl
colnum = 1
?? SPACE(pf_lm) + pc_cdelim
* ---Calculate the base number from which the rest of the numbers are
found.
number = (page - 1) * items * pc_cols + itemnum - items
DO WHILE colnum <= pc_cols
number = IIF(pc_recnum = 1, RECNO(), number + items)
IF EOF()
* ---If at end of file, just print spaces.
?? IIF(pc_recnum > 0, SPACE(recwidth + 1), "") + SPACE(pf_colwdth)
+ pc_cdelim
dopage = .F.
ELSE
* ---Otherwise, print the number if necessary and then the data
truncated to fit
* ---the column.
?? IIF(pc_recnum > 0, STR(number, recwidth) + " ", "") +
LEFT(&disp_exp, pf_colwdth) + pc_cdelim
lastrec = RECNO()
SKIP items
ENDIF
colnum = colnum + 1
ENDDO
*
* ---Update the counters and move to the correct record.
rownum = rownum + 1
itemnum = itemnum + 1
GOTO recnum
SKIP
recnum = IIF(EOF(), recnum, RECNO())
*
* ---Get set for the next row.
number = number - items + 1
?
ENDDO
*
* ---Move to the footer line if the footer will fit in the bottom margin.
IF pf_fp <= pf_bm
DO Spacing WITH pf_bm - pf_fp
newfooter = pc_footer
DO Getpage WITH newfooter, page
?? SPACE((pf_lm + pf_ll - LEN(newfooter)) / 2) + newfooter
DO Spacing WITH pf_fp - 1
ELSE
DO Spacing WITH pf_bm - 1
ENDIF
page = page + 1
GOTO lastrec
SKIP
recnum = RECNO()
number = number - items + 1
ENDDO
RETURN
* EOP Reptproc


PROCEDURE Spacing
*
PARAMETERS maxspace
IF maxspace > 0
? REPLICATE(newline, maxspace - 1)
ENDIF
RETURN
* EOP Spacing


PROCEDURE Getpage
*
PARAMETERS string, pagenum
position = AT(pagechar, string)
fullstring = string
IF position > 0
string = LEFT(fullstring, position - 1) + LTRIM(STR(pagenum))
IF position < LEN(fullstring) - LEN(pagechar) + 1
string = string + SUBSTR(fullstring, position + LEN(pagechar))
ENDIF
ENDIF
RETURN
* EOP Getpage


Conclusion

Snaked column reporting is an important report type in the lexicon of database
reporting. It is not the most frequent report type you may use, but it is a
beneficial addition to your library. We have written it to accommodate your
most general needs and at the same make it accessible to novices. We hope you
find it useful.


Special Thanks

Due to the unusual nature of the snaked column report, a number of different
approaches to the problem surfaced as discussion of it passed between
technicians. Although the article presents just one of the many techniques
explored, a number of people contributed heavily to the discussion and they
deserve credit for keeping the idea alive. A special thanks to

Roger Wegehoft
Vince Mendillo
Robert Boies
Tim Lebel
Kenneth Getz
Olivier Biggerstaff
Christopher White