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

7.1 Totals and Subtotals

SQL*Plus allows you to print totals and subtotals in a report. To do
this, you use a combination of the BREAK command and one or more
COMPUTE commands. This section continues with the master/detail
report last shown in Chapter 5, in Example 5-8. It will show you how to modify that
master/detail report so it totals the hours and dollars by project
and by employee. You will see how to print grand totals for these
columns at the end of the report.

To refresh your memory, Example 7-1 repeats the
Project Hours and Dollars Detail report from Example 5-8.

Example 7-1. 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 signs 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 output from Example 7-1 looks like this:

7.1.1 Printing Subtotals

The Project Hours and Dollars
Detail report has two numeric columns showing the hours logged to a
project together with the resulting dollar amount that was charged.
To total these for each project and employee would be desirable. To
accomplish that goal, you can use the COMPUTE command, which is a
SQL*Plus command to define summary calculations in a report. The
following four commands generate subtotals for hours and dollars, by
project and employee:

COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id The first two COMPUTE commands summarize the hours and dollars by
project. Those project totals print whenever the
project_id value changes. To help you understand
this, let me break the first COMPUTE command down into more
digestible pieces:

COMPUTE SUM
Generate a running total incremented for each detail row that
SQL*Plus outputs as part of the report. The OF clause specifies the
source of the values to be totaled.

LABEL 'Totals '
Use this text to identify that total when it prints on the report.

OF hours_logged
Take values from this column.

ON project_id
Reset the accumulated total to zero whenever the
project_id changes.

The COMPUTE command controls the accumulation of running totals, but
if you read this detailed breakdown carefully,
you'll see no mention of COMPUTE causing anything to
print. It is the BREAK command that causes the accumulated values to
print. You have to interpret COMPUTE commands in the context of the
BREAK setting for the report in question. That BREAK setting for the
Project Hours and Dollars Detail report is as follows:

BREAK ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES The report breaks once for each employee. For each employee, the
report breaks once per project. The first two COMPUTE commands are ON
project_id, and a change in
project_id corresponds to a new project. At the
end of each project, then, SQL*Plus does the following:

Displays the accumulated totals from hours_logged
and dollars_charged Resets those accumulators to zero in preparation for the next project The last two COMPUTE commands accumulate
hours_logged and
dollars_charged, not for each project but for each
employee. Those COMPUTEs are ON employee_id, which
means the accumulators will be reset to zero each time the employee
changes. The BREAK ON employee_id causes the just
finished employee's totals to print before the
accumulators are reset.

Example 7-2 shows the Project Hours and Dollars
Detail report with the COMPUTE commands added.

Example 7-2. Master/detail report with totals and subtotals

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 signs 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
COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
--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 Here's how the output from Example 7-2 looks:

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
******* ************************** ------ ------------
Totals 20 $3,380.00
...
1005 VPN Implementation 01-Jan-2004 5 $845.00
01-Mar-2004 7 $1,183.00
01-May-2004 1 $169.00
01-Jul-2004 3 $507.00
01-Sep-2004 5 $845.00
01-Nov-2004 7 $1,183.00
******* ************************** ------ ------------
Totals 28 $4,732.00
------ ------------
116 $19,604.00 Notice that the label "Totals"
appears in the project_id column. SQL*Plus places
the label you specify into the ON column of the associated COMPUTE
command. The label is formatted according to the rules specified in
that column's COLUMN command.

Only one label can be printed for project totals though two COMPUTE
commands exist. Had you specified two different labels, the first one
would have taken precedence.

Why no label for the employee totals? Because this is a master/detail
report, and the
NOPRINT option has been used
to suppress printing of the employee_name and
employee_id columns. Normally, SQL*Plus would
print the COMPUTE label in the employee_id column,
but that can't be done if the column is not being
printed. You can do nothing if you aren't happy with
this behavior. You have to live with it or avoid master/detail
reports.

The width of the label identifying the project totals is limited by
the width of the project_id column, which
won't hold a longer, more descriptive label such as
"Project Totals". However, you can
do a couple of things to make room for a longer label. The first and
most obvious thing is to make the project_id
column wider. Change the COLUMN command to widen the field from 7 to
14 digits:

