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

5.4 Master/Detail Reports

You can add page breaks and
line breaks to your reports with the
BREAK
command. BREAK is commonly used to suppress repeating values in
report columns, which commonly occur in master/detail reports. Take a
look at the script in Example 5-6, which generates a
detailed listing of all the time charged to each project by each
employee.

Example 5-6. Detailed listing of time charged to projects

SET ECHO OFF
--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 76
--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
BTITLE LEFT "========================================" -
"====================================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_id HEADING "Emp ID" FORMAT 9999
COLUMN employee_name HEADING "Employee Name" FORMAT A16 WORD_WRAPPED
COLUMN project_id HEADING "Proj ID" FORMAT 9999
COLUMN project_name HEADING "Project Name" FORMAT A12 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
--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 INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
ORDER BY e.employee_id, p.project_id, ph.time_log_date;
EXIT The query in Example 5-6 introduces the use of
Oracle's built-in TO_DATE function to convert
datetime values such as time_log_date to character
strings you and I can read. You'll see examples of
date conversion scattered throughout this book, and Appendix B goes into detail about the various format
elements that provide control over how datetime values are formatted.

When you execute this script, here's what the output
will look like:

The Fictional Company
I.S. Department Project Hours and Dollars Detail
===============================================================
Dollars
Emp ID Employee Name Proj ID Project Name Date Hours Charged
------ ---------------- ------- ------------ ----------- ------ ------------
101 Marusia Churai 1001 Corporate 01-Jan-2004 1 $169.00
Web Site
101 Marusia Churai 1001 Corporate 01-Mar-2004 3 $507.00
Web Site
101 Marusia Churai 1001 Corporate 01-May-2004 5 $845.00
Web Site
...
102 Mykhailo 1001 Corporate 01-May-2004 5 $675.00
Hrushevsky Web Site
102 Mykhailo 1002 Enterprise 01-Feb-2004 7 $945.00
Hrushevsky Resource
Planning
System
...

The first four columns repeat the same values for each
employee/project combination. This clutters the output and makes the
report a bit difficult to follow because you may not see when a value
actually changes. The following sections show how to suppress
duplicate values in a column, making the report less repetitious. You
will also see how to add page and line breaks to improve readability.
Finally, you will learn how to turn this report into a master/detail
report that shows the employee information in the page header with
the detail listed below it.

BREAK lets you specify actions to take whenever a column's value changes. Page breaks, line breaks, and printing of the value (i.e., duplicate suppression) are some examples of the actions you can ask for.

5.4.1 Suppressing Duplicate Column Values

To eliminate repeating values in a
report column, use the BREAK command to specify the NODUPLICATES
action for that column. For example, to eliminate duplicate values in
the employee_id and project_id
columns, you can issue the following command:

BREAK ON employee_id NODUPLICATES ON employee_name NODUPLICATES
ON project_id NODUPLICATES ON project_name NODUPLICATES NODUPLICATES is the default action for BREAK and is almost never
specified explicitly. Instead, the command just shown is usually
simplified:

BREAK ON employee_id ON employee_name ON project_id ON project_name Be sure to sort or group your report by the same columns that you
specify in the BREAK command, and in the same order. The script in
Example 5-6 sorts on employee_id
followed by project_id followed by
time_log_date. The BREAK command just shown
corresponds to the first two of those. Because
employee_id drives the value of
employee_name, there is no need to ORDER BY
employee_name (likewise with
project_id and project_name).

It's fine for the ORDER BY clause to be more
granular than the BREAK command, but the converse is not OK. If you
do not sort your data to correspond to your BREAK command, you will
probably be unhappy with the results.

SQL*Plus allows only one break setting to be in effect at any given time. Unlike the case with COLUMN, multiple BREAK commands do not build on each other, but rather each subsequent BREAK replaces the previous setting. However, any number of ON clauses may be used to accommodate breaks on more than one column.

Adding the BREAK command shown earlier to the report script from
Example 5-6 makes the output look like the
following:

