Reports Builder Components

Reports Builder provides a number of wizards and graphical editing tools to assist developers in creating reports. This chapter will start off by using the wizards to create a simple master-detail report, and then explore the advanced features of Oracle Reports 10 g by enhancing the report manually. Chapter 11 will discuss how to incorporate Reports into OracleAS Portal.

Note	The example report created in this chapter is created off of the demo schemas that are included with the infrastructure database installed as a part of Oracle Application Server 10 g .

Reports Builder consists of various programs and wizards that provide the developer with all the necessary tools to build reports that are robust, scalable, and secure. Navigating around these integrated tools all happens within the Reports Builder desktop. Oracle Reports 10 g provides great flexibility by not only giving the developer numerous ways of accomplishing almost any task, but by also providing numerous ways of invoking the various tools and wizards within Reports Builder. One of the obstacles many beginning Reports developers struggle with is determining where a task, such as creating a report trigger or altering the layout of a report, is performed. Oracle Reports 10 g is a mature product with many advanced features, and mastering these features will take a reasonable amount of time. The development tools and wizards have been assembled in a logical manner, thankfully, so while the Reports Builder windows may seem like a dizzying array of graphical objects, the learning curve is surprisingly short. In this chapter, we ll cover building a simple report using the Report Wizard, and then explore the advanced features of Oracle Reports 10 g by manually adding enhancements to it.

Wizards

Reports Builder is made up of numerous wizards to assist in the construction of your report. The report objects created and the code they generate is available for modification and enhancement to the developer at all times. One of the most powerful features of the wizards is the capability to run them over and over. After the initial code and layout has been generated, the wizards will automatically pick up on any modifications you have made to your report manually if you choose to run them again.

Tip	It is not necessary to use any of the wizards provided with Oracle Reports 10g. For developers uncomfortable with such tools, it is entirely possible to create every aspect of a report by hand.

The Report Wizard

Unless it has specifically been turned off, a dialog box asking how you would like to begin using Reports Builder is displayed upon starting the program. Selecting the first option, Designing: Use the Report Wizard, begins the Report Wizard that walks the developer through a series of questions regarding the following characteristics of a report:

Layout Reports can have a web layout (for reports designed to be displayed on the Web), a paper layout (for reports designed to be printed), or the wizard can generate both types of layouts.
Style The most common types of reports are
- Tabular The report is organized in a multicolumn, multirow format, with each column corresponding to a column selected from the database. The output is similar to that of a spreadsheet.
- Group Left/Above These reports suppress the repetition of values in a report by breaking, or dividing, the rows of table based on a common value in one of the columns . Group Left reports prints the common value to the left of the associated rows; Group Above prints the common value above the associated rows and is a good choice for master-detail reports where the master record does not contain a lot of information.
- Matrix reports These reports need grouping calculations performed on both its rows and columns simultaneously . Matrix reports are beyond the scope of this book.
Data source The data source defines where the data is queried. See the list of data sources listed earlier in this chapter.
Query Query defines the query.
Fields displayed on the report Not all fields specified in the query have to be printed on the report. It is possible, for example, to write a trigger at the group level to display a particular value based on the value of a column retrieved from the database, without displaying the retrieved column.
Summary columns Summary columns define where functions such as count, average, or sum are applied to any of the columns in a report.
Templates Templates define visual temples for your finished report.

In most cases, the Report Wizard is the first step the developer performs to build a report.

The Data Wizard

The Data Wizard is a subset of the Reports Wizard incorporating the Data Source, Query, Fields Displayed on the Report, and Summary columns pages. The Data Wizard can be used to modify queries already defined for the report without having to revisit all of the questions posed by the Report Wizard.

The Graph Wizard

The Graph Wizard is used to create graphs that become part of your report. The graphs that are produced become JSP tags. Graph attributes include title, subtitle , footnote, and axis title. References to user parameters, system parameters, and columns in the text can be used to display their value at run time. The Graph Wizard includes templates to create all of the standard graphs commonly found on reports: bar, line, area, pie, and combination graphs.

Major Reports Components

There are many components of Oracle Reports Builder 10 g that are intended to provide the developer with a rich graphical environment. These components are designed so that the developer has complete control over virtually every aspect of the report, yet needs to write a minimum of code to achieve the objectives of the report. Navigating your way through the many components of Oracle Reports 10 g can be difficult even for experienced developers. The following paragraphs detail the major components of Oracle Reports 10 g , their functions, and what they look like.

Note

Like any rich development environment, Oracle Reports 10 g provides the developer with numerous ways of solving problems. It cannot be stressed enough that there is no single right way to solve a particular problem. As such, the example tutorial in this chapter is but one way to create a basic report. As a developer, your goal should be to get yourself to the point where you can solve a variety of different problems using different tools and techniques quickly and efficiently . The more flexibility and creativity you can demonstrate , the more invaluable you ll become to your company. An analogy we often use is to compare software development with the construction of a house. If you were to take on the task of building a house, you had better become proficient at using a variety of tools. Would you try to build a house using just a hammer? (You wouldn t need a hammer to build Larry Ellison s house as there are no nails anywhere , but that s another story.) As the saying goes, People who only have a hammer see every problem as a nail. Don t be the developer who only has a hammer in your toolbox.

