Report Headers and Footers | Advanced Reports

Table of contents:

Timestamping Your Spool Files

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.

Figure 7-1. Report headers and footers versus page headers and footers

figs/sqp2_0701.gif

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

Timestamping Your Spool Files

If you have a report that you run on a regular basis and you want to preserve the spooled output from each run, you can use the technique described in "Getting the Current Date into a Header" to generate spool file names that include the date and time:

SET TERMOUT OFF

COLUMN time_now NEW_VALUE spool_time

SELECT TO_CHAR(SYSDATE,'YYYYMMDD-HHMISS') time_now

FROM dual;

SPOOL report&spool_time

SELECT ...

SPOOL OFF

An example of a spool filename resulting from this script fragment is the name report20040627-075632.lst (the extension defaults to .lst ). In this way, you can use the current date and time to generate a unique spool file name each time you run a script. If you require granularity in excess of one second, use SYSTIMESTAMP instead of SYSDATE.

Please note that I don't recommend this technique as a reliable way of generating unique filenames in a multiuser scenario because two users can run the same script simultaneously . The technique is handy, though, in preserving the output from multiple runs of a cron job.

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 "DollarsCharged" FORMAT 9,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 ,380.00

Marusia Churai Enterprise Resource 24 ,056.00

 Planning System

...

Mykola Leontovych Data Warehouse 9 ,089.00

 Maintenance



Mykola Leontovych VPN Implementation 16 ,936.00

=============================================================

 Page 1

...

 Dollars

Employee Name Project Name Hours Charged

-------------------- -------------------- ------ ------------

 Implementation



Igor Sikorsky Data Warehouse 4 0.00

 Maintenance

...

Mykhailo Verbytsky Data Warehouse 16 ,800.00

 Maintenance



Mykhailo Verbytsky VPN Implementation 16 ,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.

Introduction to SQL*Plus

Command-Line SQL*Plus