The Fictional Company
I.S. Department Project Hours and Dollars Detail
==================================================
Dollars
Emp ID Employee Name Proj ID Project Name Date Hours Charged
------ ---------------- ------- ------------ ----------- ------ ------------
101 Marusia Churai 1001 Corporate 01-Jan-2004 1 $169.00
Web Site
01-Mar-2004 3 $507.00
01-May-2004 5 $845.00
...
102 Mykhailo 1001 Corporate 01-Jan-2004 1 $135.00
Hrushevsky Web Site
01-Mar-2004 3 $405.00
...

This is an improvement over the previous report version. You can now
spot changes in the employee and project columns.

5.4.2 Page and Line Breaks

To aid readability, you might wish to start a new page when the
employee name changes and to leave one or two blank lines between the
detail for each project. Having each employee start on a new page has
the benefit of allowing you to give each employee his own section of
the report. Perhaps you want each employee to check the hours he has
reported. You can accomplish both these objectives via the SKIP
action of the BREAK command.

5.4.2.1 Adding a page break

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

Because the employee_id and employee_name columns change simultaneously, the page break could have been defined on either column. In this case, I chose employee_id because it is the primary key for the table and can be depended on to be unique for each employee.

5.4.2.2 Adding a line break

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
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

5.4.2.3 Report output with page and line breaks

Example 5-7 shows the report script, with a BREAK
command that generates a page break for each new employee and skips
two lines between projects.

Example 5-7. Detailed time listing, with page and line breaks

SET ECHO OFF
--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 76
--Don't repeat column values, page break for new employees,
--skip a line when projects change.
BREAK ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES
--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
BTITLE LEFT "========================================" -
"====================================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_id HEADING "Emp ID" FORMAT 9999
COLUMN employee_name HEADING "Employee Name" FORMAT A16 WORD_WRAPPED
COLUMN project_id HEADING "Proj ID" FORMAT 9999
COLUMN project_name HEADING "Project Name" FORMAT A12 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
--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 INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
ORDER BY e.employee_id, p.project_id, ph.time_log_date;
EXIT When you run the report, 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 Marusia Churai 1001 Corporate 01-Jan-2004 1 $169.00
Web Site
01-Mar-2004 3 $507.00
01-May-2004 5 $845.00
01-Jul-2004 7 $1,183.00
01-Sep-2004 1 $169.00
01-Nov-2004 3 $507.00
1002 Enterprise 01-Feb-2004 7 $1,183.00
Resource
Planning
System
...
The Fictional Company
I.S. Department Project Hours and Dollars Detail
===============================================================
Dollars
Emp ID Employee Name Proj ID Project Name Date Hours Charged
------ ---------------- ------- ------------ ----------- ------ ------------
102 Mykhailo 1001 Corporate 01-Jan-2004 1 $135.00
Hrushevsky Web Site
01-Mar-2004 3 $405.00
01-May-2004 5 $675.00
...

Each change in employee starts a new page, and two blank lines follow
each project.

Sometimes, column breaks and page breaks coincide. When that happens, SQL*Plus performs both sets of break actions, which can result in some pages that start with leading blank lines. In this example, if a project and a page break occur simultaneously, SQL*Plus first advances to a new page, then prints the two blank lines required for a project break. SQL*Plus has not been designed to recognize that, because of the page break, the two blank lines from the project break are not needed.

When using BREAK to create page breaks and line breaks, you should
ensure that the column order in your BREAK command matches the sort
order (or grouping) used for the query. 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 each 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. It does this because column breaks
are used to trigger the printing of totals and subtotals, which
you'll read about in Chapter 7. Given this particular BREAK command, if
you were totaling up hours by project and employee and the project
changed, it would be important to print the total hours for the final
employee on the previous project before printing data for the new
project.

5.4.3 Master/Detail Formatting

With column breaks on the employee and
project columns, the Project Hours and Dollars Detail report contains
quite a bit of whitespace. This is particularly true under the
Employee Name column because that value changes so
infrequently. This report is a good candidate for conversion to a
master/detail style of report.