Object Navigator

The Object Navigator (see Figure 4-1) is designed to give a hierarchical view of all of the elements that make up your report. As you expand the categories (by clicking the plus signs to the left of each heading), you ll see that there are a multitude of different objects that can make up your report. It may seem overwhelming if you count up how many different objects can be used to make up a report (even a simple one), but by walking through basic report elements, it will be demonstrated how easy it is to build a simple report quickly and find the necessary report objects in the Object Navigator when it comes time to modify them.

Figure 4-1: The Object Navigator

Property Inspector

Almost every element that makes up your report has properties that describe how it looks and behaves. If we were to describe a car, it has various properties that describe it, such as its make, model, year, color, engine size, etc. If we were to list the attributes of a laptop computer, it would have some properties similar to a car: make, model, color , but other properties unique to a laptop: memory, hard disk size , etc. (Yes, yes, we know that some cars have MP3 players with hard disks in them, but ignore that for now.) Likewise, the objects that make up an Oracle Report have numerous attributes, some of which are shared among different reports objects.

The Property Inspector (see Figure 4-2) shows all of the properties of a particular report object or group of objects. As we ll discuss shortly, the Property Inspector gives you the ability to compare and set properties for multiple report objects simultaneously. This is invaluable for maintaining a consistent look and feel in your report.

Figure 4-2: The Property Inspector

Note

Figure 4-2 was purposely taken from a Unix version of Oracle Reports 10g (running on Red Hat Linux Advanced Server 2.1) to show the subtle differences in the display between the Microsoft Windows and Unix versions of Oracle Reports 10 g . While some of the Reports Builder screens and windows display differently, all the functionality of Oracle Reports 10 g is the same between platforms.

Report Editor

The Report Editor is one of the more complex screens in Oracle Reports 10 g . When the Report Editor is invoked, the developer has the option of viewing five different aspects of the report corresponding to the five buttons in the top left of the Report Editor window (see Figure 4-3).

Figure 4-3: The Report Editor window

The first (left-most) button corresponds to the Data Model view (which is displayed in Figure 4-3). This window shows a graphical representation of the query (or queries) that drives the report. It allows the developer to construct or modify a query and to visualize the relationships between tables. For a simple query involving one or two tables, this screen may not seem to be of much use, but can greatly assist the developer when creating complex reports involving numerous tables. Like almost all of the wizards and reports components, the data model can be reentered after changes have been made. An example of this would be the use of the Reports Wizard to modify an existing query. Reentering the data model will reflect any changes made there (and vice versa).

The second left-most button corresponds to the Web Source view (see Figure 4-4). If the Reports Wizard was chosen to construct the report, an option is presented to generate a Paper Layout, a Web Layout, or both. Choosing a Web Layout or both will automatically generate JSP code. The JSP code can be moved to your Reports Server to deploy Oracle Reports on the Web quickly and easily.

Figure 4-4: The Web Source view of the Report Editor window

Tip	The process of generating JSPs from within Oracle Reports 10 g and moving them to the Web is discussed in Chapter 6.

The third button corresponds to the Paper Layout view for the report (see Figure 4-5). On this page, the developer can arrange the elements of the report graphically, insert graphs driven by the data on the report, and insert header and footer text and graphics. The fourth button runs the report and displays the paper layout. The fifth button displays the Paper Parameter Form, where it is possible to design the form the end user will use to enter values that will drive the report (see Figure 4-6).

Figure 4-5: The Paper Layout view of the Report Editor window

Figure 4-6: The Paper Parameter Form view of the Report Editor window

PL/SQL Editors

Two PL/SQL editors are provided with Oracle Reports 10 g (see Figure 4-7). The Stored PL/SQL editor allows the developer to create PL/SQL program units that are stored in the database. The PL/SQL editor allows the developer to create PL/SQL program units stored in the report. In this manner, the developer has access to all of the benefits of a PL/SQL environment without the overhead of making calls over a network.

Figure 4-7: The two PL/SQL editors in Oracle Reports 10g

Triggers

The Database Trigger editor (see Figure 4-8) allows the creation of database triggers.

Figure 4-8: The Database Trigger editor

In addition to database triggers, Oracle Reports supports formatting triggers at both the group and field level. As we discussed earlier, there are also five report triggers defined by Oracle Reports 10 g at the report level. Some of their most common uses include:

Before Parameter Form Commonly used to get or initialize settings for the report before any interaction with the end user
After Parameter Form Particularly useful for constructing a SQL statement to drive your report from values on your parameter form that do not correspond to values in the database; for example:
- The database may contain values such as 10, 20, and 30 for regions in your company. While you could provide a drop-down list with 10, 20, 30 etc. in it, users will find greater meaning in values such as ˜Asia , ˜Europe , ˜North America , etc. The After Parameter Form trigger can be used to translate a value in the drop-down list of a parameter form (i.e., ˜Asia ) to a value in the database (i.e., 10).
- To provide a complex series of sorting or ordering criteria to your end users.
Before Report Can be used to alter the report based on what parameters the end users select.
Between Pages Executed between pages of a report; of limited practical use.
After Report Useful for post-report processing such as sending an e-mail notification to selected users that a report has been generated.