Chapter II: Tutorial

CHAPTER 2: Tutorial
Lesson I: Create a Tabular Report
At the end of the lesson, your report will produce output like that shown below. This style of
report is called a tabular report.

While you build the report, you will learn how to:

a. invoke the Oracle Reports designer and create

a new report definition
b. connect to the database and define your data
c. specify a default report layout
d. save and run your report
e. view your report output

Invoke the Oracle Reports Designer

1. Invoke Oracle Reports. The method you use will depend on your system. If you invoke
products via icons on your desktop, double-click on the Oracle Reports Designer icon.

About the Object Navigator

Oracle Reports II-1

PT. Mitra Integrasi Informatika
Chapter II: Tutorial
The Object Navigator is a road map to your report. It not only shows the objects currently in use,
it shows all types of objects that you can create or reference.

In addition, you can use the Object Navigator to access the properties of objects, create certain
objects, rename objects, and examine the structure of your report from different perspectives, or
views.

Connect to the Database

Although connecting to the database is not necessary until you actually want to access it, now is
a convenient time.

1. Select File-->Connect.... (From the File menu, choose Connect....) The Connect dialog box
appears, prompting you for your username and password.

2. Click in the User Name field and type your username (e.g., scott).

3. Click in the Password field and type your password (e.g., tiger). Your password will not
appear when you type.

4. If you are connecting to a local database, skip to Step 5. If you are connecting to a remote
database (one located on another computer), type your connect string in the database field
(e.g., t:boston:payroll, where t is the SQL*Net communications protocol, boston is the
database node, and payroll is the database name).

5. Select the Connect button.

Specify Your Data

After you invoke the designer and connect to the database, your next step is to specify the data
for the report. This is done in the Data Model editor, which is accessed via the Object Navigator
or the Tools menu. In the Data Model editor, you'll create one or more queries, which are data
model objects that fetch data for your report.

Data Model Icon

1. Double-click on the icon in the Data Model node (the entry in the Object Navigator directly
below "Untitled"). This displays the Data Model editor.

Query Tool

2. Select the Query tool by clicking on it once in the Tool palette.

Oracle Reports II-2

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

3. Move the mouse pointer into the Data Model editor. Click once.

A query object, represented by a rounded rectangle, appears.

4. Double-click on the query object to display its property sheet. (Recall that a property sheet is
a window displaying all properties of a single Oracle Reports object.)

5. Right Click and click property palette in General Information :Name, delete Q_1, and replace
it with Q_Ordpict.

Hint: You are not required to name your query, but we highly recommend that you choose
unique, mnemonic names for all the objects in your report

6. Notice the SELECT Statement field.

In this field you can type a Select statement, or you can build one using the Table and
Column Names dialog box, as follows using ‘QUERY BUILDER’

7. Select Tables/Columns. The Table and Column Names dialog box appears, listing all of the
database tables to which you have access.

8. Scroll down the Database Objects list until you see ORDPICT.

9. Select ORDPICT. If you accidentally select the wrong table, simply click on ORDPICT to
change your selection.

The ORDPICT columns appear in the Columns list of values.

Oracle Reports II-3

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

10. Select CUSTID, ORDERDATE, ORDID and TOTAL by clicking on them. Note: Use the
method appropriate to your platform to select multiple, non-consecutive items from a list.

11. click ok

12. Move your text cursor to the line below "from ORDPICT" in the SELECT Statement field and
enter the following text, as shown:

order by CUSTID, ORDID

13. Select OK to close the Query property sheet. A default group, G_Ordpict, is created,
containing a list of the selected columns.

Specify a Default Layout

After specifying the data for the report, create its layout.

1. Select Tools-->Report Wizard. The Report wizard Layout dialog box appears.

Using this dialog box, you can specify several layout settings. Oracle Reports uses these
specifications to create a complete, executable report.

You can modify the following settings on the Style tab (refer to the next figure):

 Style (e.g., Tabular, Master/Detail, and so on) which determines the initial layout style of
