Master/Detail Reports

2017-11-03 09:05:07

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

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

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

Settings That Hang Around

Have you been working through the examples in this chapter? If you ignored my advice (see the earlier sidebar "Executing the Example Reports") by executing reports interactively from the SQL*Plus prompt, and if you happened to go back to re-execute a previously run report, you may have generated results that don't match those shown in this book. Formatting commands such as TTITLE and BREAK and COLUMN "stick" for the duration of your SQL*Plus session. For example, if you run ex5-1.sql after executing ex5-2.sql , while working in the same SQL*Plus session, you'll see that all the COLUMN headings defined in Example 5-2 show up when you execute Example 5-1 even though ex5-1.sql contains no COLUMN commands whatsoever.

If you feel that you must run a series of SQL*Plus scripts one after another in a single SQL*Plus session, begin each script with a set of commands to clear existing settings or create a separate, reset_state.sql script to run between reports. You can use the STORE command, described in Appendix A, to generate a file of SET commands for such a script, which might look like this:

CLEAR COLUMNS CLEAR BREAKS TTITLE OFF BTITLE OFF SET PAGESIZE 14 SET NEWPAGE 1 SET LINESIZE 80 . . .

Substitution variables, such as those set using NEW_VALUE, must be individually undefined. You can perhaps begin to see why it's safest to run each of your scripts under separate invocations of SQL*Plus. Too much can go wrong. Perhaps someday Oracle will introduce a RESET command to allow you to switch SQL*Plus back to its initial state.

Master Detail Reports

Settings That Hang Around

Категории