COLUMN project_id HEADING 'Proj ID' FORMAT 99999999999999 Just be sure to bump up the linesize setting by the same amount and
the page headers and footers.

A less obvious approach would be to change the computations so the
project totals are summarized for each project name rather than for
each project ID. This, of course, presumes that no two projects share
the same name. The necessary COLUMN commands would be the following:

COMPUTE SUM LABEL 'Project Totals' OF hours_logged ON project_name
COMPUTE SUM LABEL 'Project Totals' OF dollars_charged ON project_name The output would then look like:

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
******* ************************** ------ ------------
Project Totals 20 $3,380.00 You now have room for a more descriptive label, and the added benefit
is that the report looks better with the label indented closer to the
printed totals.

COMPUTEs Are Cumulative

The COMPUTE and BREAK commands are closely linked because, although
it is COMPUTE that defines summary calculations, it is BREAK that
causes those calculations to display. It may seem a bit odd, then,
that these two related commands work in a dissimilar manner. You are
allowed to have only one BREAK setting in a report. If you want to
break on multiple columns, you must list all those columns in the
same BREAK command. That syntax helps make the break order clear.
COMPUTE is just the opposite. If you wish to compute summary values
for multiple columns, you must issue several COMPUTE commands, one
for each of those columns. Summary calculations are not relative to
each other as breaks are, so there is no need to define them all in a
single command.

7.1.2 Printing Grand Totals

Use the REPORT keyword in the ON clause of a COMPUTE statement to
define report-level computations. For example, you might wish to
generate a grand total for hours and dollars columns in the Project
Hours and Dollars Detail report. For report-level computations to
print, you need to generate a report-level break, which you do using
the REPORT keyword in the BREAK command.

To print grand totals for the project_hours and
dollars_charged columns, add the following two
lines to the script shown in Example 7-2:

COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT Instead of specifying a column in the ON clause,
I've used the keyword REPORT. This tells SQL*Plus to
sum the data over the entire report. Notice the
use of the LABEL clause. Normally, the
label would print in the column specified in the ON clause. In cases
like this where no ON column exists, SQL*Plus will place the labels
in the first column of the report.

The next thing to do is to modify the BREAK command by adding a report
break. Forget to do this and the report totals will not print. The
final version of the BREAK command looks like this:

BREAK ON REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES The REPORT break was added to the beginning of the BREAK command
because it is the outermost break. The position
doesn't really matter because SQL*Plus always makes
the report break outermost, but I like to put it first anyway for the
sake of clarity.

Example 7-3 shows the report as it stands now.

Example 7-3. The report script modified to generate grand totals

SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 1
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 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 REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES
COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT
--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 Run the report in Example 7-3, and the grand totals
will be printed on a page by themselves at the end of the report.
Here's how that output will look:

The Fictional Company
I.S. Department Project Hours and Dollars Detail
===============================================================
Employee: 113 Mykhailo Verbytsky
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
------ ------------
786 $110,779.00 Notice three things about how the report totals are printed. First,
they print on a page by themselves. Next, the page with the grand
totals still shows an employee name in the page header. Finally, the
"Grand Totals" label does not print
as expected in the first column. I'll explain all of
these oddities next.

7.1.2.1 Grand totals and pagination

First, the pagination issue. Before SQL*Plus executes a report break,
it first executes all the other breaks. Execution begins with the
innermost break and proceeds outwards until the report break actions
are executed. In this example, SQL*Plus will skip two lines for the
project break and will skip to a new page for the employee break.
Then SQL*Plus prints the report totals. This is usually the behavior
you want when printing a master/detail report. You may intend to give
each employee his own section of the report so he can check his
hours. Because the grand total doesn't
"belong" with any one employee, you
don't want it on the pages you are giving out.

7.1.2.2 Grand totals and the final detail record

The last employee's name printed on the page header
because it was the last value retrieved from the database. It would
be nice if SQL*Plus were smart enough to make this value null or
blank, but it isn't. The value in the header is
refreshed only when a new value is read from the database, and in the
case of a report break, that doesn't happen. This is
an issue on master/detail reports only when you use variables to
include report data in the header.