your report.
 Use Current Layout Settings, which determines if Oracle Reports should use its own
defaults when creating boilerplate, or if it should use any changes you've made to the
default attributes (e.g., new font, font size, type style, and so on).

Oracle Reports II-4

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

You can modify the following settings on the Data/Selection tab (refer to the next figure):

2. Click on Field Tab .

Select the field you want to display in your report (all except ORDID)

3. Click on Template TAB and select no template  finish,

Report will be running automatically.

4. Perhaps if you would like to edit the layout, Click on menu view  layout model. The
Layout editor is displayed in front of the windows.

Examine the objects in the Layout editor. Oracle Reports used the settings in the Default
Layout dialog box to generate these objects, which, in turn, will determine how their
associated data objects appear when you run the report.

The layout objects are (refer to the figure below):

 Boilerplate (e.g., Custid) for labelling the data.

 Fields (e.g., F_CUSTID) for displaying the data.

 Repeating frames (e.g., the rectangle enclosing the fields), for controlling each record.
 Frames (e.g., the rectangle enclosing the boilerplate labels) for grouping other layout
objects.

Oracle Reports II-5

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Each type of layout object governs a different aspect of the report's format and final
appearance. For example, notice the small arrow in the upper-left corner of the repeating
frame. This arrow points down, indicating that the rows will display sequentially down the
page.

Summary of Lesson I

While familiarizing yourself with the basic functions of Oracle Reports, you created and ran a
default tabular report. More specifically, you:

 invoked the Oracle Reports designer

 connected to ORACLE (File-->Connect...)
 created a query using the Table and Column Names dialog box
 selected a default layout (Tools-->Default Layout...)
 saved the report (File-->Save)
 ran the report
 accepted the Runtime Parameter Form defaults
 viewed the report output in the Pre-viewer

Oracle Reports II-6

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Lesson II: Create a Break Report

You probably see ways to improve your report. For example, you can suppress duplicate values
of CUSTID where they appear. Also, you can make the data in the TOTAL column more
meaningful by providing summaries (e.g., a total per customer and a grand total for the entire
report).

To remove the duplicate values, create a break report based on the customer identification
number. This will group all information related to a particular customer under the same CUSTID
in the report output. To add the summaries, create your own computational columns.

At the end of this lesson, the output will resemble that shown below.

As you add these improvements, you will:

 change the data model

 update the layout to reflect data model changes
 create a formula column
 create three summary columns
 change the default settings in the Layout editor, then update the layout again to reflect
further data model changes

Invoke Oracle Reports and Open a Report

If you did not exit the designer after Lesson I, skip to "Change the Data Model". If you did exit the
designer, you need to invoke Oracle Reports and open the report customer.rdf. To do this:

1. Invoke the Oracle Reports designer and connect to ORACLE as you did in Lesson I.

2. Select File-->Open... (or the Open tool). The Display dialog box appears.

3. Select the File System radio button, then select OK. The Open dialog box appears.

4. Open customer.rdf using the Open dialog box as you would normally open a file. (Notice that
the new report definition, "Untitled," is automatically closed if you open a report.)

Oracle Reports II-7

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Change the Data Model

To suppress the duplicate values for CUSTID, create a new group containing the CUSTID
column. The new group is a break group; i.e., a group that owns a column with at least one value
that repeats over multiple records.

Create a Break Group

1. Ensure that the Data Model editor is visible, either by double-clicking on the icon in the Object
Navigator's Data Model node or by selecting Windows-->customer: Data Model.

2. Grab the G_Ordpict group by clicking in the G_Ordpict title bar. Drag the group down about 1
inch. Release the mouse button.

Moving G_Ordpict down provides room in the Data Model editor to create a new group.

3. Select the CUSTID column in the G_Ordpict group by clicking on the word CUSTID. Notice
that it is now highlighted.

