Previous | Table of Contents | Next

Page 495

then selecting the image or file. To add a drawing object, it is best to first create a frame, then select the tool from the tool palette, and then inside the frame draw what you desire.

FIG. 18.23
Boilerplate text has
been added to the
report to make it
easy to understand.


Perfecting the Layout

The last step will be the most time consuming and frustrating step in the whole process of creating a report. In this step, you work out the exact layout and look of the report. You will preview the report many times in this step until you get the report looking how you want it to look.

  1. To get the correct spacing and positioning of the objects that you created, or that the Default Layout Editor tool created, move objects closer together; use the spacing options on the repeating frame Object tab; resize objects by clicking the handles and dragging. This process will take time to learn and there are really no suggestions on how to do it because each report is different. You will learn by trial and error. Be patient. Figure 18.24 shows the finished layout.

Page 496

FIG. 18.24
The finished report
layout with fields aligned
and justified
with appropriate labels.


Adding Formula and Summary Columns

Summary and Formula Columns are added to the report in the Data Model Editor. To add them, click the icon for the column that you want then click in the data model where you want the column to be. These columns can be added inside the query or outside the query. However, with formula columns if you are going to reference a database column, you must put the formula column in the same group as the database column.

Summary Columns

Summary columns are really more than just sums. As discussed in Chapter 17, they can perform many different functions. Figure 18.25 shows the General tab of the summary column dialog box. The most common uses of this column are for sums of records and counts of records returned. To create a summary column, follow these steps:

  1. Give the column a meaningful name.

  2. Select a function to perform.

  3. Select the source column for the function. The Datatype and Width automatically fill in based on the source columns type and width. You may enter a value for the column to return in the case that the function return a null value.

The next step, selecting the Reset group for the function, is very important. This corresponds to the group the summary is in, if it is, otherwise you should select either report, meaning it is a function for the whole report, or page, meaning perform the function for a page worth of data.

Page 497

FIG. 18.25
General tab of
Summary Column.


The next two settings are available only in certain cases, but in those cases they are very important. If they are not set right, you will get errors. The first is Compute At, which is used when you use a function of % of Total. The group you select is the group the total is computed for. Product Order is used with matrix reports. The product order dictates the order in which the function will be computed when the summary column is inside the matrix. If this is not set the same as the Reset At, you will get an error.

The last setting that is available is Break Order. If the function that you are performing is distinct, you may use it as a break column. You can then also select the direction of the break.

Formula Columns

Formula Columns are functions that execute blocks of PL/SQL code. Figures 18.26 and 18.27 show the General tab of a formula column and the program unit editor window where the code is placed.

FIG. 18.26
General tab of a
Formula Column.


Page 498

FIG. 18.27
Program Unit editor
window for formula
columns.


The first thing you need to do is name the column with a meaningful name. Then you should select a Datatype and a Width. You can enter a value for the column to return if the code that you write returns null. Product Order is available only in a matrix report if the formula column is inside the matrix group in the data model. Break Order is available if the column will be returning a distinct value; otherwise, if you select it, you will get an error. Now you can click the Edit button. This will open a Program Unit editor window, as shown in Figure 18.27. This is where you enter a computation such as :salary*1.10 to calculate each person's salary, whether they were given a 10 percent raise. You can also enter PL/SQL code that you wany to execute for each record. The most popular use for formula columns using PL/SQL code is writing if statements to do certain calculations on a record based on the if statement. See the CD for more complex code examples for Formula Columns.

Summary

The Layout Editor of Oracle Reports is very complex and at times will be very frustrating. At the same time there is almost nothing you can't do in the layout with this tool.

You start with a very basic default layout and then through the use of the tool palette you can add and change the layout objects to make the report presentable. You can also add complex computations to the data model through formula and summary columns.

From Here…

The next chapter discusses moving Oracle applications to the Web. That chapter covers what software is required to make this all work and give details on configuration, running Web applications, and identify quirks and confusing items that I ran into along the way.

Page 499

Review Exercises

These exercises use the exercises from Chapter 17 and build on them.

  1. Rearrange the columns in the report. Display them in an order that makes sense to you.

  2. Add a summary column to the data model to sum salary at the department level. Also add the column to the layout and format it properly.

  3. Add a formula column to the report to count the number of employees in a department. Add the column to the layout with a boilerplate text label describing the column.

Previous | Table of Contents | Next