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.
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.
Page 496
FIG. 18.24
The finished report
layout with fields aligned
and justified
with appropriate labels.
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 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:
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 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.
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.
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
These exercises use the exercises from Chapter 17 and build on them.