4. Click-and-drag the CUSTID column out of the G_Ordpict group so that it is above G_Ordpict.
Release the mouse button. A new group is created around CUSTID and above G_Ordpict,
as shown in the next figure.

By creating a break group above G_Ordpict, you are specifying that the break group ranks
above G_Ordpict in the data hierarchy of the report. Thus, for each value of CUSTID fetched
for the break group, all columns in the default group, G_Ordpict, will be displayed.

5. Double-click on the new group to display its property sheet.

6. RightClick and click in property palette in General:Name , replace G_1 with G_Break.

Oracle Reports II-8

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Update the Report Layout

You've changed the hierarchy of the data for your report, so the next step is to update the layout
to incorporate your changes.

1. Select Tools-->Report Wizard... (or the Default Layout tool). The Report Wizard dialog box
appears.

2. Ensure that Style:Group Left is selected, then choose GROUP TAB

3. Make 1 Group level Field, level 1 contains CUSTID and choose Field TAB for select
ORDERDATE and TOTAL field

4. Select Finish to replace the existing layout. The Layout editor displays the updated layout.

The new layout, shown in the next figure, resembles the default tabular layout from Lesson I.
It contains the same fields and the same boilerplate labels. The major difference between
the layouts is in the number of repeating frames generated for each.

About Repeating Frames... Repeating frames contain the data owned by their
corresponding groups. They're called repeating frames because they repeat as many times
as necessary to display all values of the data they contain.

For example, if a report consists of a single column with ten values, the repeating frame
displaying those values repeats ten times. Each instance of the repeating frame contains a
single value. This record-level display method enables the use of record-level printing
criteria.

The first layout contained only one repeating frame, called R_Ordpict, which contained the
columns in the G_Ordpict group. The second layout contains two repeating frames:
R_Ordpict, which still surrounds the columns of the G_Ordpict group, and R_Break, which
surrounds G_Break's column CUSTID and the R_Ordpict repeating frame and its columns.

Oracle Reports II-9

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Placing the G_Break group above the G_Ordpict group in the Data Model ranks it higher in
the data hierarchy and causes the R_Break repeating frame to enclose the R_Ordpict
repeating frame in the report's layout. Within one instance of R_Break, R_Ordpict can repeat
as many times as necessary to display all records related to R_Break's current record. This
is similar in action to a DO-loop in structured programming.

In the Layout editor, a repeating frame appears as a box surrounding one or more fields. The
arrow on its border indicates the direction in which the repeating frame repeats. Both
repeating frames in your layout show downward-pointing arrows on their borders, signifying
that both will print down the page.

Create Computations

Not all columns must be selected from the database. You can create your own columns to
perform computations. Such columns calculate their own values based either on PL/SQL
expressions or on data provided by database columns. There are two types of computations you
can add to a report:

Formulas

Compute their own values using PL/SQL expressions. Formulas can operate on multiple values
per record (e.g., SAL + COMM).

Summaries

Compute their own values using functions shipped with Oracle Reports. Summaries operate on
one value over multiple records (e.g., sum of SAL).

Before you add the new computations to your data model, you may need a little more room to
work.

1. Find the Data Model editor (select Window-->customer: Data Model, or double-click on the
Data Model node icon in the Object Navigator.). Click-and-drag G_Ordpict down another
inch, giving you enough room to enlarge G_Break in the next step.

2. Select G_Break (click in the group title bar), then click-and-drag one of the group's lower
handles (the small boxes at the corners) down approximately one inch. Release the mouse
button. Use the same method to enlarge G_Ordpict.

Create a Formula Column

Formula Column Tool

1. Click once on the Formula Column tool in the Tool palette, then click below TOTAL within the
G_Ordpict group. A new column, initially named CF_1, is created.

Since you created the new column within G_Ordpict, it will display as often as the other
G_Ordpict columns.

Oracle Reports II-10

PT. Mitra Integrasi Informatika
Chapter II: Tutorial
2. Display the new column's property Palette. Delete CF_1 from General:Name and rename the
column TAX_TOTAL.

