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

6.4 Master/Detail Reports in HTML

The last HTML reporting challenge that I want to talk about is that
of generating master/detail reports such as the Project Hours and
Dollars Detail report generated by Example 5-8.
For these reports, you cannot avoid the use of


TTITLE.
You do want to define a header that repeats throughout the report
because you need a way to indicate when the master record changes.

Example 6-8 recreates the Project Hours and Dollars
Detail report in HTML form. Figure 6-5 shows the
result, and as you probably expect by now, Example 6-9 shows the CSS stylesheet used to produce the
layout that you see in the figure. Pay particular attention to the
following aspects of this report example:

The page heading, the one that occurs once at the top of the HTML
page, is written to the HTML stream using PROMPT commands, just as in
Example 6-6. PROMPT commands also write the
<div> tags.

TTITLE is used to specify the line that prints for each new master
record. This line is written out as a level-3 heading using the
<h3> tag. (Unfortunately,
it's written into the leftmost cell of a
three-column table.) Similar to what was done in the previous section, the
<div> containing the page title is formatted
as white text on black, while the <div>
containing the report detail is formatted as black text on light
gray.

To distinguish the detail lines from the master lines, the detail
table has been indented a bit and it has been given a white
background. See the table.detail style in Example 6-9.

The detail table has been given a slight, negative top margin to
position each set of detail rows close to their respective master
heading. This is optional, but the results are pleasing when rendered
in Microsoft Internet Explorer.

This report certainly isn't the last word in the
HTML formatting of master/detail reports. It's a
good example, though, along with the other reports in this chapter,
of how creative you can get using the combination of SQL*Plus, HTML,
and CSS. (See Figure 6-5.)

Example 6-8. SQL*Plus script to write a master/detail report in HTML

SET ECHO OFF
SET PAGESIZE 50000
SET NEWPAGE 1
SET MARKUP -
HTML ON -
HEAD '<title>Project Hours and Dollars Detail</title> -
<link href="/image/library/english/10067_ex6-9.css" rel="stylesheet" type="text/css"/>' -
BODY " -
TABLE 'class="detail"' -
ENTMAP OFF -
SPOOL ON
--Set up the heading to use for each new employee
TTITLE LEFT "<h3>Employee: " FORMAT 9999 emp_id_var " " emp_name_var</h3>
--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 "<p class=right>Proj ID</p>" FORMAT 9999
COLUMN project_name HEADING "<p class=left>Project Name</p>" -
FORMAT A26
COLUMN time_log_date HEADING "<p class=left>Date</p>" FORMAT A30
COLUMN hours_logged HEADING "<p class=right>Hours</p>" FORMAT 9,999
COLUMN dollars_charged HEADING "<p class=right>Dollars|Charged</p>" -
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
--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.
SPOOL hours_detaill
PROMPT <div class="top"> -
<h1>The Fictional Company</h2> -
<h2>Project Hours and Dollars Detail</h2> -
</div><div class="bottom">
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;
SPOOL OFF
EXIT

Example 6-9. CSS styles to format the output from Example 6-8

body {margin: 0; font-family: comic sans ms; background: white;}
div {margin: 10px;}
div.top {background: black; color: white;
padding-bottom: 5px; text-align: center;}
div.bottom {background: silver; color: black;}
table.detail {position: relative; top: -1em;
margin-left: 2em; background-color: white;}
p.left {text-align: left;}
p.right {text-align: right;}
th {padding-left: 5px; padding-right: 5px;
text-decoration: underline;}
td {padding-left: 5px; padding-right: 5px;
padding-top: 0; padding-bottom: 0;}
h1 {margin-bottom: 0;}
h2 {margin-top: 0; margin-bottom: 0;}
h3 {margin-top: 0; margin-bottom: 1.25em;}