790.TXT
=====================================================================
Using PAGEMODE
=====================================================================
PRODUCT: R:BASE VERSION: 4.5++ or Higher
=====================================================================
CATALOG: Forms, Reports & Labels AREA : Reports
PAGEMODE is an exciting new feature in R:BASE 4.5 ++ that allows you
to create reports with endless possibilities. PAGEMODE allows you to:
Produce multi-column reports
Create reports from tables with many-to-many relationships
Create reports with more than one detail section
Design different layouts for different pages of the report
Create reports with pages defined longer than 84 lines long or wider
than 255 characters
Format odd and even pages differently
Customize the report to always place break headers and footers together
on the same page
Place a different page header on each page
Number every other page of a report
Have fully conditional data printing, for example, don't print a
detail section if the balance is zero
PAGEMODE does not use the regular R:BASE report writer. Instead, you
use the R:BASE programming language to create the report definition.
The R:BASE report writer processes data a row at a time and then
prints that row. With PAGEMODE, a report is processed a page at a
time. A virtual page (many rows of data) is laid out in memory, then
sent to the printer or to a file. This, combined with the power of the
R:BASE programming language is what allows you to create so many
different and complex reports.
The DECLARE CURSOR command is usually used to retrieve the data for
printing a report created with PAGEMODE. For additional information
and tips on using DECLARE CURSOR, refer to the July/August 1994 issue
of the Exchange.
The virtual page is defined by the current settings of LINES and
WIDTH. Once PAGEMODE is ON, these settings cannot be changed. A report
page is laid out in memory, then sent to the printer or a file using
either the NEWPAGE or OUTPUT screen commands. To lay out the page, use
the WRITE or SHOW VARIABLE commands with AT to place the data in a
particular location, or use SELECT with the new SELMARGIN setting to
designate the starting column for the output.
Because the R:BASE programming language is used to create a PAGEMODE
report, you need to approach the report definition task differently.
Creating reports using PAGEMODE is not as easy as using the regular
R:BASE report writer; you can't see the layout on the screen as you
create the report. Also, since you are writing a program, many
iterations are usually needed to get the layout just right.
The Basic PAGEMODE Program Structure When creating a PAGEMODE report,
you follow the same basic structure for all reports:
Define row and column location variables
Use a DECLARE CURSOR to retrieve the data
Use the row and column variables to display the data
Send the completed page to the printer
Variables are set up to hold the row and column numbers for displaying
data. These variables are controlled and modified by conditions in your
program. In addition to row and column location variables, you may also
want row count or line count variables to indicate actual location on
the virtual page. The variables display data in columns and tell when
you have reached the end of a page.
A page in a report created with PAGEMODE is not a physical thing, but
a virtual page that you have defined using the LINE and WIDTH settings
in R:BASE. You define the virtual page, place text and data on the
virtual page, then send the page to the printer or to a file. A
PAGEMODE report prints a page at a time, but the data is still
retrieved and processed a row at a time. After defining the page
size using the LINES and WIDTH settings, PAGEMODE is invoked by the
SET PAGEMODE ON command. PAGEMODE is not relevant to screen display;
you must set your output to a file or the printer when placing data
on a virtual page.
You have the most control over data placement by using the DECLARE
CURSOR command to retrieve the data. DECLARE CURSOR retrieves the data
from the specified table or view a row at a time. You then manipulate
the data from that row, determine where to display it on the virtual
page and get the next row of data. As you get each row of data,
variables are checked in your program to see if a break has occurred
or end of page has been reached. The NEWPAGE or OUTPUT SCREEN commands
are used to send the current virtual page and get the next page ready
for data.
Working with Page Layout
The virtual page size is determined by the settings for LINES and
WIDTH when PAGEMODE is turned ON. LINES and WIDTH cannot be changed
while PAGEMODE is on. To change the page size within a report, turn
PAGEMODE OFF, change the LINES or WIDTH setting, and then turn
PAGEMODE back on. The size you set determines where you can place
data on the virtual page.
Row and column positions for display are specified as literal values
or are kept in variables, for example, vRow and vColumn. Using
variables makes it easy to display data on the page one row after the
other. Note that you cannot use expressions with the AT row, column
option; you must set the correct row and column values first using
SET VARIABLE. The vRow variable can be used to check for end of page,
or you can set up a row counter variable, vRowCount, for example.
There is no automatic form feed at the end of a page in a PAGEMODE
report. You control the form feeds (new pages) through your program
code by sending a NEWPAGE or OUTPUT SCREEN command. Thus, your
program needs to be aware of the current setting for LINES so you can
test for the end of a page. The current row count is compared to a
constant value or to the LINES setting to check for the end of page.
The CVAL function is used to place the current line setting in a
variable. For example,
SET VARIABLE vLines = (CVAL('LINES'))
Then, an IF statement compares the vLines variable with the vRowCount
variable. For example,
IF vRowCount >= .vLines THEN
NEWPAGE
SET VARIABLE vRowCount = 1
SET VARIABLE vRow = 1
SET VARIABLE vColumn = 1
ENDIF
The vRowCount, vRow, and vColumn variables must be reset for each new
page. Leave a five line margin at the bottom of the page by comparing
vRowCount to vLines minus five. For example,
IF vRowCount >= (.vLines - 5) THEN
NEWPAGE
SET VARIABLE vRowCount = 1
SET VARIABLE vRow = 6
SET VARIABLE vColumn = 5
ENDIF
Margins at the top of the page and on the left side are determined by
the initial settings for the vRow and Vcolumn variables.
You can calculate the number of rows to print for the next data item,
and compare that to see if all the data will fit on a page. This
calculation is used to make sure break header, detail, and break
footer information all appear on the same page. For example,
SELECT COUNT(*) INTO vNumToPrint +
FROM transmaster WHERE custid = .vCustid
IF (.vRowCount + .vNumToPrint) >= .vLines THEN
NEWPAGE
SET VARIABLE vRowCount = 1
SET VARIABLE vRow = 6
SET VARIABLE vColumn = 5
ENDIF
There are many different ways to keep track of the number of rows that
have been printed on the page. The important thing to remember is that
you need to keep track of it and issue the form feed yourself; it does
not happen automatically.
Checking Column Width
PAGEMODE reports easily print data in a columnar format. Decide on the
column width based on the length of data to display, then simply set
variables to contain the starting column position for each column. For
example,
SET VARIABLE vColumn1 = 5
SET VARIABLE vColumn2 = 30
SET VARIABLE vColumn3 = 55
...
WRITE .vName AT .vRow, .vColumn1
WRITE .vName AT .vRow, .vColumn2
WRITE .vName AT .vRow, .vColumn3
The above commands are used to display data in three columns evenly
spaced across the page with a four space margin on the left side of
the page. You determine in your program whether the columns go across
the page and then down, or first down the page and then across. You
should set up the column positions at the beginning of your program
rather than automatically incrementing the column display variable by
a certain amount and checking against the WIDTH setting.
The number of columns can be dynamically calculated based on the
maximum length of values to display for a group of data. Use the
SLEN and MAX functions to find the maximum data length for the group
of data.
-- compute the maximum text length
SELECT MAX(SLEN(title)) INTO vLen +
FROM books WHERE lstnm = .vLName
-- set the number of columns based
-- on the maximum text length
IF vLen <= 25 THEN
SET VAR vNumCols = 3
SET VAR vColumn1 = 5
SET VAR vColumn2 = 30
SET VAR vColumn3 = 55
ELSE
IF vLen <= 50 THEN
SET VAR vNumCols = 2
SET VAR vColumn1 = 5
SET VAR vColumn2 = 40
ELSE
SET VAR vNumCols = 1
SET VAR vColumn1 = 5
ENDIF
ENDIF
Set SELMARGIN to specify the beginning column for SELECT command
output. SELECT and SELMARGIN can be used with WHERE CURRENT OF
CURSOR to display a row of data starting at any column position.
The example programs show detailed code for setting the column width.
Ways to Display Data in PAGEMODE
The key to using PAGEMODE is working with the data layout. There are
three commands used to display data on the virtual page: WRITE, SHOW
VARIABLE, and SELECT.
WRITE
The WRITE command is the one you'll use the most. You can specify a
display width and wrap text using the new =w option, format the data
directly with the USING format option, specify the page location using
AT row column, and display either variables or literal text. The WRITE
command does not reposition the cursor, however, so it is not as easy
to use with wrapped text.
SHOW VARIABLE
The SHOW VARIABLE command is similar to the WRITE command in that you
can display variables at a specific location using AT row column, and
you have the =w option for wrapping text. You don't have the
formatting options, however, and can only display variables, not
literal text. To format data, first execute a separate SET VARIABLE
command using the FORMAT function. Literal text is first placed in a
variable, and then shown. The SHOW VARIABLE command repositions the
cursor, which makes it easier to use with wrapped text.
SELECT
The SELECT command is not as easy to use or as useful as the WRITE and
SHOW VARIABLE commands. There is no straightforward way to limit the
number of rows displayed unless you use SELECT with DECLARE CURSOR,
which displays the current row only. You can select only columns
specified in the cursor declaration; you can't use any expressions or
functions in the SELECT command. To specify a column display width,
the =w option must be included in the cursor declaration. Also, SELECT
does not have an AT row column option, but displays at the current
cursor location. There are situations where SELECT is applicable,
however. When using it, SET HEADING OFF, and use SET SELMARGIN to
designate the starting column for display.
Wrapping Text
All three commands that display data will wrap text or note data;
however, even though the WRITE command wraps lines, you have no way of
knowing how many lines were wrapped and where the current cursor
position is. WRITE does not reposition the cursor location. Both the
SHOW VARIABLE and SELECT commands reposition the cursor to the next
row after the command is completed.
When using SHOW VAR or SELECT, both of which reposition the cursor to
the next row, ISTAT('PAGEROW') is used to indicate the new row
position. PAGEROW is a new ISTAT function for use with PAGEMODE
reports only. For example:
SHOW VAR vNoteVar=40 AT .vRow, .vColumn
SET VAR vRow = (ISTAT('PAGEROW'))
If using the WRITE command, calculate the number of lines to wrap by
taking the length of the variable and dividing by the width wrapped
in. This gives you the number to increment the vRow variable by. For
example:
SET VAR vLength = (SLEN(.vNoteVar))
SET VAR vWrap = (NINT(.vLength / 40))
WRITE .vNoteVar=40 AT .vRow, .vColumn
SET VAR vRow = (.vRow +.vWrap + 1)
Creating the Different Report Sections
In a report created with PAGEMODE, the report sections aren't made for
you; you create them in your program code. Following are guidelines
for creating the various report sections. As with a regular R:BASE
report, you can have any combination of sections in a PAGEMODE report.
Page Header/Footer
A page header is data that is written at the top of every page. In a
regular R:BASE report, this data is printed before any other data on
the page is processed or printed. The page header can be written at
any time in a PAGEMODE report, even after all the other data for a
page has been processed.
Often code is placed at the beginning of the program to write the page
header. Wherever the code is placed in a PAGEMODE report program, the
code references row numbers to write the data at the top of the page,
and the code executes only once for each page. The code is normally
placed either before or after the WHILE loop that processes detail
data.
Like the page header, the page footer is only written once per page
and can be written in code placed either at the beginning or end of
page. The row number where the data is written indicates whether it is
a page header or page footer. The placement in the program code for
both the page header and footer is determined by when all the data
needed to be printed for the header or footer has been processed. If
you have all the necessary information, it is easier to print the
header at the beginning of the page because you don't need to save any
of the data in variables for later printing. Or, place code for both
the page header and footer just before the page is sent.
The columnar phone list report example shows how to write a page
header that has both data from the first row and data from the last
row on the page.
Break Header/Footer
The easiest way to set up a breakpoint for a PAGEMODE report is to use
cursors. Declare a cursor for just the column that will be the
breakpoint and use a DISTINCT so only unique values are retrieved.
This cursor controls the program and defines the breaks. Each time the
cursor fetches a new row, a break occurs. To access detail
information, define a second, nested, cursor. The second cursor is on
the same table and retrieves all the other data needed from the row.
The second cursor is dependent on the first cursor; the second cursor
retrieves rows based on the current breakpoint value.
The first cursor uses a WHILE loop to step through each distinct
breakpoint value. This WHILE loop prints the break header and footer.
The WHILE loop for the second cursor steps through all the detail data
for the break.
The code sample below does not have all the code to invoke PAGEMODE
and write out the data, but shows the logic of using cursors to
define breakpoints and how the cursors easily identify where a group
of data begins or ends. The code sets up a breakpoint on custid;
transaction information is printed grouped by each customer. By
declaring a cursor to be distinct for a single column, we emulate a
breakpoint in a report. Each row that is fetched is a new breakpoint
value. Code that is executed between getting each row is used to
calculate subtotals or print out detail data.
This code example returns all customers who have transactions; cursor
c1 is defined to look at data from the Transmaster table. For a report
on all customers whether or not they have had transactions, declare
the cursor on the Customer table instead of the Transmaster table.
Then cursor c2 will return a "no data found" error for customers with
no transactions. No outer join view is needed.
SET VAR vCustid TEXT
DECLARE c1 CURSOR FOR SELECT DISTINCT custid +
FROM transmaster
DECLARE c2 CURSOR FOR SELECT transid, empid, +
invoicetotal, transdate FROM transmaster +
WHERE custid = .vCustid
OPEN c1
FETCH c1 INTO vCustid Ind1
WHILE SQLCODE <> 100 THEN
-- This is the break header position,
-- the first customer ID is retrieved.
-- Cursor c2 fetches all transaction rows for
-- that customer.
OPEN c2 RESET
FETCH c2 INTO vTransid vind1, vEmpid vind2, +
vInvoiceTotal vind3, vTransDate vind4
WHILE SQLCODE <> 100 THEN
--- The detail rows are processed here.
FETCH c2 INTO vTransid vind1, vEmpid vind2,
vInvoiceTotal vind3, vTransDate vind4
ENDWHILE
-- The break footer position is here,
-- right before the next customer row is fetched.
FETCH c1 INTO .vCustid
ENDWHILE
Reports can print one break per page or many breaks. To print only
one break per page, send the virtual page with NEWPAGE or OUTPUT
SCREEN before fetching the next row. Both the break header and footer
can be easily printed at the end of the break since the break header
data will appear in the same location on each page - just remember to
save the header data into variables for printing later.
When printing many breaks per page, check in the program code for the
end of the page. Make sure there is enough room left on the page to
print the break header, detail and break footer data. Otherwise, send
the virtual page. With many breaks per page, it is easier to print the
header at the beginning of the break; then you have the correct row
number to place the header data.
Multiple Breaks
Expand the breakpoint concept outlined above to set up multiple
breakpoints. Each breakpoint is a cursor; each succeeding breakpoint
cursor is dependent on the previous cursor. Remember, place all the
cursor declarations at the beginning of the program and use OPEN RESET
for speed. OPEN RESET can't be used, however, on a cursor declaration
that includes an ORDER BY clause. For those cursors, CLOSE and OPEN
the cursor. Be sure to initialize all variables used in the WHERE
clauses of the DECLARE CURSOR statements. The variables won't contain
actual data until the row for the cursor is fetched.
This code example shows how to first group customers by state, then
group transactions by customer. Again, the commands for invoking
PAGEMODE and displaying data are not included; this code is used to
demonstrate breakpoint logic only.
-- Initialize variables
SET VAR vCustState TEXT, vCustid TEXT
-- Set up the breakpoints with cursors.
-- Cursor c1 is break 1, cursor c2 is break 2,
-- cursor c3 retrieves the detail data.
DECLARE c1 CURSOR FOR +
SELECT DISTINCT custstate FROM customer
DECLARE c2 CURSOR FOR +
SELECT DISTINCT custid FROM transmaster +
WHERE custid IN +
(SELECT custid FROM customer +
WHERE custstate = .vCustState)
DECLARE c3 CURSOR FOR +
SELECT transid, empid, invoicetotal, transdate +
FROM transmaster WHERE custid = .vCustid
OPEN c1
FETCH c1 INTO vCustState Ind1
WHILE SQLCODE <> 100 THEN
-- This is the break header 1 position,
-- the first state value is retrieved.
-- Cursor c2 then fetches all customer rows
-- for that particular state
OPEN c2 RESET
FETCH c2 INTO vCustid Ind2
WHILE SQLCODE <> 100 THEN
-- This is the break header 2 position,
-- the first customer row. Cursor c3 then
-- fetches all transaction rows for
-- that customer
OPEN c3 RESET
FETCH c3 INTO vTransid vind1, vEmpid vind2, +
vInvoiceTotal vind3, vTransDate vind4
WHILE SQLCODE <> 100 THEN
-- The detail rows are processed here
FETCH c3 INTO vTransid vind1, vEmpid vind2, +
vInvoiceTotal vind3, vTransDate vind4
ENDWHILE
-- Break footer 2 position is here, +
-- right before the next
-- customer row is fetched.
FETCH c2 INTO vCustid Ind2
ENDWHILE
-- Break footer 1 position is here, after
-- all the customer data has been fetched,
-- before the next state value is fetched.
FETCH c1 INTO .vCustid
ENDWHILE
Detail
The detail section in a regular R:BASE report is data that is printed
for every row in the table or view. The detail section may be data
printed in columns or row by row. Detail data can be placed anywhere
on the page from any place in the program code, but usually the detail
data is written within the innermost DECLARE CURSOR, since that is
where each row of data is accessed.
Debugging a Report created with PAGEMODE
A PAGEMODE report is a program; debug it using TRACE just as you would
any other command file or application. Pay special attention to the
row, column, and page length variables. These variables are likely to
be set to a wrong value causing the output to be off.
We recommend you direct your PAGEMODE report to a file rather than to
the printer directly. Some time lags are likely between pages. Also,
outputting to a file lets you quickly check for formatting problems.
There is no built-in Print preview; add the WHERE clause, WHERE
LIMIT = 10, to restrict the amount of data and to check the
formatting. The Preview option in the R:BASE report writer uses this
same WHERE clause.
Note that when using PAGEMODE, your report will always end with a form
feed. The OUTPUT SCREEN sends the current page and ends the page with
a form feed. You can't get rid of it - you must send the final page,
which sends a form feed.
In regular R:BASE reports, you learn to base a report on the many
table and define lookups to the one table, or base the report on a
view. Writing a PAGEMODE report, you don't need to define lookups or
create a view. You can declare a cursor on your one table, then a
second, nested cursor on the many table. Note that if you are only
doing calculations on a table, you may not need to declare a cursor
on it. Use the SET VAR or SELECT...INTO commands for defining
calculations or lookups.
You might see extra blank lines in your output. PAGEMODE sets up a
page based on the settings for LINES and WIDTH; that entire page is
sent to the printer or file, including any blank lines. Thus, if LINES
are set to 60, each time a virtual page is sent, 60 lines are sent.
Examples
Now let's look at some examples to see how to apply this logic to
actual PAGEMODE reports. The best way to understand how PAGEMODE works
is to look at some examples of reports. We'll start with a simple
columnar report such as a phone list. Then, add break points and put
a break header on every page when the break detail is more than one
page. These code examples can be used as templates for your
applications. The examples use the Hifi sample database.
There are common elements to all the examples. LINES are set
artificially low in each example to force a page break to occur. All
the examples output to a file and the file is displayed to the screen
when the report is complete. MESSAGES are set off to make sure they
don't interfere with the report data. Common elements are commented in
the first example where they are used; they are not commented in later
examples for space considerations and to make it easier for you to see
he changes and differences in the examples. Remember these are just
examples. There are many ways to create PAGEMODE reports, these
examples are designed to show you some of the basic techniques.
Example 1 - prints an employee telephone list in columnar format. The
page header data is actually written at the end of the page so it
includes data from the first row printed on the page, and data from
the last row printed on the page.
*(PHONE1.RMD)
*(example 1 - a columnar report with a page header written at the
end of the page)
CON hifi
CLS
SET MESSAGE OFF
SET PAGEMODE OFF
SET LINES 10
DECLARE c1 CURSOR FOR +
SELECT (LastName + ',' & FirstName), Phone, Lastname +
FROM salespeople ORDER BY lastname
OPEN c1
FETCH c1 INTO vName i1 ,vPhone i2, vLastname i3
SET VAR vCol INT = 1, vRow INT = 3, vRowLimit = 9
SET VAR vFirst TEXT = .vLastname, +
vLast TEXT = .vLastname, +
vPage INT = 1
SET PAGEMODE ON
OUTPUT phone.out
WHILE SQLCODE <> 100 THEN
SET VAR vRow = (.vRow + 1)
IF vRow >= .vRowLimit THEN
IF vCol = 40 THEN
WRITE .vFirst '-' .vLast AT 2 1
WRITE 'Page' .vPage AT 2 68
WRITE '----------------------------------' AT 3 1
WRITE '----------------------------------' AT 3 40
NEWPAGE
SET VAR vFirst = .vLastname, vLast = .vLastname
SET VAR vPage = (.vPage + 1), vCol = 1
ELSE
SET VAR vCol = 40
ENDIF
SET VAR vRow = 4
ENDIF
WRITE .vName=21 .vPhone AT .vRow .vCol
SET VAR vLast = .vLastname
FETCH c1 INTO vName i1, vPhone i2, vLastname i3
ENDWH
WRITE .vFirst '-' .vLast at 2 1
WRITE 'Page' .vPage at 2 68
WRITE '----------------------------------' at 3 1
WRITE '----------------------------------' at 3 40
OUTPUT SCREEN
CLOSE c1
DROP CURSOR c1
SET PAGEMODE OFF
SET LINES 20
TYPE phone.out
Lines are set artificially low to show two pages.
Note the cursor definition concatenates first and last nameready to
be displayed on the report. Lastname is included twice in the DECLARE
CURSOR statement - the first for display purposes, concatenated with
first name, the second reference is used to retrieve just the last
name for placement in the page heading.
The first row of data is fetched
Before the rows are iteratively retrieved, the row and column values
for display are initialized. In addition, the variable vRowLimit is
used to indicate how many rows to print per column. It is set to one
less than the pagesize (lines) for this report,
The variables are initialized for the page heading. The lastname value
from the first row is saved in the variable vFirst. The variable vLast
is reset within the cursor WHILE loop
Turn PAGEMODE on, indicate the output device and start looping through
the rows of data
The row display variable is incremented by one for each row that is
fetched. The names start printing on row 4 to allow space for the page
heading.
Check to see if we have reached the end of a page. When displaying one
row of output for each row retrieved from the table, it is easy to use
the vRow variable to track where you are on the page. When wrapping
data, or displaying a variable number of rows of output for each row
of data, use SHOW VAR and (ISTAT('PAGEROW')) to keep track of the
actual page location.
Now check to see if the second column of names has printed. If so,
write the page heading and send the page.
Write the page heading.
Send the page.
Re-initialize variables for page 2.
Column 1 is done, set up parameters for displaying the names in column
2.
Reset the row display variable for either column 2 or the next page.
Write the detail data.
Each row, get the current lastname value. When the page is done the
current value is displayed by the WRITE command that does the page
heading.
Get the next row of detail data.
No more data, write the page heading on the last page.
Close the file, cleanup and display the report.
Example 2 - prints a phone list report using breakpoints. Each break
is printed on a separate page. The data is not printed in columns to
make it easier to see how breakpoints are set up. The employee phone
list data is printed by state.
*(PHONE2.RMD)
*(example 2 - a report with break points.
An employee phone list is printed by state, each state on a separate
page.)
DEL phone.out
CON hifi
CLS
SET MESSAGE OFF
SET PAGEMODE OFF
SET LINES 15
SET VAR vState TEXT
DECLARE c1 CURSOR FOR SELECT DISTINCT state +
FROM salespeople
DECLARE c2 CURSOR FOR +
SELECT (LastName + ',' & FirstName), Phone, Lastname +
FROM salespeople WHERE state = .vState +
ORDER BY lastname
SET VAR vCol INT = 3, +
vRow INT = 2, +
vCount INT = 0, +
vPage INT = 1, +
vPageLimit = (CVAL('LINES'))
SET PAGEMODE ON
OPEN c1
FETCH c1 INTO vState ii1
WHILE SQLCODE <> 100 THEN
OUTPUT phone.out APPEND
WRITE 'List of employees in state of:', .vState, +
' Page:', .vPage AT .vRow, .vCol
SET VAR vRow = (.vRow + 1)
WRITE '----------------------------------' +
AT .vRow, .vCol
OPEN c2
FETCH c2 INTO vName i1 ,vPhone i2, vLastname i3
WHILE SQLCODE <> 100 THEN
SET VAR vRow = (.vRow + 1)
SET VAR vCount = (.vCount + 1)
IF vRow >= .vPageLimit THEN
NEWPAGE
SET VAR vRow = 2, vPage = (.vPage + 1)
WRITE 'List of employees in state of:', .VState, +
' Page:', .VPage AT .vRow, .VCol
SET VAR vRow = (.vRow + 1)
WRITE '----------------------------------' +
AT .vRow, .vCol
ENDIF
WRITE .vName=21 .vPhone AT .vRow .vCol
FETCH c2 INTO vName i1, vPhone i2, vLastname i3
ENDWH
CLOSE c2
SET VAR vRow = (.vRow + 2)
WRITE 'Number of employees for', .vState, 'is', +
.vCount AT .vRow, 10
OUTPUT SCREEN
SET VAR vRow = 3, vPage = (.vPage + 1), vCount = 0
FETCH c1 INTO vState ii1
ENDWH
OUTPUT screen
DROP CURSOR c1
DROP CURSOR c2
SET PAGEMODE OFF
SET LINES 20
TYPE phone.out
Initialize the variable used in the WHERE clause of cursor c2.
Set up the breakpoint by declaring a cursor on the break column. The
cursor is specified as DISTINCT to retrieve one row for each unique
value in the column.
Declare a cursor to retrieve the other data to display.
Initialize the row and column display variables. The variable vCol is
set even though the data is not printed in columns. The pagesize is
found by using the CVAL function to query the current setting for
LINES.
Get the first break value.
Opening and closing the file each time through the WHILE loop
eliminates extra form feeds.
The break heading acts as the page heading. The OUTPUT..APPEND command
in combination with the OUTPUT SCREEN at the end of the break puts
each break on a new page.
Begin data retrieval for the first state (break). Cursor c2 is
re-opened with different data for each state (break value).
Increment the row display value, and count the number of records per
break.
Test to see if the end of a page has been reached. If so, send the
page, and print the break header on the next page. If the detail data
is more than will fit on a single page, the break header values repeat
on the next page
Write the detail data.
Write the break footer, all the detail for the break value has been
processed.
The OUTPUT SCREEN sends the current page and closes the file. It puts
the next break on a new page
Get the next state or break value and reset variables.
Close the file, cleanup and display the report.
Example 3 - a breakpoint report with multiple breaks printed per page.
The break heading is repeated on the next page if the data for the
break won't fit on the page. The report prints a page header as well
as a break header and break footer.
*(PHONE3.RMD)
*(example 3 - this example includes a page header
along with the break header. Multiple breaks are
printed per page. The break header is repeated on
subsequent pages if the break detail spans a page)
DEL phone.out
CON hifi
CLS
SET MESSAGE OFF
SET PAGEMODE OFF
SET LINES 25
SET VAR vState TEXT
DECLARE c1 CURSOR FOR SELECT DISTINCT state +
FROM salespeople
DECLARE c2 CURSOR FOR +
SELECT (LastName + ',' & FirstName), Phone, Lastname +
FROM salespeople WHERE state = .vState +
ORDER BY lastname
SET VAR vCol INT = 3, +
vRow INT = 2, +
vPage INT = 1, +
vPageLimit = (CVAL('lines')), +
vCount INT = 0
SET PAGEMODE ON
OUTPUT phone.out
WRITE 'Employee Phone List by State' AT .vRow, 25
SET VAR vRow = (.vRow + 1)
WRITE .#date AT .vRow, 32
WRITE 'Page:', .VPage AT .vRow, 60
SET VAR vRow = (.vRow + 2)
OPEN c1
FETCH c1 INTO vState ii1
WHILE SQLCODE <> 100 THEN
WRITE 'List of employees in state of:', .VState +
AT .vRow, .VCol
SET VAR vRow = (.vRow + 1)
WRITE '----------------------------------' +
AT .vRow, .vCol
OPEN c2
FETCH c2 INTO vName i1 ,vPhone i2, vLastname i3
WHILE SQLCODE <> 100 THEN
SET VAR vRow = (.vRow + 1)
SET VAR vCount = (.vCount + 1)
IF vRow >= .vPageLimit THEN
NEWPAGE
SET VAR vRow = 2, vPage = (.vPage + 1)
WRITE 'Employee Phone List by State' +
AT .vRow, 25
SET VAR vRow = (.vRow + 1)
WRITE .#date AT .vRow, 32
WRITE 'Page:', .vPageAT .vRow, 60
SET VAR vRow = (.vRow + 2)
WRITE 'List of employees in state of:', .VState +
AT .vRow, .VCol
SET VAR vRow = (.vRow + 1)
WRITE '----------------------------------' +
AT .vRow, .vCol
SET VAR vRow = (.vRow + 1)
ENDIF
WRITE .vName=21 .vPhone AT .vRow .vCol
FETCH c2 INTO vName i1, vPhone i2, vLastname i3
ENDWH
CLOSE c2
SET VAR vRow = (.vRow + 2)
WRITE 'Number of employees for', .vState, 'is', +
.vCount AT .vRow, 10
SET VAR vRow = (.vRow + 3), vCount = 0
FETCH c1 INTO vState ii1
ENDWH
OUTPUT screen
DROP CURSOR c1
DROP CURSOR c2
SET PAGEMODE OFF
SET LINES 20
TYPE phone.out
Initialize the variable used in the WHERE clause of cursor c2. Declare
the cursors for the breakpoint and the detail data.
Initialize row, column and page variables. Variable vCount is used to
count the rows and is displayed in the break footer.
Write the page heading for page 1.
Write the break heading.
Begin retrieving the detail data.
Test to see if we are the end of the page.
Send the current page and then write the page heading and break
heading for the next page.
The page heading.
The break heading.
Display the detail data.
All of the detail for the current break value has been processed.
Write the break footer before fetching the next break value from
cursor c1.
Reset variables for the next break.
Close the file, cleanup and display the report.