3. Ensure that the datatype is Number.

Enter the Formula

Use the Program Unit editor, an integrated PL/SQL editor, to create your formula.

1. Select Formula:Edit.... The Program Unit editor appears, as shown in the next figure.

Notice that it already contains a template for your formula.

2. Rename the function if you wish, then enter the remainder of the PL/SQL in the Source Text
field.

Here's the complete formula (assuming a sales tax of 7.5%):

function TAX_TOTAL return NUMBER is

TAX_TOTAL NUMBER;

begin

TAX_TOTAL := :TOTAL + (:TOTAL * .075);

return TAX_TOTAL;

end;

The colons appear before TOTAL because it functions as a bind variable reference; i.e., the
values of TOTAL are substituted into the formula at runtime. If this is unfamiliar, see the
PL/SQL User's Guide and Reference.

3. Select Compile. If you typed the function correctly, the status line reports, "Successfully
Compiled." Otherwise, the status line reports "Compiled with Errors," and the Program Unit

Oracle Reports II-11

PT. Mitra Integrasi Informatika
Chapter II: Tutorial
editor points out your error in the Compilation Messages field. (If this occurs, correct the
mistake in the Source Text field and select Compile again.)

4. Select Close to close the Program Unit editor, then accept the property sheet. TAX_TOTAL
is now listed as a column belonging to G_Ordpict. The column name appears in italic,
indicating that it is a user-created column.

Create a Group Summary

To create a summary that computes the total per customer:

Summary Column Tool

1. Click once on the Summary Column tool in the Data Model editor's Tool palette, then click in
the G_Break group below CUSTID. A new column, initially named CS_1, is created.

Since you created the new column within G_Break, it will display as often as the other
G_Break column, CUSTID. The new summary will appear once for each customer, thus
functioning as a group-level summary

2. Display the property palette for the new column. Delete CS_1 from General:Name and enter
CUSTOMER_TOTAL.

Notice the Function field. The value in the Function field tells Oracle Reports what type of
computation the summary will perform. Sum, which already appears, is the default function
assigned to user-created summary columns.

Take a moment to click on the Function field and examine the list of functions available with
Oracle Reports. Release the mouse button without changing the default value. For details
on each of these summary functions, see "Column Properties" in the Reports Reference
Manual.

3. Select TAX_TOTAL from the Source list of values.

This tells Oracle Reports that you want the summary to add data from the TAX_TOTAL
column.

4. Select G_Break from the Reset At list of values.

This tells Oracle Reports to reset the value of your summary to zero for each new value of
G_Break.

5. Accept the property sheet for CUSTOMER_TOTAL.

Create a Running Summary

To create a summary that keeps track of the total per customer as it accumulates:

Oracle Reports II-12

PT. Mitra Integrasi Informatika
Chapter II: Tutorial
Summary Column Tool

1. Select the Summary Column tool, then click in the G_Ordpict group below TAX_TOTAL. A
new column, again named CS_1, is created in G_Ordpict.

Since you created the new column within G_Ordpict, it will display as often as the other
G_Ordpict columns.

2. Display the column's property sheet. Name it RUN_TOTAL.

3. Select TAX_TOTAL from the Source list of values.

4. Select G_Break from the Reset At list of values.

In this case the accumulating total prints once for each new value of TAX_TOTAL added.
Since both CUSTOMER_TOTAL and RUN_TOTAL reset to zero at G_BREAK, RUN_TOTAL
shows how, record by record, the value for CUSTOMER_TOTAL accumulates.

5. Accept the property sheet for RUN_TOTAL.

Create a Report Summary

To create a summary that computes a grand total:

Summary Column Tool

1. Select the Summary Column tool and click in an open space in the editor. A new column is
created. As it belongs to the report as a whole, it will display only once--by default, at the end
of the report.

2. Display the column's property sheet and name it GRAND_TOTAL.

3. Select TAX_TOTAL from the Source list of values.

4. Ensure that the reset level is set to Report.

