PQL Procedures 
|
| PQL 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.
| |
REPORT command is:
REPORT FILENAME = filename [ BOOLEAN = (logical condition)] [ MISSCHAR = char ] [ PAGESIZE = lines[,chars]] [ SAMPLE = fraction] [ SHOWMISS ] [ SORT = [(n)] varname [A|D]...]
REPORT, without the PRINT option, specifies
the full report procedure.
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. |
SHOWMISS
| Specifies that a variable's original missing values are printed for fields containing missing values. The default character is the asterisk (*). Missing values are always excluded from totals - this option only affects printing. |
SORT
| Specifies the sequence of the output. 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
| |
AFTER REPORTInitiates 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.
It is not recommended practice to reference procedure variables in this block.
If procedure variables are referenced, then, if the report contained a
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 BLOCKInitiates 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.
When an 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.
If multiple 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 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:
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 nSkips 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 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.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.
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
| |
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:
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 AGEAny conditional lower level breaks must use the level.identifier (N.n) syntax.
| |
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.
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 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 BLOCKCommands 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.
A typical report has one 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 LEVELDefines the end of a break level.
| |
END REPORTDefines the end of the report procedure. This is not required and is specified for readability.
| |
FOOTING output_specificationsSpecifies 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 nSpecifies 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 output_specificationsSpecifies 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 nDefines 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 BLOCKThe
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 [ 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.
A single ON ERROR block may be specified without the level
identifier 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.
Typically, ON ERROR blocks contain code to display error messages and terminate the program (with the STOP command).
If there are no 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 [ 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.
A number may be specified on the 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.
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 PAGE EJECT command at a
suitable place in another action block.
| |
PAGE EJECT nCauses 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.
| |
Branched report with different formats
External files and Edit Buffers
| |
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 RETRIEVALThe above program illustrates the basic requirements for a report specification. These requirements are:
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:
1
2
.....
20
| |
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 RETRIEVALThe count is initialised in a
BEFORE REPORT block.
The detail block is executed once for each procedure table record and is used to
increment the count. The 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
| |
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 RETRIEVALThis 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
| |
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
| |
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.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:
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
| |
Changing the previous program to accomplish this is trivial. There are four
things to do: put the Marital Status (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.
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.
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
.......
| |
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:
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.
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 ID number. The second level
is a conditional break on the value of Gender that formats the data differently
for each sex.
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 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 REC 2
. 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 Jan 03, 2006 Page 1
ID Number 1
Name John D Jones
Gender Male
Date of Birth Jan 08, 1968
Position Title Date Salary Rating
4 Laborer 02/04/03 1500 n/a
04/05/03 1600 Good
06/05/03 1650 Very Good
5 Technician 10/09/04 2000 n/a
12/09/04 2100 Good
02/04/05 2150 Very Good
Employee Report Jan 03, 2006 Page 2
ID Number 2
Name James A Arblaster
Gender Male
Date of Birth Dec 02, 1962
Position Title Date Salary Rating
6 Sr Technician 01/12/03 2500 n/a
03/16/03 2550 Acceptable
04/27/03 2600 Good
08/08/03 2650 Very Good
Employee Report Jan 03, 2006 Page 3
ID Number 3
Name Mary Black
Gender Female
Date of Birth Aug 05, 1973
Title Salary
Chemist 2750
Chemist 2800
Chemist 2850
Chemist 2900
Sr Chemist 3000
Sr Chemist 3100
Sr Chemist 3150
| |
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
| |
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 AFTER
RETRIEVAL block and then to access it in the report.
Remember that unless the Procedure Table is sorted, the report operates as
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.
The following program produces the required results:
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
| |
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 AFTER REPORT section.
Since the subtotal lines are formatted for its 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
| |