Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

7.3 Report Headers and
Footers

Report headers and footers work much like page headers and footers,
except that they print only once in a report. A report header prints
at the beginning of the report, after the first page title and before
the first detail line. A report footer prints at the end of a report,
after the last detail line and before the final page footer. Figure 7-1 illustrates this by showing how the different
types of headers and footers print relative to each other in a
three-page report.

You define a report header using the
REPHEADER
command. The REPFOOTER command defines a report footer. The
parameters you can use with REPHEADER and REPFOOTER are the same as,
and work the same way as, those used with the TTITLE command.

One use for a report header is to define a report title that prints
only on the first page of a report, leaving only column titles at the
top of all subsequent pages. You can use a report footer to mark the
end of a report, so you know for sure whether you have all the pages.
Here is an example showing how these things can be done.

Recall that the Project Hours and Dollars Report from Example 5-5 used the following commands to define page
headers and footers:

TTITLE CENTER 'The Fictional Company' SKIP 3 -
LEFT 'I.S. Department' -
RIGHT 'Project Hours and Dollars Report' SKIP 1 -
LEFT '============================================================='
BTITLE LEFT '=============================================================' -
SKIP 1 -
RIGHT 'Page ' FORMAT 999 SQL.PNO

The TTITLE command defined a title containing
the name of the report, which in this case was printed on each page
of the report. By replacing TTITLE with REPHEADER and adding a
command to turn TTITLE off, you will cause the title containing the
report name to print only once. The following example shows how this
is done and also defines a report footer:

TTITLE OFF
REPFOOTER CENTER '*** End of Hours and Dollars Report ***'
REPHEADER CENTER 'The Fictional Company' SKIP 3 -
LEFT 'I.S. Department' -
RIGHT 'Project Hours and Dollars Report' SKIP 1 -
LEFT '============================================================='
BTITLE LEFT '=============================================================' -
SKIP 1 -
RIGHT 'Page ' FORMAT 999 SQL.PNO The report footer, defined with the REPFOOTER command, will print on
the last page, after the last detail record, to mark the end of the
report. Example 7-5 shows this new incarnation of
the Project Hours and Dollars Report.

Example 7-5. Project Hours and Dollars Report formatted using report headers and footers

SET ECHO OFF
--Set up pagesize parameters
SET NEWPAGE 1
SET PAGESIZE 55
--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 61
--Set up page headings and footings
TTITLE OFF
REPFOOTER CENTER '***End of Hours and Dollars Report ***'
REPHEADER CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Report" SKIP 1 -
LEFT "============================================================="
BTITLE LEFT "=============================================================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED
COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Turn off feedback and set TERMOUT off to prevent the
--report being scrolled to the screen.
SET FEEDBACK OFF
SET TERMOUT OFF
--Execute the query to generate the report.
SELECT e.employee_name,
p.project_name,
SUM(ph.hours_logged) hours_logged,
SUM(ph.dollars_charged) dollars_charged
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
GROUP BY E.EMPLOYEE_ID, E.EMPLOYEE_NAME,
P.PROJECT_ID, P.PROJECT_NAME;
EXIT When you run the script in Example 7-5, your results
will be as follows:

The Fictional Company
I.S. Department Project Hours and Dollars Report
=============================================================
Dollars
Employee Name Project Name Hours Charged
-------------------- -------------------- ------ ------------
Marusia Churai Corporate Web Site 20 $3,380.00
Marusia Churai Enterprise Resource 24 $4,056.00
Planning System
...
Mykola Leontovych Data Warehouse 9 $1,089.00
Maintenance
Mykola Leontovych VPN Implementation 16 $1,936.00
=============================================================
Page 1
...
Dollars
Employee Name Project Name Hours Charged
-------------------- -------------------- ------ ------------
Implementation
Igor Sikorsky Data Warehouse 4 $280.00
Maintenance
...
Mykhailo Verbytsky Data Warehouse 16 $4,800.00
Maintenance
Mykhailo Verbytsky VPN Implementation 16 $4,800.00
***End of Hours and Dollars Report ***
....
=============================================================
Page 3 As you can see, the report title printed only on the first page of
the report. Subsequent pages began with the column titles. The report
footer printed on the last page following the last detail line. When
you are working with report headers and footers, keep in mind that
these report elements still print within the context of a page. Page
titles and footers print on each page regardless of whether a report
header or footer prints on that page. Had the above report included a
page title (TTITLE), the page title would have printed on the first
page prior to the report header.