To have each employee’s data start on a new page, add SKIP PAGE to the list of actions to be performed each time the employee changes. The resulting BREAK command looks like this:

BREAK ON employee_id SKIP PAGE NODUPLICATES -
      ON employee_name NODUPLICATES -
      ON project_id NODUPLICATES -
      ON project_name NODUPLICATES

To add two blank lines between projects, use the SKIP 2 action. SKIP allows you to advance a specified number of lines each time a column’s value changes. It takes one numeric argument specifying the number of lines to advance. Here’s how the BREAK command looks with both the page and line breaks specified:

BREAK ON employee_id SKIP PAGE NODUPLICATES -
      ON employee_name NODUPLICATES -
      ON project_id SKIP 2 NODUPLICATES -
      ON project_name NODUPLICATES

When you run the report using this BREAK setting, the output will look like this:

The Fictional Company


I.S. Department                              Project Hours and Dollars Detail
=============================================================================

                                                                         
                                                                      Dollars
Emp ID Employee Name    Proj ID Project Name     Date         Hours   Charged
------ ---------------- ------- ---------------- ----------- ------ ---------
   101 Jonathan Gennick    1001 Corporate Web    01-Jan-1998      1   $169.00
                                Site

                                                 01-Mar-1998      3   $507.00
                                                 01-May-1998      5   $845.00
                                                 01-Jul-1998      7 $1,183.00
                                                 01-Sep-1998      1   $169.00
                                                 01-Nov-1998      3   $507.00


                           1002 Year 2000 Fixes  01-Feb-1998      7 $1,183.00
                                                 01-Apr-1998      1   $169.00
                                                 01-Jun-1998      3   $507.00
                                                 01-Aug-1998      5   $845.00
                                                 01-Oct-1998      7 $1,183.00
                                                 01-Dec-1998      1   $169.00
...
                             The Fictional Company


I.S. Department                              Project Hours and Dollars Detail
=============================================================================

                                                                      Dollars
Emp ID Employee Name    Proj ID Project Name     Date         Hours   Charged
------ ---------------- ------- ---------------- ----------- ------ ---------
   102 Jenny Gennick       1001 Corporate Web    01-Jan-1998      1   $135.00
                                Site

                                                 01-Mar-1998      3   $405.00
                                                 01-May-1998      5   $675.00

As you can see, each change in employee starts a new page, and there are now two blank lines following each project.

When using BREAK to create page breaks and lines breaks, it is important that the column order specified in your BREAK command match the sort order (or grouping) used for the query. This is very important! Suppose you took the BREAK command just used and turned it around like this:

BREAK ON project_id SKIP 2 NODUPLICATES -
      ON project_name NODUPLICATES -
      ON employee_id SKIP PAGE NODUPLICATES -
      ON employee_name NODUPLICATES

You would find that every change in project resulted in a skip to a new page. Why? Because when SQL*Plus executes a break action for a given column, it first executes the break actions for all columns to the right of it in the list. The reason it does that is because column breaks are also used to trigger the printing of totals and subtotals. If you were totaling up hours by project and employee, and the employee changed, it would be important to print the total hours for the employee’s last project prior to printing the total hours for that employee.

Use the COLUMN command to get the value of the employee name and ID columns into user variables. Instead of specifying a display format for those columns, use the NEW_VALUE and NOPRINT clauses. For example:

COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT

The NEW_VALUE clause tells SQL*Plus to update a user variable with the new contents of the column each time a row is returned from the query. In this example, emp_name_var will be updated by SQL*Plus to contain the most recently retrieved employee name. Likewise, the emp_name_id variable will be updated with the employee ID. It is not necessary to declare these variables. Simply pick some names that make sense and use them. The NOPRINT clause is used to tell SQL*Plus not to include the employee name and ID columns in the report detail.

The next step is to modify the page header to include the employee information. That can be done using this updated TTITLE command:

