Reports:
Up until now, the output that we have been creating has been useful but not necessarily designed
in a way that would produce professional looking reports. We are no going to look at defining
a group of parameters and designing a report that can either be displayed on the screen or
printed as a report. This will involve more complex code that is run as a unit so we will use
the editor to create the Oracle SQL script and save it as a file. The tables being used to
provide the data to the report are defined using a SELECT. Multiple tables can be used or we
can create a view which can provide the information from multiple tables.
When we want to create a script file we can invoke the editor followed by the name of the script
file that we are creating. For example: EDIT donrept1 will open the editor to create a script
file called donrept1 after first prompting us with a message saying it cannot find the file.
When you are done you can simply save the file. To bring it back for further editing you can
again key EDIT donrept1. Note that when you say EDIT and a file name you will bring up a file
if one exists, otherwise you will start a new file. In doing the report below, I am using the
view called donexpand that I created above. I will show the report in varying stages of
development as I add new elements.
The first step is to call the editor and give the script a name, I called in hdrcol1. The code
starting with CLEAR COLUMNS shows what I keyed in as the script in the edition. CLEAR COLUMNS
clears the previous headings. COLUMN followed by the name of the field followed by the word
HEADING followed by the information for the heading establishes the heading for the particular
field. Notice the | which separates the first and second line of the heading. The / at the
bottom will cause the most recent query to be rerun so it is a good way of testing. When I
save the script for alter use, I included the select which will generate the query. The Input
truncated to 1 characters baffles me - I tried a lot of things to get rid of it but the report
looks fine, so...
I went back in to the editor and added the select. I then ran it with the / to execute the
last query and I ran it with the @hdrcol1. Notice that I do not get the truncated comment
when I ran it with the /.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #'
COLUMN name HEADING 'Donor|Name'
COLUMN contamt HEADING 'Contribution|Amount'
COLUMN driveno HEADING 'Drive|Number'
COLUMN drivename HEADING 'Drive|Name'
/
SQL> @ hdrcol1
Input truncated to 1 characters
Donor Donor Contribution Dri Drive
Id # Name Amount Num Name
----- --------------- ------------ --- ---------------
11111 Stephen Daniels 25 100 Kids Shelter
23456 Susan Ash 20 100 Kids Shelter
22222 Carl Hersey 10 100 Kids Shelter
12121 Jennifer Ames 50 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
33333 Nancy Taylor 10 300 Health Aid
6 rows selected.
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #'
COLUMN name HEADING 'Donor|Name'
COLUMN contamt HEADING 'Contribution|Amount'
COLUMN driveno HEADING 'Drive|Number'
COLUMN drivename HEADING 'Drive|Name'
SELECT * FROM donexpand
/
SQL> /
Donor Donor Contribution Dri Drive
Id # Name Amount Num Name
----- --------------- ------------ --- ---------------
11111 Stephen Daniels 25 100 Kids Shelter
23456 Susan Ash 20 100 Kids Shelter
22222 Carl Hersey 10 100 Kids Shelter
12121 Jennifer Ames 50 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
33333 Nancy Taylor 10 300 Health Aid
6 rows selected.
SQL> @ hdrcol1
Input truncated to 1 characters
Donor Donor Contribution Dri Drive
Id # Name Amount Num Name
----- --------------- ------------ --- ---------------
11111 Stephen Daniels 25 100 Kids Shelter
23456 Susan Ash 20 100 Kids Shelter
22222 Carl Hersey 10 100 Kids Shelter
12121 Jennifer Ames 50 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
33333 Nancy Taylor 10 300 Health Aid
6 rows selected.
In the example below, I am adding a title to the report. To do that, I went back into the
editor (still editing hdrcol1) and added the linesize and the TTITLE. Notice that the output
when I ran hdrcol1 includes the date and the page number as well as the header line. The reason
I added the linesize is to control the length used for the report and therefore the placement of
the title header which is centered and the data and page no. Obviously I could have used the |
if I wanted another line in the header.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #'
COLUMN name HEADING 'Donor|Name'
COLUMN contamt HEADING 'Contribution|Amount'
COLUMN driveno HEADING 'Drive|Number'
COLUMN drivename HEADING 'Drive|Name'
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
SELECT * FROM donexpand
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Donor Donor Contribution Dri Drive
Id # Name Amount Num Name
----- --------------- ------------ --- ---------------
11111 Stephen Daniels 25 100 Kids Shelter
23456 Susan Ash 20 100 Kids Shelter
22222 Carl Hersey 10 100 Kids Shelter
12121 Jennifer Ames 50 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
33333 Nancy Taylor 10 300 Health Aid
6 rows selected.
In the example below the columns are sized and in the case of the contribution amount formatted
to print with $ and decimal places. In doing the formatting, you need to conform to the
linesize or you will get wrap. Notice Oracle leaves a space between columns. In looking at
the code below, remember after edit hdrcol1, I am cutting and pasting what appears in the
editor.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #' FORMAT A8
COLUMN name HEADING 'Donor|Name' FORMAT A16
COLUMN contamt HEADING 'Contribution|Amount' FORMAT $9,999.99
COLUMN driveno HEADING 'Drive|Number' FORMAT A8
COLUMN drivename HEADING 'Drive|Name' FORMAT A16
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
SELECT * FROM donexpand
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Donor Donor Contribution Drive Drive
Id # Name Amount Number Name
-------- ---------------- ------------ -------- ----------------
11111 Stephen Daniels $25.00 100 Kids Shelter
23456 Susan Ash $20.00 100 Kids Shelter
22222 Carl Hersey $10.00 100 Kids Shelter
12121 Jennifer Ames $50.00 100 Kids Shelter
12121 Jennifer Ames $40.00 200 Animal Home
33333 Nancy Taylor $10.00 300 Health Aid
6 rows selected.
Now I am going to include totals and breaks. I want to break on drive number and drive number
is in the middle of the report. The look and feel will be better, if drive number is the first
field. First, I am going to put in the breaks with the order as it currently is. Then I am
going to create a new view with the drive number at the beginning and examine the difference.
Finally I am going to include totals.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #' FORMAT A8
COLUMN name HEADING 'Donor|Name' FORMAT A16
COLUMN contamt HEADING 'Contribution|Amount' FORMAT $9,999.99
COLUMN driveno HEADING 'Drive|Number' FORMAT A8
COLUMN drivename HEADING 'Drive|Name' FORMAT A16
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
BREAK ON REPORT ON driveno SKIP 2
SELECT * FROM donexpand
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Donor Donor Contribution Drive Drive
Id # Name Amount Number Name
-------- ---------------- ------------ -------- ----------------
11111 Stephen Daniels $25.00 100 Kids Shelter
23456 Susan Ash $20.00 Kids Shelter
22222 Carl Hersey $10.00 Kids Shelter
12121 Jennifer Ames $50.00 Kids Shelter
12121 Jennifer Ames $40.00 200 Animal Home
33333 Nancy Taylor $10.00 300 Health Aid
6 rows selected.
SQL> CREATE VIEW drivefst AS
2 SELECT driveno, drivename, idno, name, contamt
3 FROM donexpand;
View created.
SQL>/
Sun Jun 27 page 1
Donations with Donor and Drive
Drive Drive Donor Donor Contribution
Number Name Id # Name Amount
-------- ---------------- -------- ---------------- ------------
100 Kids Shelter 11111 Stephen Daniels $25.00
Kids Shelter 23456 Susan Ash $20.00
Kids Shelter 22222 Carl Hersey $10.00
Kids Shelter 12121 Jennifer Ames $50.00
200 Animal Home 12121 Jennifer Ames $40.00
300 Health Aid 33333 Nancy Taylor $10.00
6 rows selected.
Now I am getting totals. First notice that I have changed the select to use the new view
drivefst. Next I included to COMPUTE SUM commands, the first one acts on breaks and the second
acts on final totals.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #' FORMAT A8
COLUMN name HEADING 'Donor|Name' FORMAT A16
COLUMN contamt HEADING 'Contribution|Amount' FORMAT $9,999.99
COLUMN driveno HEADING 'Drive|Number' FORMAT A8
COLUMN drivename HEADING 'Drive|Name' FORMAT A16
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
BREAK ON REPORT on driveno SKIP 1
COMPUTE SUM OF contamt ON driveno
COMPUTE SUM of contamt ON REPORT
SELECT * FROM drivefst
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Drive Drive Donor Donor Contribution
Number Name Id # Name Amount
-------- ---------------- -------- ---------------- ------------
100 Kids Shelter 11111 Stephen Daniels $25.00
Kids Shelter 23456 Susan Ash $20.00
Kids Shelter 22222 Carl Hersey $10.00
Kids Shelter 12121 Jennifer Ames $50.00
******** ------------
sum $105.00
200 Animal Home 12121 Jennifer Ames $40.00
******** ------------
sum $40.00
300 Health Aid 33333 Nancy Taylor $10.00
******** ------------
sum $10.00
------------
sum $155.00
6 rows selected.
If all you want is a final total, then the break and sum information about driveno can be
eliminated. Note that if you are breaking on REPORT only the BREAK line can be eliminated
completely. If all you want are break totals with no final totals, the break and sum
information about REPORT can be eliminated. Note: REPORT is for final totals.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #' FORMAT A8
COLUMN name HEADING 'Donor|Name' FORMAT A16
COLUMN contamt HEADING 'Contribution|Amount' FORMAT $9,999.99
COLUMN driveno HEADING 'Drive|Number' FORMAT A8
COLUMN drivename HEADING 'Drive|Name' FORMAT A16
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
BREAK ON REPORT
COMPUTE SUM of contamt ON REPORT
SELECT * FROM drivefst
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Drive Drive Donor Donor Contribution
Number Name Id # Name Amount
-------- ---------------- -------- ---------------- ------------
100 Kids Shelter 11111 Stephen Daniels $25.00
100 Kids Shelter 23456 Susan Ash $20.00
100 Kids Shelter 22222 Carl Hersey $10.00
100 Kids Shelter 12121 Jennifer Ames $50.00
200 Animal Home 12121 Jennifer Ames $40.00
300 Health Aid 33333 Nancy Taylor $10.00
------------
sum $155.00
6 rows selected.
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #' FORMAT A8
COLUMN name HEADING 'Donor|Name' FORMAT A16
COLUMN contamt HEADING 'Contribution|Amount' FORMAT $9,999.99
COLUMN driveno HEADING 'Drive|Number' FORMAT A8
COLUMN drivename HEADING 'Drive|Name' FORMAT A16
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
BREAK ON driveno SKIP 1
COMPUTE SUM of contamt ON driveno
SELECT * FROM drivefst
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Drive Drive Donor Donor Contribution
Number Name Id # Name Amount
-------- ---------------- -------- ---------------- ------------
100 Kids Shelter 11111 Stephen Daniels $25.00
Kids Shelter 23456 Susan Ash $20.00
Kids Shelter 22222 Carl Hersey $10.00
Kids Shelter 12121 Jennifer Ames $50.00
******** ------------
sum $105.00
200 Animal Home 12121 Jennifer Ames $40.00
******** ------------
sum $40.00
300 Health Aid 33333 Nancy Taylor $10.00
******** ------------
sum $10.00
6 rows selected.
If you want to do an average instead of a sum, you would substitute AVG. In fact, any of the functions can be used.
SQL CODE:
SQL> edit hdrcol1
CLEAR COLUMNS
COLUMN idno HEADING 'Donor|Id #' FORMAT A8
COLUMN name HEADING 'Donor|Name' FORMAT A16
COLUMN contamt HEADING 'Contribution|Amount' FORMAT $9,999.99
COLUMN driveno HEADING 'Drive|Number' FORMAT A8
COLUMN drivename HEADING 'Drive|Name' FORMAT A16
SET LINESIZE 65
TTITLE 'Donations with Donor and Drive'
BREAK ON driveno SKIP 1
COMPUTE AVG of contamt ON driveno
SELECT * FROM drivefst
/
SQL> @ hdrcol1
Input truncated to 1 characters
Sun Jun 27 page 1
Donations with Donor and Drive
Drive Drive Donor Donor Contribution
Number Name Id # Name Amount
-------- ---------------- -------- ---------------- ------------
100 Kids Shelter 11111 Stephen Daniels $25.00
Kids Shelter 23456 Susan Ash $20.00
Kids Shelter 22222 Carl Hersey $10.00
Kids Shelter 12121 Jennifer Ames $50.00
******** ------------
avg $26.25
200 Animal Home 12121 Jennifer Ames $40.00
******** ------------
avg $40.00
300 Health Aid 33333 Nancy Taylor $10.00
******** ------------
avg $10.00
SET CLAUSES etc.
There are a lot of other set clauses that can be inserted in your report. For example SET
PAGESIZE establishes the number of lines that will appear on a page. For a screen report this
could be set to 24 (screen size) or less and for a report this could be set to less than 66
(page size). The SET PAUSE can be used to pause the screen display after every page rather
than have it go through to the last page. Enter is used to move on to the next screen. If you
are printing the file, you will spool it to an output file from which you can print. Use the
SPOOL command to send the output to a file (note that it will be displayed as it is being
written to the file). The spool command allows you to establish a path to spool to. You can
now print or edit the document as you would other data. The command SPOOL OFF terminates the
processing. There are a variety of other commands that turn off settings used in the script.
They should be applied to return settings to normal for the next operation.
| COMMAND | PROCESSING |
| BREAK ON | Handles break processing on the column(s) specified |
| CLEAR BREAK | Turns off break processing |
| TTITLE | Provides titles at the top of the report |
| TTITLE OFF | Turns off titles at the top of the report |
| BTITLE | Provides titles at the bottom of the report |
| BTITLE OFF | Turns off titles at bottom of report |
| CLEAR COLUMNS | Clears previous titles |
| CLEAR COMPUTE | Clears previous calculations |
| COLUMN | Used to change the name of a column |
| COMPUTE | Used with a function to act on column values |
| HEADING | Used to set up a column heading |
| ON REPORT | Used for final totals |
| ON column name | Used to define field for breaks |
| RTRIM | Removes extra space after column value |
| SET FEEDBACK OFF | Turns off number of rows selected feedback |
| SET FEEDBACK ON | Turns on number of rows selected feedback |
| SET LINESIZE | Sets characters per line maximum |
| SET PAGESIZE | Sets maximum number of lines per page |
| SET PAUSE ON | Causes screen display to pause after each page |
| SET PAUSE OFF | Screen will not pause between pages of report |
| SKIP 1 | Inserts blank line between groups |
| SPOOL | Sends output to file for printing |
| WRAP | Causes wrapping within a column |
| FORMAT | Allows for formatting of data |
Another Example Using More Commands:
SQL CODE:
Please note: I am having a problem with case in Word so the last two examples are entirely in
upper case.
SQL>EDIT COLHDR1
CLEAR COLUMNS
SPOOL A:\RPT1.SQL
COLUMN IDNO HEADING 'DONOR|ID #' FORMAT A8
COLUMN NAME HEADING 'DONOR|NAME' FORMAT A16
COLUMN CONTAMT HEADING 'CONTRIBUTION|AMOUNT' FORMAT $9,999.99
COLUMN DRIVENO HEADING 'DRIVE|NUMBER' FORMAT A8
COLUMN DRIVENAME HEADING 'DRIVE|NAME' FORMAT A16
SET LINESIZE 70
SET PAGESIZE 22
SET PAUSE ON
SET FEEDBACK OFF
TTITLE 'DONATIONS WITH DONOR AND DRIVE'
BREAK ON REPORT ON DRIVENO SKIP 1
COMPUTE SUM OF CONTAMT ON DRIVENO
COMPUTE SUM OF CONTAMT ON REPORT
SELECT * FROM DRIVEFST
/
SPOOL OFF
SET FEEDBACK OFF
SQL> /
WED JUN 30 PAGE 1
DONATIONS WITH DONOR AND DRIVE
DRIVE DRIVE DONOR DONOR CONTRIBUTION
NUMBER NAME ID # NAME AMOUNT
-------- ---------------- -------- ---------------- ------------
100 KIDS SHELTER 11111 STEPHEN DANIELS $25.00
KIDS SHELTER 23456 SUSAN ASH $20.00
KIDS SHELTER 22222 CARL HERSEY $10.00
KIDS SHELTER 12121 JENNIFER AMES $50.00
******** ------------
SUM $105.00
200 ANIMAL HOME 12121 JENNIFER AMES $40.00
******** ------------
SUM $40.00
300 HEALTH AID 33333 NANCY TAYLOR $10.00
******** ------------
SUM $10.00
WED JUN 30 PAGE 2
DONATIONS WITH DONOR AND DRIVE
DRIVE DRIVE DONOR DONOR CONTRIBUTION
NUMBER NAME ID # NAME AMOUNT
-------- ---------------- -------- ---------------- ------------
------------
SUM $155.00
6 ROWS SELECTED.
Example Using Multiple Files Instead Of View:
SQL CODE:
SQL> EDIT HDRCOL1
CLEAR COLUMNS
SPOOL A:\RPT1.SQL
COLUMN DRIVENO HEADING 'DRIVE|NUMBER' FORMAT A8
COLUMN DRIVENAME HEADING 'DRIVE|NAME' FORMAT A16
COLUMN IDNO HEADING 'DONOR|ID #' FORMAT A8
COLUMN NAME HEADING 'DONOR|NAME' FORMAT A16
COLUMN CONTAMT HEADING 'CONTRIBUTION|AMOUNT' FORMAT $9,999.99
SET LINESIZE 70
SET PAGESIZE 22
SET PAUSE ON
SET FEEDBACK OFF
TTITLE 'DONATIONS WITH DRIVE AND DONOR'
BREAK ON REPORT ON DRIVENO SKIP 1
COMPUTE SUM OF CONTAMT ON DRIVENO
COMPUTE SUM OF CONTAMT ON REPORT
SELECT DR.DRIVENO, DRIVENAME, DO.IDNO, NAME, CONTAMT
FROM DONOR DO, DONATION DN, DRIVE DR
WHERE DO.IDNO = DN.IDNO AND DN.DRIVENO = DR.DRIVENO
/
SPOOL OFF
SET FEEDBACK OFF
WED JUN 30 PAGE 1
DONATIONS WITH DRIVE AND DONOR
DRIVE DRIVE DONOR DONOR CONTRIBUTION
NUMBER NAME ID # NAME AMOUNT
-------- ---------------- -------- ---------------- ------------
100 KIDS SHELTER 11111 STEPHEN DANIELS $25.00
KIDS SHELTER 23456 SUSAN ASH $20.00
KIDS SHELTER 22222 CARL HERSEY $10.00
KIDS SHELTER 12121 JENNIFER AMES $50.00
******** ------------
SUM $105.00
200 ANIMAL HOME 12121 JENNIFER AMES $40.00
******** ------------
SUM $40.00
300 HEALTH AID 33333 NANCY TAYLOR $10.00
******** ------------
SUM $10.00
WED JUN 30 PAGE 2
DONATIONS WITH DRIVE AND DONOR
DRIVE DRIVE DONOR DONOR CONTRIBUTION
NUMBER NAME ID # NAME AMOUNT
-------- ---------------- -------- ---------------- ------------
------------
SUM $155.00
WED JUN 30 PAGE 1
DONATIONS WITH DRIVE AND DONOR
DRIVE DRIVE DONOR DONOR CONTRIBUTION
NUMBER NAME ID # NAME AMOUNT
-------- ---------------- -------- ---------------- ------------
100 KIDS SHELTER 11111 STEPHEN DANIELS $25.00
KIDS SHELTER 23456 SUSAN ASH $20.00
KIDS SHELTER 22222 CARL HERSEY $10.00
KIDS SHELTER 12121 JENNIFER AMES $50.00
******** ------------
SUM $105.00
200 ANIMAL HOME 12121 JENNIFER AMES $40.00
******** ------------
SUM $40.00
300 HEALTH AID 33333 NANCY TAYLOR $10.00
******** ------------
SUM $10.00
WED JUN 30 PAGE 2
DONATIONS WITH DRIVE AND DONOR
DRIVE DRIVE DONOR DONOR CONTRIBUTION
NUMBER NAME ID # NAME AMOUNT
-------- ---------------- -------- ---------------- ------------
------------
SUM $155.00