A master/detail report is one that displays the value of one record
in a heading and then lists the detail from related records below
that heading. The record shown in the heading is referred to as the
master , and the records shown below that heading
are referred to as detail records.

Three additional steps are needed to convert this report from a
plain, columnar report to the master/detail style of report:

Retrieve the employee name and ID into substitution variables.

Modify the page heading to print the value of those variables.

Revise the report width and the width of the remaining fields.

Substitution variables are text variables that can be used to hold
values retrieved from the database or to hold values entered by a
user. Substitution variables allow you to include report data as part
of a page header or footer, which is just what we are going to do
here. Chapter 8 talks about using these
variables to facilitate user interaction with your SQL*Plus scripts.

5.4.3.1 Retrieve the employee information into substitution variables

Use the COLUMN command to get the value of
the employee name and ID columns into substitution variables. Instead
of specifying a display format for those columns, use the

NEW_VALUE and NOPRINT clauses:

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 corresponding employee ID. Declaring these variables
is unnecessary. Choose some names that make sense and use them. The
NOPRINT clause tells SQL*Plus not to print the employee name and ID
columns as part of the report detail.

5.4.3.2 Modify the page heading to print the employee name and ID

Next, modify the page header to include the employee information.
This can be done using the following, 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 page header is the addition of a fifth line,
which is the bold line in the example. Here's how to
interpret this line:

SKIP 2
Tells SQL*Plus to advance two lines after printing the ruling line of
equal sign characters. This effectively leaves
one blank line before the employee ID and name
are printed.

" Employee: "
This is a quoted literal, so SQL*Plus prints it as it is shown. It
serves to label the information that follows.

FORMAT 9999
Tells SQL*Plus to format any subsequent numeric values in a
four-digit field with no leading zeros.

emp_id_var
Tells SQL*Plus to print the contents of this variable, which contains
the most recently retrieved employee ID number.

" "
Causes a space to print between the employee ID and name, so the two
fields don't run together.

emp_name_var
Tells SQL*Plus to print the contents of this variable, which contains
the most recently retrieved employee name.

Formatting a character field such as the employee name is unnecessary when it appears in a TTITLE, but you do have that option. Specifying FORMAT A20 TRUNCATED, for example, will cause the employee name to print in a 20-character-wide field with any names longer than 20 characters being truncated.

5.4.3.3 Revisit the report width and the width of the remaining fields

The employee_name and
employee_id columns used a total of 22 characters.
Because each column was followed by one blank space, eliminating the
columns 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 two
project names are 26 characters or less, so let's
increase the width of that field to 26 by changing its COLUMN
command:

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
this:
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. Example 5-8 shows
the final version of the script, incorporating all the changes
described in this section. Example 5-8 incorporates
the use of SET RECSEP OFF to eliminate those pesky, blank lines that
otherwise print following any detail row with a wrapped column value.

Example 5-8. Master/detail report showing a breakdown of time billed to projects

SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equals sign 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 INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
ORDER BY e.employee_id, p.project_id, ph.time_log_date;
EXIT The report output, now in master/detail form, will look like this:

The Fictional Company
I.S. Department Project Hours and Dollars Detail
=====================================================
Employee: 101 Marusia Churai
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Jan-2004 1 $169.00
01-Mar-2004 3 $507.00
01-May-2004 5 $845.00
01-Jul-2004 7 $1,183.00
01-Sep-2004 1 $169.00
01-Nov-2004 3 $507.00
1002 Enterprise Resource 01-Feb-2004 7 $1,183.00
Planning System
01-Apr-2004 1 $169.00

5.4.3.4 Printing data in a page footer

You can print data as part of the page footer using the same method
shown for the page header. The only difference is that you would
normally use the OLD_VALUE clause with the COLUMN
command instead of the NEW_VALUE clause shown in the example.
That's because when SQL*Plus prints the footer, it
has 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 normally
want to happen.