One possible solution to this problem of the last set of detail
values being carried forward into the grand total row is to use UNION
ALL to forcibly add a row of nulls to the end of your result set. For
example, you can replace the query in Example 7-3
with the following:

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
UNION ALL
SELECT NULL, 'Grand Totals', NULL, NULL, NULL, NULL, NULL
FROM dual
ORDER BY employee_id NULLS LAST, project_id, time_log_date;

If you have downloaded the example scripts for this book,
you'll find this solution in
ex7-3b.sql. The result of executing that script
is shown in the following two pages at the end of the report:

The Fictional Company
I.S. Department Project Hours and Dollars Detail
===============================================================
Employee: Grand Totals
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
******* ************************** ------ ------------
Totals
------ ------------
...
The Fictional Company
I.S. Department Project Hours and Dollars Detail
===============================================================
Employee: Grand Totals
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
------ ------------
786 $110,779.00 This solution is a bit of a hack. It does work, but it generates one
extra page of output. The use of NULLS LAST in the
query's ORDER BY clause will force the null record
to the end of the result set. The second-to-last page of the report
represents that record as SQL*Plus attempts to report the hours and
dollars for the nonexistent employee named "Grand
Totals". Of course, there is no detail for that
employee, so that page is blank except for the page and column titles
and some horizontal rules related to the COLUMN command.

As before, the final page of the report displays a grand total. The
employee name is still carried forward from the final record in the
result set, but that final record is the one manufactured by the
UNION ALL query. Just to add clarity to the report, I gave that null
record one non-null column; I specified the
employee_name as 'Grand
Totals' because I knew it would print on this
final page where it helps identify the final totals as two grand
totals.

The section "Taking Advantage of
Unions" shows an alternative to NULLS LAST that
forces the order of results from a UNION ALL query.

7.1.2.3 Grand totals and the lack of a label

The final item to notice about the output from Example 7-3 is the lack of a label for the grand total
values. I did say that SQL*Plus puts the label for report-level
calculations in the first column of the report. Contrary to what you
might intuitively expect, SQL*Plus bases the first column on the
SELECT statement, not on what is printed. When this report was
converted to a master/detail report, printing the first two columns
was suppressed using the NOPRINT clause of the COLUMN command. No
employee_id column, no "Grand
Totals" label.

My solution to the problem of the final employee name record carrying
over to the grand total page addresses, to some extent at least, the
lack of a label for the grand totals. The solution described in this
section manages to place the label on the same line as the totals.

Because the employee_id and
employee_name columns are not being printed, their
position in the SELECT statement is irrelevant. You can move them to
the end (making project_id the first column) and
widen the project_id column to hold 12 characters
instead of 7, and the "Grand
Totals" label will now print in the first column of
the report.

Example 7-4 shows the final listing, complete with
the changes that allow the "Grand
Totals" label to print. The listing incorporates the
solution described in the preceding section, so the final
employee's name doesn't carry over
to the grand total page. To widen the project_id
column to accommodate 12 characters, five extra leading spaces were
inserted into the project_id column title. The
linesize was adjusted from 66 to 71, and five equal sign characters
were added to the ruling lines in the header and footer.

Example 7-4. A grand total solution that labels the grand totals

SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 1
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 71
--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 REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES
COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT
--Execute the query to generate the report.
SELECT p.project_id,
p.project_name,
TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date,
ph.hours_logged,
ph.dollars_charged,
e.employee_id,
e.employee_name
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
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, 'Grand Totals'
FROM dual
ORDER BY employee_id NULLS LAST, project_id, time_log_date;
EXIT Following is the final page of output produced by Example 7-4. The "Grand
Totals" label does indeed appear in the
project_id column when the grand totals print. The
employee name is listed as "Grand
Totals", which is the result of the UNION ALL
solution described in the previous section.

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

فارسی

کردی

العربیه

اردو

Türkçe

Русский

English

Français

کانال فیلم من

تبیان من

فایلهای من

کتابخانه من

پنل پیامکی

وبلاگ من

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی