Simple Report Writing


Hopefully you've learned by now that I tend to lure you into things by telling you not to worry and just follow the instructions. Well, using the built-in SQL tools will let you build simple reports, but with a bit of practice you can actually create pretty sophisticated reports .

Note

Most shops usually use a third-party tool such as Brio, SQR, or Crystal Reports for the bulk of their report generation. Users can easily learn some of these, IT staff can use others for intricate reports, and all of them fit nicely in the Oracle environment.

However, be aware that Oracle has released a significantly improved Oracle Discoverer. Discoverer presents a different model for users to access the database data, along the idea of having "workbooks" that contain the data for individual business areas. I encourage you to investigate this tool further.


We are going to jump right into using the power of SQL*Plus. Take a look at the following report, which shows the very first customer for the "Guerrilla Oracle Waiver Exam Testing Institute":

Sat Jan 19

 

page 1

Test Identification

__________________

All Tests Taken Student Identification

__________________

Location

__________________

1

222222222

Main Lab

 

Guerrilla Exams

 

And here's the SQL*Plus code:

 rem rem            Tests Taken report rem   written by Jerome Engles rem rem  purpose: this is an ad hoc report to list all tests taken, rem  by type of test rem set headsep graphics/1_icon.gif ttitle 'All Tests Taken' graphics/2_icon.gif btitle 'Guerrilla Exams' Column TEST_ID         heading 'Test Identification ' graphics/3_icon.gif Column TEST_ID         format 999999999 Column STUDENT_ID      heading 'Student Identification ' Column STUDENT_ID      format 999999999 Column LOCATION        heading 'Location' format a30 Break on TEST_ID graphics/4_icon.gif Set LINESIZE   80 graphics/5_icon.gif Set PAGESIZE   55 Set NEWPAGE    0 SPOOL C:\TEST.LST graphics/6_icon.gif SELECT  TEST_ID, STUDENT_ID, LOCATION graphics/7_icon.gif FROM          TEST_HISTORY        ORDER BY TEST_ID; End of spooling graphics/8_icon.gif 

(1) Use the broken vertical bar for a separator.

(2) ttitle = top title; btitle = bottom title

(3) Three columns for the report, using the table column names

(4) Break when Test_ID changes

(5) Page formatting

(6) Start of spooling

(7) Notice that each column has a SELECT statement.

(8) End of spooling

Here's how things match up:

Sat Jan 19 [1]

 

page 1

Test Identification

__________________

All Tests Taken [2] Student Identification

_____________________

Location [3]

____________

1

222222222

Main Lab [4]

 
 

Guerrilla Exams [5]

 

[1] System-generated date.

[2] ttitle

[3] Columns

[4] Data from table

[5] btitle

From this simple example you should have the general idea, so let's look at some rules:

By convention, the headsep (head separator) character is the broken vertical bar, but if your keyboard doesn't support it, you can use another key. Just be careful not to use something that may appear in one of your headings. The head separator tells the system to put the heading on more than one line. You'll see this in the next example, where we expand our simple report.

You can add comments either with the rem command at the beginning of the line, or by enclosing them within these keystrokes:

 /* ... */ 

You can use this notation anywhere !



Guerrilla Oracle
Guerrilla Oracle: The Succinct Windows Perspective
ISBN: 0201750775
EAN: 2147483647
Year: 2003
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net