| ||
| VisualPQL Procedures | | Full Report |
Full Report differs from other procedures in that it is specified with a set of commands rather than a single command. The report specification follows the first part of the program as do the other VisualPQL Procedures.
In contrast to Quick Report, Full Report gives precise control over the program logic and the structure and appearance of the report. Full Report is used when branched reports are required or computations beyond subtotals and totals are needed. It is also used when output formats other than columns are needed and when different sections of the report have different formats.
A Full Report procedure starts with the
REPORT command without
the PRINT option, and ends with an
END REPORT command.
All commands from REPORT to
END REPORT are a single REPORT procedure. A
single program may include an unlimited number of REPORT
procedures. Output from each report procedure is written to a separate file.
As the report executes, each record in the procedure table is processed. The
values in any given procedure table record are set at the time the
PERFORM PROCS command copies the local variables to the
procedure table. Values in the procedure table cannot be updated in the
procedure. New variables (variables that were not used before the report) can be
created and used as required.
If multiple report procedures are specified in one program, the local variables used in one are not available for update in subsequent procedures. In other words, the locally defined variables in the first report become procedure variables in subsequent reports and cannot be modified. If referenced, these contain the last value assigned.
Specify a
BEFORE REPORT or
AFTER REPORT to
create blocks of commands that are executed before or after a report. If any new
local variables are required these are typically declared in the BEFORE
REPORT block using any of the standard PQL
variable definition features.
The key structuring in a report is Break Levels. Breaks are triggered by the change in value of a named variable and determine the appropriate processing for that condition. If a break level block is specified without a variable, it is actioned for every record.
Commands are further broken into Action Blocks. The action blocks identify sets of commands executed within a break level for particular conditions such as when the break level initially happens, for every record in the break level and at the end of the break level.
Once the procedure has identified the appropriate block
to execute, it executes these standard VisualPQL commands.
The primary output for producing a report is the PQL
WRITE command.
Every detail line that appears in the final report gets there
because a WRITE command specified it.
REPORT tracks how full a page is and performs page breaks.
The PAGE EJECT
command also causes a new page.One type of action block, the PAGE BLOCK specifies the commands to be executed when there is a page break. When a page break occurs, all specified page blocks in all levels are executed.
The
HEADING,
HEADING BLOCK,
FOOTING or
FOOTING BLOCK commands
specify the headings or footings which are output when a page break occurs.
HEADING BLOCK or FOOTING BLOCK define
multiple lines, HEADING or FOOTING define a
single line.
These commands are executed according to the flow of control and may alter the heading, but the output is not written until the page break occurs. If the heading or footing is only defined once, the recommended place for these blocks is in the BEFORE REPORT block of commands.
Syntax
The syntax for the REPORT command is:REPORT FILENAME = filename
[ / BOOLEAN = (logical condition)]
[ / MISSCHAR = char ]
[ / PAGESIZE = lines[,chars]]
[ / SAMPLE = fraction [,seed] ]
[ / SORT = [(n)] varname [A|D]...]
REPORT, without the PRINT option, specifies
the full report procedure. Separate multiple parameters on the command with a
slash /.
FILENAME
| Specify the filename produced by the procedure. This is a required clause. |
BOOLEAN
| Selects procedure table records. If the logical expression is true for the record, the record is used in the report. The variable names used in the expression must be procedure variables. |
MISSCHAR | Specifies the character printed for variables having missing values. The default is an asterisk (*). The specified character may be any character including blank, except the slash (/) or comma (,). |
PAGESIZE
| Sets the page length and page width of the Report output file. The default page size is 60 lines per page and 136 print positions (characters) per line. |
SAMPLE
|
Specifies that a random sample of the procedure table records are
used by the procedure. The fraction specifies the percent of records used and is specified as a positive decimal number less than or equal to 1 (one). .25, for example specifies that a 25% sample be used. The optional seed is an integer that is used to initialise the random number generator. |
SORT
| Specifies the order in which the procedure table records and therefore the output are sorted.n is an integer that specifies the maximum number of records to be sorted. The default for this parameter is either the number of records in the database or the value specified in the sortn parameter and need only be specified if the number of records in the procedure table is greater than the default. The procedure table is sorted by the specified variables in variable list order. A variable name followed by (A) or (D) specifies that for that variable the sort is in Ascending order (the default) or in Descending order. |
For example:
REPORT FILENAME = REPORT1.LIS /
SORT = GENDER /
PAGESIZE = 60,132
It is not recommended practice to reference procedure variables in this block.
If procedure variables are referenced, then, if the report contained a
When an
If multiple
A break at a level causes breaks at all lower levels.
The first record triggers the top level break.
It is possible to specify a
logical break condition on the command.
This creates a report that can have different formats
depending on the data values.
The CONNECT TO command can be used
to execute a lower level break without having to respecify it in every logical
branch.
The ON ERROR command is
equivalent to a break level command and deals with records not matching any other logical
break condition.
For each break level, specify Action Block(s) which contain
commands that are executed when the break condition is encountered.
Each action block command initiates a block of commands that is ended by another
action block command or by the end of the break level.
If an action block is not specified,
commands in the break level are considered to be in a detail block.
The four action blocks are:
INITIAL BLOCK which is
executed when the break initially happens.
PAGE BLOCK which is
executed when a page break happens.
DETAIL BLOCK which is
executed for every record in the break level.
AT END BLOCK which is
executed at the end of the break.
Other break levels (either simple or conditional) may be defined within
conditional break levels. Each procedure table record that matches the specified
condition follows the path of break levels nested within it. Typically, this
means that a detail block is defined within each path.
Once a conditional break is specified, specify the entire branch, including any
more deeply nested break levels, before specifying other conditional breaks at
the original level. When specifying conditional break levels, specify a branch
for all possibilities.
Use the ON ERROR command to specify the
path to take for any unanticipated conditions.
A conditional break has additional syntax. Multiple conditions specify the same
level, and the level is further qualified by a condition identifier.
which is a number following the level, separated by a period.
The break level is initiated by the specified condition being met.
Specify the condition in parentheses. For example:
Complex branched reports frequently converge at some lower level, for example,
the specifications for level 4 detail blocks might be identical. Respecifying
identical blocks in different paths is avoided by using
A typical report has one
A special
Typically,
If there are no
A number may be specified on the
Note: A page block does not cause a page break, it is executed when a page break
occurs. To force a page break, use the
Branched report with different formats
External files and Edit Buffers
1
The detail block is executed once for each procedure table record and is used to
increment the count. The
To calculate the averages, a sum and a count of the salaries is calculated.
These are initialised at gender breaks, and incremented in the detail block for
each record. The average is calculated at the end of each gender group in the at
end block:
Changing the previous program to accomplish this is trivial. There are four
things to do: put the Marital Status (
A new break level on Marital Status is added and Marital Status is included in
the sort specification. Placing this break at a higher level than Gender without
specifying any action blocks, means that it serves only to trigger the break
point actions at the lower level. Exactly the same report is produced as before,
except that it is broken every time either Marital Status or Gender changes.
Note how few changes have been made to the program. Changed lines are marked
with an '*' in the comment area.
This is a case where a conditional or branched report is required. When
reporting females, it follows one path through the report code and when
reporting males, it takes another. The layout of the report is as follows:
For Females:
In the report specification, there are three break levels, one to produce a new
report section for each employee, another to deal with the different format for
men and women and the last to group output lines by position. The first break
level is a simple break on the employee
Note in the format for the men, some detail lines have position data and others
do not. The lines that have values for position come from the
Suppose, for example, that the percent of the total payroll each employee's
salary represents is to be written. The calculation of this percentage needs the
sum of all the salaries and each employee's salary. Getting the sum of salaries
simply requires adding up salaries in the retrieval. The problem is that in the
report any given procedure table record only has a partial sum. The basic
technique is to store the sum in a new variable (which is not part of the
procedure table) at the end of the retrieval section in an
Remember that unless the Procedure Table is sorted, the report operates as
The following program produces the required results:
If a file is written in the first part of the program and then read from in the
report, delay execution of the report with a sort parameter and make sure that
the file is closed at the end of the retrieval section and then re-opened before
reading it in the report section. Edit buffers have the advantage of being
randomly accessed for both read and write operations, though the data can only
be retrieved in strings, a single line at a time.
Files may be written from within the report section as well as read. Consider a
report from census data in which detail lines are reported for counties with
summary data by state as a subtotal line. A summary section might be required
which reprints all the subtotal (state) data on a single page at the end of the
report. This could be done by storing all the data in a report array and then
printing it out again in the
Since the subtotal lines are formatted for its
AFTER REPORT
AFTER REPORT
Initiates a block of commands executed once at the end of the report procedure.
If this command is used, it must be the last report block in the report
specification.AFTER REPORT is typically used to print report summary
information such as grand totals and other statistics.SORT clause, these contain the last record in the procedure
table. If the report does not contain a SORT, these contain
the last values put in by the main body of the program.
AT END BLOCK
AT END BLOCK
Initiates a block of commands executed when a break condition is triggered,
before the next INITIAL BLOCK is executed. It is also executed
after the last record has been processed.AT END BLOCK is executed, the old procedure table
record prior to the break condition is current. It is not recommended practice
to reference procedure records in the AT END BLOCK. If a value
from a procedure variable is needed, compute the value into a report variable in
either the INITIAL BLOCK or the DETAIL
BLOCK.AT END BLOCKs along a report path are triggered by
higher level break conditions, all of the AT END BLOCKs are
executed in reverse order, from highest numbered break level outwards.
BEFORE REPORT
BEFORE REPORT
BEFORE REPORT initiates a block of commands executed once at
the beginning of the report. The block is terminated by the first BREAK
LEVEL command. If BEFORE REPORT is specified, it
must be the first command following REPORT. The first
procedure table record is available in the BEFORE REPORT
block. BEFORE REPORT is used to:
For example:
REPORT FILENAME = 'EXAMPLE.REP' / PAGESIZE = 66 , 80 /
BEFORE REPORT
. STRING * 80 TITLE FOOTLINE HEADLINE
. INTEGER SALTOTAL SALCOUNT
. SET TITLE FOOTLINE HEADLINE ('')
. SET SALTOTAL SALCOUNT ( 0 )
. HEADING BLOCK 2
. COMPUTE HEADLINE = 'Salary Report'
. WRITE HEADLINE
. COMPUTE HEADLINE = DATEC( TODAY(0) , ' MM/DD/YY')
. WRITE HEADLINE
. END HEADING BLOCK
. FOOTING 37T 'Page ' PAGE
. PAGE EJECT
BREAK LEVEL 1
..........
BLANK LINES
BLANK LINES n
Skips the specified number of blank lines. Blank lines specified by this command
do not extend across pages. If the command causes a page break, counters are
reset and the new page produced. In contrast, the WRITE
command produces physical blank lines which do span pages.
BREAK LEVEL
BREAK LEVEL {break_level [,break varname ]} |
{break_level.identifier (condition)}
BREAK LEVEL defines a break condition and starts
the block of commands executed when the condition is true. End the block
of commands with the
END BREAK LEVEL command.
Every report has at least one break level and can have as many as necessary.
Multiple break levels are defined hierarchically and are nested within each other.
Each break level is uniquely identified with a number which increases as
more deeply nested levels are defined. That is the highest level is 1, the
next is 2, etc.Simple Break
A simple break is specified with the following syntax:
BREAK LEVEL level [,break varname ]
A simple break is triggered by a change in the value of the named variable from
one procedure table record to the next. For example:
REPORT .... / SORT = GENDER AGE
BREAK LEVEL 1, GENDER
BREAK LEVEL 2, AGE
Conditional Break
Conditional breaks allow specification of different actions that depend on the
value of the break variable. Conditional breaks occur when the value changes to
a particular value, as opposed to simple breaks which occur whenever the value
changes. This branch of the break level is executed when the condition is true.
For example, different report formats might be produced for males as opposed to
females.BREAK LEVEL 1.1 (GENDER = 1)
BREAK LEVEL 1.2 (GENDER = 2)
A conditional break creates a branching structure which may have further lower
levels. These must have unique level numbers.
Instead of additional level numbers, lower level simple breaks can qualify the
level number with from one to three characters. These have no meaning other
than as a label. The level number determines the level. For example:
BREAK LEVEL 1.1 (GENDER = 1)
BREAK LEVEL 2A AGE
BREAK LEVEL 1.2 (GENDER = 2)
BREAK LEVEL 2B AGE
Any conditional lower level breaks must use the level.identifier (N.n) syntax.
CONNECT TO
CONNECT TO level.condition_ident
CONNECT TO specifies that a BREAK LEVEL in
another branch is executed at that point. The BREAK LEVEL
referenced on the CONNECT TO must have been defined previously
and must be a lower level i.e have a numerically higher number.CONNECT
TO. For example:BREAK LEVEL 1.1 (GENDER = 1)
BREAK LEVEL 2.1 (AGE LT 18)
..... commands
BREAK LEVEL 2.2 (AGE GE 18)
..... commands
BREAK LEVEL 1.2 (GENDER = 2)
BREAK LEVEL 2.3 (AGE LT 18)
..... commands
BREAK LEVEL 2.4 (AGE GE 18)
CONNECT TO 2.2
DETAIL BLOCK
DETAIL BLOCK
Commands in the DETAIL BLOCK are executed once for each
procedure table record. If the block is within a conditional break level, it is
only executed for records which satisfy the condition.DETAIL BLOCK in each branch of the
report, though there is no restriction on how many different break levels may
contain detail blocks.
END BREAK LEVEL
END BREAK LEVEL
Defines the end of a break level.
END REPORT
END REPORT
Defines the end of the report procedure. This is not required and is specified
for readability.
FOOTING
FOOTING output_specifications
Specifies the text printed at the bottom of each report page. The text is
written when the page eject occurs. The syntax of the FOOTING
command is identical to that of the
WRITE and the
HEADING command.
In addition, report variables and the system
maintained variables PAGE, DATE, and TIME
may be used to print the current page, date and time. If multiple
FOOTING commands are executed, the output from the most recent
is written. Do not specify both a FOOTING and a
FOOTING BLOCK.
There is no default FOOTING. For example:FOOTING 70T 'Page ' PAGE
FOOTING BLOCK
FOOTING BLOCK n
Specifies a block of commands which creates a footing to be output when a page
break is encountered. The command must appear within a break level or before
report block. The block is terminated with END FOOTING BLOCK.
The WRITE command specifies
the output.
Typically used when
the footer contains multiple lines, when logical conditions control the footer
text and when computations are performed to construct the footer. The maximum
number of output lines is specified on the command. If multiple FOOTING
BLOCK commands are executed, the output from the most recent is
written. Do not specify both a FOOTING BLOCK and a
FOOTING. There is no default FOOTING BLOCK.
For example:
FOOTING BLOCK 1
. IF(PAGE EQ 1) WRITE 33T 'Company Report'
. IF(PAGE GT 1) WRITE 38T '-' PAGE '-'
END FOOTING BLOCK
HEADING
HEADING output_specifications
Specifies the text printed at the top of each report page. The text is written
when the page eject occurs. The syntax of the HEADING command
is identical to that of the
WRITE and the
FOOTING command. In addition, report variables and the system
maintained variables PAGE, DATE, and TIME
may be used to print the current page, date and time. If multiple
HEADING commands are executed, the output from the most recent
is written. Do not specify both a HEADING and a HEADING
BLOCK. There is no default HEADING. For example:
'Company Report' 65T DATE(DATE'Mmm DD, YYYY')
HEADING BLOCK
HEADING BLOCK n
Defines a block of commands executed when a page break is encountered. The
command must appear within a break level or before report block. The block is
terminated with END HEADING BLOCK.
The WRITE
command specifies the output. Typically used when the header contains multiple
lines, when logical conditions control the header and when computations are
performed to construct the header text. Specify the maximum number of output
lines the block can produce on the command. If multiple HEADING
BLOCK commands are executed, the output from the most recent is
written. Do not specify both a HEADING and a HEADING
BLOCK. There is no default HEADING BLOCK. For
example:HEADING BLOCK 3
. COMPUTE HEADLINE = 'Company Report'
. WRITE HEADLINE
. COMPUTE HEADLINE = DATEC (TODAY(0) , ' MM-DD-YY'
. WRITE HEADLINE
. COMPUTE HEADLINE = 'Division: ' + VALLAB(DIVISION)
. WRITE HEADLINE
END HEADING BLOCK
INITIAL BLOCK
INITIAL BLOCK
The INITIAL BLOCK is executed once each time the break
condition is triggered, i.e. when the value of the break variable changes. This
block is executed for the first record.
ON ERROR
ON ERROR [ level.condition ]
The ON ERROR command is a special form of the BREAK
LEVEL command that may be specified once at any conditional break
level. It defines actions for conditions not explicitly covered on other
BREAK LEVEL commands at that level.ON ERROR block may be specified without the level
identifier. Only one such ON ERROR block may be defined and it
may be specified at any point. This block is executed any time a procedure table
record is not covered by a break condition.ON ERROR blocks contain code to display error
messages and terminate the program (with the STOP command).ON ERROR levels of any kind within a report
and an error is detected, i.e., a procedure record is read that does not meet
any of the logical conditions at a branching point, the program and report are
automatically terminated. Any output written to the report file up to the point
of such a termination is preserved.
PAGE BLOCK
PAGE BLOCK [ n ]
The PAGE BLOCK is executed under two conditions. It is
executed on every page eject and is also executed when the break is
triggered.PAGE BLOCK command. If the
command is executed because of the break and fewer lines than this remain on the
current page, a page eject is done. This ensures that there is room for data
after printing out column headers.PAGE EJECT command at a
suitable place in another action block.
PAGE EJECT
PAGE EJECT n
Causes a page break when executed. This can be used to trigger all the page
break dependent code in the report. If a number is specified, this conditionally
executes the Page Eject if fewer than the specified number of lines remain on
the current page. For example, to force a section of the report to begin on a
new page, place the PAGE EJECT in the INITIAL BLOCK.
Examples
The following are examples of full report:
Example 1: A Simple Listing
This example is a very simple full report. It lists the values of the
variable ID, which is the case identifier in the example database.RETRIEVAL
. PROCESS CASES
. GET VARS ID | put ID in local variables
. PERFORM PROCS | copy procedure rec to procedure table
. END CASE
REPORT FILENAME= 'REPORT1.REP' | name the report file
. BREAK LEVEL 1 | dummy break
. DETAIL BLOCK | for each procedure table record
. WRITE ID | write ID to the report file
. END BREAK LEVEL
END REPORT
END RETRIEVAL
The above program illustrates the basic requirements for a report specification.
These requirements are:
Though neither the DETAIL BLOCK nor the
WRITE command are strictly required by the VisualPQL compiler,
if these are deleted, the program would execute but would write nothing to the file.
The report outputs 20 lines, each with an ID number:
2
.....
20
Example 2: Simple Statistics
Suppose a count of the records is required. This requires a minor change to the
previous example:RETRIEVAL
. PROCESS CASES
. GET VARS ID
. PERFORM PROCS
. END CASE
REPORT FILENAME = 'REPORT2.REP'
BEFORE REPORT
. SET IDCOUNT (0)
. BREAK LEVEL 1
. DETAIL BLOCK
. COMPUTE IDCOUNT = IDCOUNT + 1
. AFTER REPORT
. WRITE 'The Count is: ' IDCOUNT
END REPORT
END RETRIEVAL
The count is initialised in a BEFORE REPORT block.AFTER REPORT block is executed when
the entire procedure table has been processed to write out the totals. This
report outputs a single line.The count is: 20
Example 3: A Simple Branched Report
This example, and the next, show two different techniques for producing a count
by sex. The first example uses a conditional break to construct a report that
branches to a different COMPUTE statement depending on the
value of the variable GENDER. Each computation increments a
different counter variable.
RETRIEVAL
. PROCESS CASES
. PROCESS REC EMPLOYEE
. GET VARS GENDER
. PERFORM PROCS
. END REC
. END CASE
REPORT FILENAME ='REPORT3.REP'
BEFORE REPORT
. SET BOYS GIRLS (0) | initialise counter variables
BREAK LEVEL 1.1 (GENDER EQ 1) | do this block for men
. DETAIL BLOCK
. COMPUTE BOYS = BOYS + 1
END BREAK LEVEL
BREAK LEVEL 1.2 (GENDER EQ 2) | do this block for women
. DETAIL BLOCK
. COMPUTE GIRLS = GIRLS + 1
END BREAK LEVEL
AFTER REPORT| write out the grand totals
. WRITE 'Male count is ' BOYS
. WRITE 'Female count is ' GIRLS
END REPORT
END RETRIEVAL
This branches to one of the two conditional break level blocks and increments
either the variable BOYS or GIRLS. This report outputs
only two lines.Male count is 12
Female count is 8
Example 4: Simple Breaks
The next example produces the identical report by a different method. The logic
of this report is to sort the procedure table by GENDER and specify
a break level that breaks on GENDER. All of the men are first in
the table, followed by all of the women. A break is triggered by the first
record in the table and another break is triggered when the value of
GENDER changes from male to female. The initial block is used to
initialise a counter and to get the value label for GENDER. The
counter is incremented in the detail block and the counts are written in the at
end block at the end of each group (each gender).RETRIEVAL
PROCESS CASES
. PROCESS REC EMPLOYEE
. GET VARS GENDER
. PERFORM PROCS
. END REC
END CASE
REPORT FILENAME ='REPORT4.REP'/ SORT = GENDER
BEFORE REPORT
. STRING * 6 SEX | variable for gender label
. INTEGER COUNTER | counter variable
BREAK LEVEL 1 GENDER | simple break on GENDER
. INITIAL BLOCK | when break occurs
. SET COUNTER (0) | initialise counter
. COMPUTE SEX = VALLAB(GENDER) | get gender label
. DETAIL BLOCK | for each record
. COMPUTE COUNTER = COUNTER + 1 | increment counter
. AT END BLOCK | when break is done
. WRITE SEX ' count is ' COUNTER| output count to report
END BREAK LEVEL
END REPORT
END RETRIEVAL
Example 5: Detail Lines and Subtotals
Many reports contain detail lines displaying data from each procedure table
record. In this example, the report is broken into two sections, by gender, and
reports name, current job position (CURRPOS) and salary. Each
section appears on a different page and reports average salary for the section.
It is formatted as below, where x's stand for data.Male Salary Report Page 1
Name Job Title Salary
-------------------- -------------------- ------
xxxxx xxxxxxxxx xxxxxxxxxxxxxxxxxxx xxxx
xxxxxxxx xxxxxxxx xxxxxxxxxxxxxxxxxxx xxxx
xxxxxx xxxxxxxxx xxxxxxxxxxxxxxxxxxx xxxx
------
Average Male Salary xxxx
- - - - new page - - -
Female Salary Report Page 2
Name Job Title Salary
-------------------- -------------------- ------
xxxxx xxxxxxxxx xxxxxxxxxxxxxxxxxxx xxxx
xxxxxxxx xxxxxxxx xxxxxxxxxxxxxxxxxxx xxxx
xxxxxx xxxxxxxxx xxxxxxxxxxxxxxxxxxx xxxx
------
Average Female Salary xxxx
The requirements for this report are that the variables GENDER, NAME,
CURRPOS and SALARY are in the procedure table and that the
procedure table is sorted by GENDER. A break on gender is used to
calculate the average salary and to separate the two sections of the report.
Each gender section is printed on a new page by specifying a PAGE
EJECT when the break occurs. Since the heading and average salary
line change with each gender, these are also calculated at each break.RETRIEVAL
PROCESS CASES
. PROCESS REC EMPLOYEE
. GET VARS GENDER NAME CURRPOS SALARY
. PERFORM PROCS
. END REC
END CASE
REPORT FILENAME = 'REPORT5.REP' / | specify output file
SORT = GENDER | sort by gender
BEFORE REPORT | before we really start
. INTEGER SALSUM SALCNT AVGSAL | declare vars for stats
. STRING * 40 HEADLINE SUBTLINE | declare string vars
BREAK LEVEL 1 GENDER | break on gender
. INITIAL BLOCK | for each new gender
. SET SALSUM SALCNT (0) | initialise sum and count
. COMPUTE HEADLINE = | construct header label
TRIM(VALLAB(GENDER))+
' Salary Report'
. COMPUTE SUBTLINE = | construct subtotal label
'Average '+
TRIM(VALLAB(GENDER))+
' Salary'
. HEADING HEADLINE 44T 'Page 'PAGE| define heading
. PAGE EJECT | force a newpage
. PAGE BLOCK | at each new page break
. WRITE 'Name' | output column headers
22T 'Job Title'
44T 'Salary'
. WRITE '--------------------'| output col underlines
22T '--------------------'
44T '------'
. DETAIL BLOCK | for each table record
. COMPUTE SALSUM = SALSUM + SALARY| increment salary sum
. COMPUTE SALCNT = SALCNT + 1 | increment salary count
. WRITE NAME(A20) | output data line
22T [VALLAB(CURRPOS)](A20)
44T SALARY(I6)
. AT END BLOCK | when gender is done
. COMPUTE AVGSAL = SALSUM / SALCNT| average salary
. WRITE 44T '------' | output subt underline
. WRITE SUBTLINE 44T AVGSAL (I6) | output subtotal line
END BREAK LEVEL
END REPORT
END RETRIEVAL
Example 6: Multiple Breaks
Suppose the same report is required with a different report section for every
combination of Gender and Marital Status giving four sections, married men,
single men, married women and single women.MARSTAT) into the
procedure table; change the header to include the marital status label; get the
same label into the average salary display line; ensure that the report is
broken by both Gender and Marital Status.RETRIEVAL
. PROCESS CASES
. PROCESS REC EMPLOYEE
. GET VARS MARSTAT GENDER NAME CURRPOS SALARY |* add MARSTAT
. PERFORM PROCS
. END REC
. END CASE
REPORT FILENAME = 'REPORT6.REP'
/ SORT = MARSTAT GENDER |* add marstat
BEFORE REPORT | before we really start
. INTEGER SALSUM SALCNT AVGSAL | declare vars for stats
. STRING * 40 HEADLINE SUBTLINE | declare string vars
BREAK LEVEL 1 MARSTAT |*break on marstat to trigger
END BREAK LEVEL | actions at next break level
BREAK LEVEL 2 GENDER |*break on gender (note new #)
. INITIAL BLOCK | for each new gender/marstat
. SET SALSUM SALCNT (0) | initialise sum and count
. COMPUTE HEADLINE = |construct header label
TRIM(VALLAB(MARSTAT)) |*added marstat label
+' '+TRIM(VALLAB(GENDER))
+' Salary Report'
. COMPUTE SUBTLINE = |construct subtotal label
'Average'+
TRIM(VALLAB(MARSTAT)) |*added marstat label
+' '+TRIM(VALLAB(GENDER))+
' Salary'
. HEADING HEADLINE 44T 'Page 'PAGE | define heading
. PAGE EJECT | force a newpage
. PAGE BLOCK | at each new page or break
. WRITE 'Name' | output column headers
22T 'JobTitle'
44T 'Salary'
. WRITE '--------------------'| outputcol underlines
22T '--------------------'
44T '------'
. DETAIL BLOCK | for each proc table record
. COMPUTE SALSUM = SALSUM + SALARY | increment salary sum
. COMPUTE SALCNT = SALCNT +1 | increment salary count
. WRITE NAME(A20) | outputdata line
22T [VALLAB(CURRPOS)](A20)
44T SALARY(I6)
. AT END BLOCK | when break group is done
. COMPUTE AVGSAL =SALSUM / SALCNT| calculate average salary
. WRITE 44T '------' | output subt underline
. WRITE SUBTLINE 44T AVGSAL (I6) | output subtotalline
END BREAK LEVEL
END REPORT
END RETRIEVAL
Married Male Salary Report Page 1
Name Job Title Salary
-------------------- --------------------- ------
John D Jones Technician 2150
James A Arblaster Sr Technician 2650
Jack Brown Sr Administrator 3350
------
Average Married Male Salary 2862
Married Female Salary Report Page 2
Name Job Title Salary
-------------------- --------------------- ------
Carol F Safer Sr Chemist 1650
Bonnie Rosen Director 3200
.......
.......
Example 7: A Branched Report with Differing Formats
Consider a report that is broken into sections, where the sections contain
different data depending on the value of some variable. For example an
employment history by employee, with certain data for men and other data for
women.Employee Report date Page x
ID Number xx
Name xxxxxxxxxx
Gender Female
Date of Birth xxxxxxxxxxxxx
Title Salary
xxxxxxxxxxxx xxxx
xxxxxxxxxxxx xxxx
xxxxxxxxxxxx xxxx
xxxxxxxxxx xxxx
xxxxxxxxxx xxxx
xxxxxxxxxx xxxx
For Males:Employee Report date Page x
ID Number xx
Name xxxxxxxxxx
Gender Male
Date of Birth xxxxxxxxxxxxx
Position Title Date Salary Rating
xx xxxxxxxxxxxxxxxx xxxxxxxx xxxx xxxxxxxxx
xxxxxxxx xxxx xxxxxxxxx
xx xxxxxxxxxxxxxxxx xxxxxxxx xxxx xxxxxxxxx
xxxxxxxx xxxx xxxxxxxxx
Variables are read from three record types in the COMPANY database.
The report is a complete salary history of each employee, including salaries and
dates from two different record types. The initial program puts the values of
STARTSAL from the OCCUP record type and
NEWSAL from the REVIEW record type into a single
variable. The same thing happens with STARTDAT and
REVDATE. ID number. The second level
is a conditional break on the value of Gender that formats the data differently
for each sex.OCCUP
record and those that don't come from REVIEW records at the
position printed in the previous line. This requires different processing
depending on which record type the data came from with one of two
WRITE commands
with different line formats. Following is the
code for the report.RETRIEVAL
PROCESS CASES
. PROCESS REC 1
. GET VARS ID NAME GENDER BIRTHDAY | rec 1 vars to proc rec
. END REC
. PROCESS REC2
. GET VARS POSITION | put position in proc rec
. GET VARS DATE SALARY = | put revdate and startsal
STARTDAT STARTSAL | into vars date, salary
. SET RECTYPE (2) | set record typeflag
. PERFORM PROCS | copy rec 2 to proc table
. PROCESS REC 3 VIA (POSITION) | get rec 3 using position
. GET VARS RATING | put rating in procedure rec
. GET VARS DATE SALARY = | put revdate and newsal
REVDATE NEWSAL | into vars date, salary
. SET RECTYPE (3) | set record type flag
. PERFORM PROCS | copy rec 3 to proc table
. END REC
. END REC
END CASES
REPORT FILENAME = REPORT7.REP /SORT = ID GENDER POSITION DATE
BEFORE REPORT
. HEADING BLOCK 7
. WRITE 'Employee Report' 2X DATE 60T 'Page' PAGE(I3)//
. WRITE 'ID Number' 17T ID
. WRITE 'Name'17T NAME
. WRITE 'Gender ' 17T [VALLAB(GENDER)]
. WRITE 'Date of Birth' 17T BIRTHDAY(DATE'Mmm DD, YYYY')
. END HEADING BLOCK
. BREAK LEVEL 1 ID | break on each employee
. INITIAL BLOCK
. PAGE EJECT | put each employee on a new page
END BREAK LEVEL
BREAK LEVEL 2.1, (GENDER EQ 1) | take this branch for men
. PAGE BLOCK | at break or page eject
. BLANK LINES 2 | output 2 blank lines
. WRITE 'Position Title' 32T 'Date' 42T 'Salary' 50T 'Rating'
END BREAK LEVEL
BREAK LEVEL 3A, POSITION | for every new position
. DETAIL BLOCK | for every procedure rec
. IFTHEN (RECTYPE EQ 2) | do following if rec 2
. WRITE POSITION(I8), | output data line with
10T [VALLAB(POSITION)] (A20) | position information
32T DATE(DATE'MM/DD/YY')
42T SALARY(I6)
50T 'n/a' | no rating data in rec 2
. ELSEIF (RECTYPE EQ 3) | do following if rec 3
. WRITE 32T DATE (DATE'MM/DD/YY')| output data without
42T SALARY(I6) | position data
50T [VALLAB(RATING)]
. END IF
. AT END BLOCK | before next Position
. BLANK LINES 1 | output a blank line
END BREAK LEVEL
BREAK LEVEL 2.2, (GENDER EQ 2) | take this branch for women
. PAGE BLOCK | at break or page eject
. BLANK LINES 2 | output 2 blank lines
. WRITE 20T 'Title' 42T,'Salary' | output column headers
END BREAK LEVEL
BREAK LEVEL 3B,POSITION | break on position
. DETAIL BLOCK | for each procedure record
. WRITE 20T [VALLAB(POSITION)](A20) 42T SALARY(I6)
. AT END BLOCK | before next position
. BLANK LINES 1 | output a blank line
END BREAK LEVEL
END REPORT
END RETRIEVAL
Employee Report 06/21/91 Page 1
ID Number 1
Name John D Jones
Gender Male
Date of Birth Jan 15, 1938
Position Title Date Salary Rating
4 Laborer 02/10/80 1500 n/a
04/10/80 1600 Good
06/10/80 1650 Very Good
5 Technician 10/15/81 2000 n/a
12/15/81 2100 Good
02/10/82 2150 Very Good
Employee Report 06/21/91 Page 2
ID Number 2
Name James A Arblaster
Gender Male
Date of Birth Dec 07, 1942
Position Title Date Salary Rating
6 Sr Technician 01/18/80 2500 n/a
03/21/80 2550 Acceptable
05/02/80 2600 Good
08/13/80 2650 Very Good
Employee Report 06/21/91 Page 3
ID Number 3
Name Mary Black
Gender Female
Date of Birth Aug 10, 1953
Job Title Salary
Chemist 2750
Chemist 2800
Chemist 2850
Chemist 2900
Sr Chemist 3000
Sr Chemist 3100
Sr Chemist 3150
Example 8: Underlines
Reports frequently contain underlined column headers. Typically, these are
produced with a
WRITE commands
that contains the appropriate
number of dashes as quoted strings. An alternative is to have a string variable
that contains nothing but dashes and to use that variable repeatedly on the
WRITE statement, controlling the number of dashes with the
formatting options. Consider a typical WRITE statement with
quoted dashes:WRITE '----------' 2X '-----' 2X '------------' 2X '-------'
If there is a variable UL, filled with dashes, this gives the same
output with:WRITE UL(A10) 2X UL(A5) 2X UL(A13) 2X UL(A7)
Following is an example of part of a report program using this technique.BEFORE REPORT
. STRING UL
. SET UL ('--------------------')
BREAK LEVEL 1
. PAGE BLOCK
. WRITE 'Employee Name' 25T 'Job Title' 50T 'Salary'
. WRITE UL(A20) 25T UL(A20)50T UL(A6)
. DETAIL BLOCK
. WRITE NAME (A20) 25T JOBTITLE(A20) 50T SALARY(I6)
END BREAK LEVEL
Example 9: Totals and After Report
Sometimes reports need a data value calculated in the pre-report part of the
program, which is needed for every procedure record. AFTER
RETRIEVAL block and then to access it in the report.PERFORM PROCS sends each procedure table record. Since the
percent cannot be calculated until all the database records have been processed,
delay execution with a dummy sort. Create a dummy variable (DUMMY)
that always has the value 0 for this purpose. If the report is sorted for other
reasons, this is unnecessary.RETRIEVAL
SET TOTSAL DUMMY (0) | initialise total salary
PROCESS CASES
. PROCESS REC EMPLOYEE
. GET VARS NAME SALARY
. COMPUTE TOTSAL = TOTSAL + SALARY | increment total salary
. PERFORM PROCS
. END REC
END CASE
AFTER RETRIEVAL
SALTOT = TOTSAL | put total in variable
REPORT FILENAME = REPORT8.REP /
SORT = DUMMY | do a dummy sort to delay execution
BREAK LEVEL 1
DETAIL BLOCK
COMPUTE PCT = (SALARY/SALTOT) * 100 | calculate percentage
WRITE NAME (A20) | output the data
2X SALARY (I4)
2X PCT ('999.99')
END BREAK LEVEL
Example 10: Using External Files and Edit Buffers
Full Report includes the capability of reading from and writing to files and of
using edit buffers. In the previous example, it would have been possible to
write the sum to a file or edit buffer and then read it back.AFTER REPORT section.WRITE
statement, it takes almost no extra effort to write once to the report and a
second time to a file. In the AFTER REPORT section read the
lines of text in the file and print them. The general structure of the report
would be:BEFORE REPORT
STRING * 80 FILETEXT
INTEGER STATE1 TO STATE5 | declare state total vars
OPEN TFILE.TXT WRITE
BREAK LEVEL 1 STATE
.INITIAL BLOCK
. SET STATE1 TO STATE5(0) | initialise state totals
. AT END BLOCK
. WRITE STATE1 TO STATE5 | write totals to report
. WRITE (TFILE.TXT) STATE1 TO STATE5 | write totals to file
END BREAK LEVEL
BREAK LEVEL 2 COUNTY
. DETAIL BLOCK
. WRITE COUNTY1 COUNTY2 COUNTY3 COUNTY4 COUNTY5
| increment state totals
| (e.g. STATE1 = STATE1 +COUNTY1)
END BREAK LEVEL
AFTER REPORT
CLOSE TFILE.TXT | close file
OPEN TFILE.TXT READ | open file for read
PAGE EJECT | start on a new page
WRITE 30T 'State Summary Data' // | pagetitle
LOOP | loop thru records
. READ (TFILE.TXT,ERR=EOF)FILETEXT(A80)| read a line from file
. WRITE FILETEXT | write line to report
END LOOP
EOF:
END REPORT