Table of contents:

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

tag. (Unfortunately, it's written into the leftmost cell of a three-column table.)
Similar to what was done in the previous section, the
containing the page title is formatted as white text on black, while the 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 '

Project Hours and Dollars Detail

' - BODY "" - TABLE 'class="detail"' - ENTMAP OFF - SPOOL ON --Set up the heading to use for each new employee TTITLE LEFT "

Employee: " FORMAT 9999 emp_id_var " " emp_name_var

--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 COLUMN time_log_date HEADING "

Date

" FORMAT A30 COLUMN hours_logged HEADING "

Hours

" FORMAT 9,999 COLUMN dollars_charged HEADING "

DollarsCharged

" - FORMAT 9,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_detail.html PROMPT

The Fictional Company

Project Hours and Dollars Detail

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

Figure 6-5. Example 6-8s master/detail report rendered using styles from Example 6-9