TTITLE CENTER 'The Fictional Company' SKIP 3 -
       LEFT 'I.S. Department' -
       RIGHT 'Project Hours and Dollars Detail' SKIP 1 -
       LEFT `=============================================================' -
       SKIP 2 'Employee: ' FORMAT 9999 emp_id_var ' ' emp_name_var SKIP 3

The only change to the header is the addition of a fifth line — the bold line in the example. Here’s how to interpret this line:

The employee_name and employee_id columns used a total of 22 characters. Since each column was followed by one blank space, eliminating them from the detail section of the report frees up 24 characters that may be usable elsewhere.

The one column that can benefit from a longer length is the project_name column. This column prints 12 characters wide on the report, but is defined in the database to hold up to 40. A quick look at the actual data shows that all but one project name is 26 characters or less, so let’s increase it to 26 by changing its COLUMN command to:

COLUMN project_name HEADING 'Project Name' FORMAT A26 WORD_WRAPPED

The remaining 10 characters can be taken out of the linesize, which is currently 76, so the new linesize command becomes:

SET LINESIZE 66

By adjusting the linesize, you ensure that the right-justified portions of the page title line up with the right edge of the report. Remember to adjust the number of equal-signs in the TTITLE and BTITLE commands to match the linesize. The report output will now look like this:

The Fictional Company


I.S. Department                   Project Hours and Dollars Detail
==================================================================

Employee:   101 Jonathan Gennick


                                                           Dollars
Proj ID Project Name               Date         Hours      Charged
------- -------------------------- ----------- ------ ------------
   1001 Corporate Web Site         01-Jan-1998      1      $169.00
                                   01-Mar-1998      3      $507.00
                                   01-May-1998      5      $845.00
                                   01-Jul-1998      7    $1,183.00
                                   01-Sep-1998      1      $169.00
                                   01-Nov-1998      3      $507.00


   1002 Year 2000 Fixes            01-Feb-1998      7    $1,183.00
                                   01-Apr-1998      1      $169.00
                                   01-Jun-1998      3      $507.00
                                   01-Aug-1998      5      $845.00
                                   01-Oct-1998      7    $1,183.00
                                   01-Dec-1998      1      $169.00

Here is the final version of the script, incorporating all the changes described in this section:

--Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equals signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 66

--Set up page headings and footings
TTITLE CENTER 'The Fictional Company' SKIP 3 -
       LEFT 'I.S. Department' -
       RIGHT 'Project Hours and Dollars Detail' SKIP 1 -
       LEFT '========================================' -
            '==========================' -
       SKIP 2 'Employee: ' FORMAT 9999 emp_id_var ' ' emp_name_var SKIP 3

BTITLE LEFT '========================================' -
            '==========================' -
       SKIP 1 -
       RIGHT 'Page ' FORMAT 999 SQL.PNO

--Format the columns
COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN project_id HEADING 'Proj ID' FORMAT 9999
COLUMN project_name HEADING 'Project Name' FORMAT A26 WORD_WRAPPED
COLUMN time_log_date HEADING 'Date' FORMAT A11
COLUMN hours_logged HEADING 'Hours' FORMAT 9,999
COLUMN dollars_charged HEADING 'Dollars|Charged' FORMAT $999,999.99

--Breaks and Computations
BREAK ON employee_id SKIP PAGE NODUPLICATES -
      ON employee_name NODUPLICATES -
      ON project_id SKIP 2 NODUPLICATES -
      ON project_name NODUPLICATES

--Execute the query to generate the report.
SELECT E.EMPLOYEE_ID,
       E.EMPLOYEE_NAME,
       P.PROJECT_ID,
       P.PROJECT_NAME,
       TO_CHAR(PH.TIME_LOG_DATE,'dd-Mon-yyyy') time_log_date,
       PH.HOURS_LOGGED,
       PH.DOLLARS_CHARGED
  FROM EMPLOYEE E,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE;

You can print data as part of the page footer using the same method just shown for the page header. The only difference is that you would normally use the OLD_VALUE clause with the COLUMN command rather than NEW_VALUE. That’s because when SQL*Plus prints the footer, it has already read the next detail record from the database. Using NEW_VALUE for data in the footer would cause the footer to display information pertaining to the next page in the report — not something you would normally want to happen.