PQL Procedures 
|
| PQL Procedures | | Quick Report |
REPORT command with a
PRINT clause.
A Full Report
is specified using the REPORT command without the
PRINT clause.
Consider using the Full Report when:
REPORT command. These control:
SORT or BREAK
options, the procedure table is sorted. As each record is processed, a number of
conditions may occur:
REPORT keeps track of lines on a page. When the current page
is full, the page footing is printed and a new page is created. The page counter
is incremented, the heading for the new page is written and then column headers
are written.
REPORT tracks any specified Break
Variables. When the value of a break variable changes, break actions are
taken. Break actions include the printing of a subtotal line and possibly
triggering a page break.
The printing of detail lines can be suppressed.
| |
REPORT FILENAME = filename
PRINT = {variable [(['heading' LABEL] [position] [format])]...| ALL
[ BOOLEAN = (logical_expression)]
[ BREAK = [(n)] variable [([A|D][G][C][P]['text'])],...]
[ FOOTING = [LEFT | RIGHT | CENTER] (footing) ]
[ HEADCENTER ]
[ HEADING = [LEFT | RIGHT | CENTER] (heading) ]
[ MISSCHAR = character ]
[ NOCENTER ]
[ NOCOLHEAD ]
[ NODETAIL ]
[ NOGROUPING ]
[ NOPAGEHEAD ]
[ NOPAGING ]
[ NOSORT ]
[ NOSUBTOTALS ]
[ NOTOTALS ]
[ NOUNDERCOL ]
[ NOUNDERHEAD ]
[ PAGEWIDTH = c ]
[ PAGELENGTH = l ]
[ PAGESIZE = l, c ]
[ PAGELIMIT = n ]
[ SAMPLE = fraction ]
[ SHOWMISS ]
[ SORT = [(n)] variable [(A|D)], ... ]
[ SPACING = SINGLE | DOUBLE | TRIPLE
COLHEAD (b,a)
DETAIL (b,a)
SUBTOTALS (b,a)
TOTALS (b,a) ]
[ SUBTOTALS = {break_var [(subtotal_var, ...)|(subtotal_var)} ...]]
[ TOTALS = variable, ......[('total_text')] ]
[ UPPERCASE ]
FILENAME
| Specify the filename produced by the procedure. This is a required clause. | ||||||||||||||||||||||||||||
PRINT
|
Specifies that this is a Quick Report. This is a required clause.
Specify the variables required. Each specified variable creates one
column from left to right in the order specified.
| ||||||||||||||||||||||||||||
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. For example, to select all
procedure records where SALARY is greater than or equal to 18000:BOOLEAN = (SALARY GE 18000) | ||||||||||||||||||||||||||||
BREAK
|
Specifies break variables which control subtotaling. Specify the
variables in break level order, most significant first.
| ||||||||||||||||||||||||||||
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. | ||||||||||||||||||||||||||||
FOOTING
|
FOOTING defines one or more lines of text printed
at the bottom of each page of the report. There is no default page footing.
LEFT|RIGHT|CENTER
Left, right or centre justifies the heading or footing. LEFT is the default.output specSpecify the text of the heading or footing in parentheses using the same syntax as the WRITE command. Three system maintained variables may be used in the specification: DATE, for current date; TIME, for current
time; and PAGE, for current page number, are available for use
in the HEADING and FOOTING output
specifications. For example:FOOTING = CENTER ( '- ' PAGE '-' ) | ||||||||||||||||||||||||||||
HEADCENTER
| Centers column headings within each column. By default, column headings for string variables are left justified and column headings for numeric variables are right justified. | ||||||||||||||||||||||||||||
HEADING
|
HEADING defines one or more lines of text printed at the top of each page of the report. The default heading is the date, the time and the page number. Suppress the default heading with the NOHEADING keyword. If the NOPAGING keyword is specified, no headings or footings are produced.The specification for headings and footings have the same clauses: LEFT|RIGHT|CENTER
Left, right or centre justifies the heading or footing. LEFT is the default.output specSpecify the text of the heading or footing in parentheses using the same syntax as the WRITE command. Three system maintained variables may be used in the specification: DATE, for current date; TIME, for current
time; and PAGE, for current page number, are available for use
in the HEADING and FOOTING output
specifications. For example:HEADING = ('Employee Report', 2X, DATE,
60T, 'Page ' PAGE(I3) ) /
| ||||||||||||||||||||||||||||
MISSCHAR
| Specifies the character printed for fields containing missing values. The default character is the asterisk (*). The specified character may be any character (including blank), except the slash (/) and comma (,). | ||||||||||||||||||||||||||||
NOCENTER
|
Left justifies the report. By default, when output to a file,
the report is centred. (If output to CONSOL, the report is left-justified.)
Specify NOCENTER when positioning individual columns using nT
(tab).
| ||||||||||||||||||||||||||||
NOCOLHEAD
|
Turns off column heading printing at all break points. By default,
column headings are printed at break points. If NOCOLHEAD is
specified, specifying individual column headings on the BREAK
option, results in a compilation error.
| ||||||||||||||||||||||||||||
NODETAIL
| Turns off the printing of detail lines, only subtotal lines are printed. By default, detail lines are printed. | ||||||||||||||||||||||||||||
NOGROUPING
| Turns off grouping for all break variables. By default, grouping is on. | ||||||||||||||||||||||||||||
NOPAGEHEAD
| Turns off printing of page headings. By default, page headings are printed. | ||||||||||||||||||||||||||||
NOPAGING
| Suppresses all paging. By default, page ejects are performed when a page is full. | ||||||||||||||||||||||||||||
NOSORT
|
Turns off sorting of procedure records. By default, the records are
sorted when a BREAK or SORT clause is
specified.
| ||||||||||||||||||||||||||||
NOSUBTOTALS
|
Turns off subtotals. By default, subtotals are calculated and
printed for numeric variables when a BREAK clause is
specified.
| ||||||||||||||||||||||||||||
NOTOTALS
| Turns off grand totals. By default, grand totals are calculated and printed for numeric variables. | ||||||||||||||||||||||||||||
NOUNDERCOL
| Turns off underlining of the detail line above subtotals. By default, a detail line above a subtotal is underlined. | ||||||||||||||||||||||||||||
NOUNDERHEAD
| Turns off underlining of column headings. By default, column headings are underlined. Underlines by minus (-) printed one line below the text. | ||||||||||||||||||||||||||||
PAGEWIDTH=n
| Sets the number of print characters per line. The default is to make the report as wide as necessary. | ||||||||||||||||||||||||||||
PAGELENGTH=n
|
Sets the number of print lines per page. The default is 60 lines per
page. The NOEJECT keyword suppresses paging
entirely.
| ||||||||||||||||||||||||||||
PAGESIZE=l,c
| Sets the pagelength and pagewidth in a single statement. l is the number of lines; c is the number of columns. If the required width of a report exceeds the page width, a compilation error message is issued and the program is not executed. | ||||||||||||||||||||||||||||
PAGELIMIT=n
| Sets the maximum number of pages produced. When this limit is reached, the report is terminated. | ||||||||||||||||||||||||||||
SAMPLE
|
Specifies that a random sample of the procedure table records are
used by the REPORT.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 sorting order for the report. 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, to sort on NAME in ascending order and
SALARY in descending order:SORT = NAME SALARY (D) | ||||||||||||||||||||||||||||
SPACING
| Controls the spacing between lines of the report. | ||||||||||||||||||||||||||||
SUBTOTALS
|
Specifies variables to subtotal. The BREAK clause
also specifies when subtotals are printed. By default, all numeric variables,
that do not appear in the BREAK clause, are subtotaled.Two types of variables can be specified on the subtotal clause. Break variables, i.e. the variables which control the printing of the subtotals, and subtotal variables, i.e. variables which are added up to calculate the subtotals. Differentiate these by the use of parentheses (). Specify break variables outside the parentheses; specify subtotal variables inside the parentheses. Either set of variables may be omitted. This gives rise to three possible formats of the SUBTOTALS clause, one with just
break variables, the second with just subtotal variables and the third with
both:SUBTOTALS = break variable, ...Specify break variables to determine which breaks cause subtotal printing. By default, all breaks print subtotals. SUBTOTALS = (subtotal variable, ...)
Specify subtotal variables to determine which numeric variables are subtotaled. By default, all numeric variables which are not break variables are subtotaled. Subtotals cannot be calculated for variables which cause breaks. Subtotals are printed at every breakpoint. SUBTOTALS = break variable1 (subtotal variable1, ...)Specify both break variables and subtotal variables to determine which subtotals print at which breaks. Subtotals in the parenthesised list are printed when the corresponding break variable changes value. For example, subtotals for the variables GROSS and NET are
calculated and printed when STATE changes value; subtotals for the
variable TAXES are calculated and printed when COUNTY
changes values.SUBTOTALS = STATE (GROSS NET)
COUNTY (TAXES)
| ||||||||||||||||||||||||||||
TOTALS
|
Specifies grand totals. By default, grand totals are printed for all
numeric variables. The variable list specifies numeric variables which are then
totaled. To suppress totals, specify the NOTOTALS keyword.The 'total text' string is optional and defines the text printed to the left of the grand totals. If this string is not specified, the default text ' TOTAL' is printed.For example, to calculate totals for GROSS and
NET and print them with the label 'Grand Totals'.TOTALS = GROSS NET ('Grand Totals')
| ||||||||||||||||||||||||||||
UPPERCASE
| Converts all text in the report to uppercase. By default, both upper and lower case are produced. |
| |
REPORT specifications use the procedure table produced by this
program.
RETRIEVAL . PROCESS CASES . RECORD IS EMPLOYEE C Find Last Name . COMPUTE REVNAME = TRIMLR(REVERSE (NAME)) . COMPUTE LEN = ABS (SRST(REVNAME,' ')) . COMPUTE LNAME = REVERSE(SBST(REVNAME,1,LEN-1) . END RECORD IS . PROCESS REC OCCUP . GET VARS POSITION STARTDAT STARTSAL DIVISION . PERFORM PROCS | copy variables to proc table . END PROCESS REC . END CASES REPORT . . . ....... END RETRIEVAL
REPORT FILENAME = REPORT1.REP
PRINT = LNAME DIVISION POSITION STARTDAT STARTSAL
PAGESIZE = 60 , 79
NOTOTALS
Dec 27, 2005 10:07:25 Page 1
LNAME DIVISION POSITION STARTDAT STARTSAL
--------------- -------- -------- -------- --------
Jones 1 4 02 10 80 1500
Jones 1 5 10 15 81 2000
Arblaster 1 6 01 18 80 2500
Black 1 9 10 13 79 2750
Black 1 10 02 18 81 3000
Brown 1 14 10 13 77 3200
Green 1 10 11 04 79 3000
Safer 1 9 05 07 79 2000
Safer 1 10 03 08 81 2500
West 1 12 07 10 81 2200
Moore 1 13 01 01 74 2200
DIVISION and
POSITION are printed rather than their numeric values.
STARTSAL is formatted as money with decimal digits, dollar signs
and commas to separate thousands. The page number is displayed in the footing
and a page header is defined.
REPORT FILENAME = REPORT2.REP
PRINT = LNAME (A12)
DIVISION (L10)
POSITION (L10)
STARTDAT
STARTSAL ( F10.2 , D , C )
HEADING =('Employee Jobs and Starting Salaries' )
FOOTING = CENTER ( '- ' PAGE'-' )
PAGESIZE = 60 , 79
Employee Jobs and Starting Salaries
NAME DIVISION POSITION STARTDAT STARTSAL
----------- -------- --------- -------- ----------
Jones Chemical Laborer 02 10 80 $1,500.00
Jones Chemical Technician 10 15 81 $2,000.00
Arblaster Chemical SnrTechn 01 18 80 $2,500.00
Black Chemical Chemist 10 13 79 $2,750.00
Hiller Corporate Sr Engin 01 12 75 $2,600.00
.......................
.......................
Nugent Corporate Sr Accoun 07 03 78 $2,300.00
Neuman Corporate Engineer 04 12 79 $2,000.00
Pau Manufactur Sr Techn 10 10 78 $2,700.00
Fauntleroy Manufactur Sr Secre 06 11 80 $2,000.00
Josephine Manufactur Director 01 10 76 $3,000.00
Rabinowitz Corporate President 01 01 73 $4,000.00
----------
TOTAL $75,550.00
- 1 -
HEADING clause that causes the
division value label to appear in the page header. The column headers have also
been customised.
REPORT FILENAME = REPORT3.REP
PRINT = DIVISION (L13 , 'Division')
LNAME (A13 , 'Employee' 'Name' )
POSITION (L16 , 'Job' 'Title' )
STARTDAT (DATE 'MM/DD/YY' , 'Job Starting Date')
STARTSAL (F10.2 , D , C , 'Starting' 'Salary')
BREAK = DIVISION ('Division Totals' , P)
TOTALS = STARTSAL ('Company Totals')
HEADING = ([TRIM(VALLAB(DIVISION))
+ ' Division Job History'])
FOOTING = CENTER ( '- ' PAGE '-' )
PAGESIZE = 60 , 79
Chemical Division Job History
Job
Employee Job Starting Starting
Division Name Title Date Salary
--------- --------- --------- --------- ---------
Chemical Jones Laborer 02/10/80 $1,500.00
Jones Technician 10/15/81 $2,000.00
Arblaster SrTechnician 01/18/80 $2,500.00
Black Chemist 10/13/79 $2,750.00
Black Sr Chemist 02/18/81 $3,000.00
Brown Sr Administr 10/13/77 $3,200.00
Green Sr Chemist 11/04/79 $3,000.00
Safer Chemist 05/07/79 $2,000.00
Safer Sr Chemist 03/08/81 $2,500.00
West Sr Engineer 07/10/81 $2,200.00
Moore Administrator 01/01/74 $2,200.00
Moore Sr Administr 12/05/75 $2,700.00
Moore Director 01/07/77 $3,500.00
----------
Division Totals $33,050.00
- 1 -
................
................
................
Company Totals $75,550.00
POSITION within DIVISION but
suppress subtotals and repeated column header for POSITION to get
grouping. The page heading is centred.
REPORT FILENAME = REPORT4.REP
PRINT = DIVISION (L10 , 'Division')
POSITION (L10 , 'Job' 'Title' )
LNAME(A12 , 'Employee' 'Name' )
STARTDAT (DATE'MM/DD/YY','Job'-'Starting'-'Date')
STARTSAL (F10.2 , D , C ,'Starting' 'Salary')
BREAK = DIVISION ('Division Totals' , P , C)
POSITION
SUBTOTALS= DIVISION (STARTSAL)
TOTALS = STARTSAL('Company Totals')
HEADING = CENTER([TRIM(VALLAB(DIVISION)) +
' Division Job History'])
FOOTING = CENTER ( '- ' PAGE '-' )
PAGESIZE = 60 , 79
Chemical Division Job History
Job
Job Employee Starting Starting
Division Title Name Date Salary
-------- ---------- ------------ -------- ----------
Chemical Laborer Jones 02/10/80 $1,500.00
Technician Jones 10/15/81 $2,000.00
Arblaster 01/18/80 $2,500.00
Chemist Black 10/13/79 $2,750.00
Safer 05/07/79 $2,000.00
West 02/18/81 $3,000.00
Green 11/04/79 $3,000.00
Safer 03/08/81 $2,500.00
Sr Enginee West 07/10/81 $2,200.00
Administra Moore 01/01/74 $2,200.00
Sr Adminis Brown 10/13/77 $3,200.00
Moore 12/05/75 $2,700.00
Director Moore 01/07/77 $3,500.00
Division Totals $33,050.00
- 1 -
...........
...........
----------
Company Totals $75,550.00
| |