Report Headers and Footers

2017-11-03 09:05:07

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

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.

Timestamping Your Spool Files

Категории