A reset level of Report means that GRAND_TOTAL never resets to zero. Oracle Reports will
continue to accumulate the values of TAX_TOTAL until the end of the report.

The final data model looks like the next figure.

Oracle Reports II-13

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Change Default Layout Settings and Redefault

You've added columns that you want to show in the output, so you need to update your layout.
This time you can change the defaulting to improve the overall appearance and readability of your
report.

Change Layout Settings

Oracle Reports offers a variety of options for displaying text and graphics. You can use any font
(e.g.,Courier or Helvetica), weight (e.g., bold), and style (e.g., italic) available on your system to
create boilerplate text.

1. Go to the Layout editor (double-click on the Layout node in the Object Navigator or select
Window-->customer: Layout). Note: Do not select a default layout first.

2. Using the choices in the Format menu, specify a font of Arial, 8 pt., bold, as you would
normally specify a font in an application.

Note: Remember that if you don't have the choices specified here, feel free to choose a
comparable type face and style.

3. Notice the Fill Color, Line Color, and Text Color tools located near the bottom of the Tool
palette. The middle tool, Line Color, is used to customize borders around layout objects.
The top tool, Fill Color, is used to fill layout objects with colors and patterns. The last tool,
Text Color, enables you to change the default text color.

The Fill/Line/Text Display, the box directly above the three tools, shows the currently-selected
fill, border, and text. The default fill and border for objects created by Oracle Reports are
transparent, while the default for objects you create is a black, one-point line around a white
fill.

Since you are changing the text defaults, when you redefault the layout, all objects with be
created with white backgrounds and black borders. To prevent this, change the fill and
border to transparent.

Oracle Reports II-14

PT. Mitra Integrasi Informatika
Chapter II: Tutorial
Fill Color Tool

4. Select the Fill Color tool. When the color palette appears, select No Fill.

When you redefault your layout, none of the layout objects will have fills.

Line Color Tool

5. Select the Line Color tool. When the color palette appears, select No Line.

When you redefault your layout, none of the layout objects will have borders.

6. Select Tools-->Default Layout... (or the Default Layout tool) to display the Default Layout
dialog box.

7. Select General:Use Current Layout Settings to ensure that the new text settings are used as
defaults.

8. Accept the Default Layout dialog box and the alert that appears. The layout appears in the
Layout editor.

Notice that the boilerplates and fields use the font, the font size, and the weight you specified.

9. Save and run your report.

Oracle Reports II-15

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Summary of Lesson II

While modifying your tabular report into a break report, you:

 changed the data model for your report by creating a new group and assigning a column
to it
 created computations
 changed the settings used by Oracle Reports to create default objects
 redefaulted the report layout to reflect changes to the data model

Oracle Reports II-16

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

Lesson III: Create Report with Parameter

We will create report with parameter base on Report in Lesson II.

In Report Lesson II (Break Report) there is an information of Customer ID (CUSTID),
We would like to add two parameter to limit between two number of Customer ID

Eg : Customer ID From ____ To _____

Create User Parameter

1. Create 3 User Parameter :

a. P_CUSTID_LOW
b. P_CUSTID_HI
c. P_WHERECLAUSE

2. Change Property for P_WHERECLAUSE :

a. Datatype : Character
b. Width : 100

Create Trigger

1. Use the Program Unit editor, an integrated PL/SQL editor, to create your trigger in After
Parameter Form

Oracle Reports II-17

PT. Mitra Integrasi Informatika
Chapter II: Tutorial

2. Type the following program trigger

function AfterPForm return boolean is

begin
:P_WHERECLAUSE := ' WHERE CUSTID >= '||P_CUSTID_LOW
|| ' AND CUSTID <= '||P_CUSTID_HI
return (TRUE);
end;

Edit Data Model

1. Add additional whereclause statement in data model using user parameter by type :
&P_Whereclause

2. Save and Run Report.

Oracle Reports II-18

PT. Mitra Integrasi Informatika