Master/Detail Reports
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 "DollarsCharged" FORMAT 9,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 9.00 Web Site 101 Marusia Churai 1001 Corporate 01-Mar-2004 3 7.00 Web Site 101 Marusia Churai 1001 Corporate 01-May-2004 5 5.00 Web Site ... 102 Mykhailo 1001 Corporate 01-May-2004 5 5.00 Hrushevsky Web Site 102 Mykhailo 1002 Enterprise 01-Feb-2004 7 5.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.
|
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.
|
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 9.00 Web Site 01-Mar-2004 3 7.00 01-May-2004 5 5.00 ... 102 Mykhailo 1001 Corporate 01-Jan-2004 1 5.00 Hrushevsky Web Site 01-Mar-2004 3 5.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
|
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 "DollarsCharged" FORMAT 9,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 9.00 Web Site 01-Mar-2004 3 7.00 01-May-2004 5 5.00 01-Jul-2004 7 ,183.00 01-Sep-2004 1 9.00 01-Nov-2004 3 7.00 1002 Enterprise 01-Feb-2004 7 ,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 5.00 Hrushevsky Web Site 01-Mar-2004 3 5.00 01-May-2004 5 5.00 ...
Each change in employee starts a new page, and two blank lines follow each project.
|
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.
|
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 "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 --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 9.00 01-Mar-2004 3 7.00 01-May-2004 5 5.00 01-Jul-2004 7 ,183.00 01-Sep-2004 1 9.00 01-Nov-2004 3 7.00 1002 Enterprise Resource 01-Feb-2004 7 ,183.00 Planning System 01-Apr-2004 1 9.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.
