Unit V

Preparing for OCP Exam 5: Building Reports

Chapter 23

Introducing Oracle Reports

In this chapter, you will cover the following areas of building reports in Oracle:

• Designing and running reports
• Report Builder concepts
• Using Report Wizard to create reports

OCP Exam 5 tests your knowledge of writing reports against the Oracle database server using Oracle Reports, a component of the Developer toolset. This chapter introduces you to using Oracle Reports to develop and execute reports against the Oracle server. Both the creation of reports and the use of Oracle Reports for this purpose are covered in the test, with emphasis on how to use the Reports toolset. This introduction covers designing and running reports, Report Builder concepts, and using the Report Wizard to create new reports quickly. The chapter content covers 15 percent of material found on OCP Exam 5.

As an aside, and before proceeding any further, make sure you understand the basics of usage for the other Builder components, and the other Developer/2000 tools that were covered in Chapter 11. In particular, make sure you understand Project Builder, Query Builder, and Chart Builder. Only about 3 percent of OCP Exam 5 content is directly focused in this area, but they are easy points to score! Plus, there are several questions in OCP Exam 5 that require an indirect knowledge of other Builder components, so the time studying this material is well spent.

In this section, you will cover the following points on designing and running reports:

• Style and structure of common business reports
• Using Runtime executable to run prebuilt reports
• Identifying various report destinations
• Viewing report output in the Report Previewer

Many applications and work environments require the use of reporting to make decisions. As the report developer, you need to understand a few basic items about the development of reports before proceeding into other areas of developing and managing reports with Oracle Reports. This section covers basic concepts on the style and structure of common business reports. In addition, you may find that, in some environments, there are reports already in production that require support. This section will acquaint you with the requirements for running prebuilt reports with the Reports Runtime executable. The section will cover the identification of reporting destinations such as file and printer. Finally, the section will show you how to view the output for a report using the Report Previewer.

Business reports have a style and structure all their own. Several different types of reports may exist in a business, and each one may be used in a different way. Several different elements of commonality may exist between business reports, however. The most straightforward style of business report is a tabular report. This report shows data in a table format, similar in concept to the idea of an Oracle table. You may not have realized it, but you’ve already seen many examples of tabular reports in the form of output from SQL queries in SQL*Plus. Oracle, by default, returns output from your select statement in tabular format. Oracle Reports seeks mainly to beautify the output and add some features like showing totals on columns and other items that are helpful—particularly for financial reports. The following code block shows a simple tabular report:

EMPID LASTNAME FIRSTNAME SALARY
------- ---------- ---------- ----------
059495 Flurb David 25000
965383 King Mustafa 56000
593834 Yojumbo Bill 29380

Sometimes your simple tabular report will contain data where the columns on the right may contain unique data while the entries on the left are duplicates. For example, when your report displays a table join or select statement containing a group by clause, where there is a one-to-many relationship such as managers to employees, you may have a manager’s name listed 20 times on the left, once for each unique employee. A group-left report can help to unclutter your output in this situation. Consider the following code block, which displays a simple group-left report:

MANAGER EMPLOYEE
----------- ------------
ROBERTS MCBOOP
DINNY
LIVINGSTON
ELLISON DAVIS
FLUMPY
GRUMPY

Another type of report common in business situation is a matrix report. This type of report adds another column to your basic tabular report containing a set of fixed items down the vertical axis. The matrix, then, shows the value at each intersection of fixed value along the horizontal axis at the top and the vertical axis on the left. Consider a simple example of a matrix report: a table showing a value by which you multiply your income to find how much you owe for income tax. Along the horizontal axis at the top you may see several categories corresponding to your filing status, such as married, single, and married filing separately. Along the left vertical axis, you will see various income brackets. The body of the report shows a multiplier that you use to determine your income. The following code block shows the simple example:

| SINGLE | MARRIED | MFS
--------------------------------------
0-10000 | 0.18 | 0.15 | 0.17
10001-45000 | 0.21 | 0.19 | 0.20
45001-90000 | 0.25 | 0.21 | 0.23
90001-up | 0.28 | 0.25 | 0.27

You may have combinations of report types as well, such as matrix with group. Other types of reports are reports containing multimedia objects like charts, graphics, sound clips, and the like. Another two types of reports include form letters and mailing labels. Oracle can store all these items in the database, so there’s nothing preventing you from creating reports of these types and more. The examples shown here are only examples of the reports you may be required to do.

Many times, you will have a production environment where several reports already exist. An existing report that is compiled and ready to run using Reports Runtime will have the filename extension .rep. To process these reports, you must use Reports Runtime. This tool can be executed interactively in Windows-based environments or in batch from an operating system command line or shell script. This discussion will help you understand the use of Reports Runtime working in batch mode. To run a report from an operating system command line, you issue the appropriate command, passing in the appropriate parameters. The appropriate command depends on the version of Oracle Reports you are running: r25run for Developer/2000 1.x or r30run32 for Developer/2000 2.0, respectively.

Reports Runtime behaves according to the runtime options you specify via command line parameters. Most of the parameters available for you to specify on the command line are included in Table 23-1. Those that aren’t include parameters that handle runtime report tracing and some parameters usable only in character mode. To change any of these parameters from within the Reports Runtime interactive session, use the Edit | Runtime Options menu item to open the Runtime Options dialog box.

Once your parameters are defined either on the command line or in your command file, you are set to run your report from the operating system command prompt. Alternately, you may wish to define the execution of your report to happen from a script, allowing you to set up report execution as part of some time schedule. The following code block shows the command line you may use to run a report in batch from the operating system command prompt where you pass parameters:

c:\orant\bin> r30run32 module=jason.rep userid=jason/stacy background=y

Or, you can place all your parameters in the command file, as follows:

c:\orant\bin> r30run32 cmdfile=jasonrep.cmd

In this example, the contents of jasonrep.cmd appear in the following block:

MODULE=jason.rep
USERID=jason/stacy
BACKGROUND=y

You can run a report executable in one of two modes: character and bitmap. Report running mode is determined by the MODE parameter, which can be set to character, bitmap, or default. Character mode is a holdover from older UNIX-based Oracle environments where the reports appeared on your UNIX character-based terminal. Bitmap mode is used for Windows-based environments and offer graphical output.

Some parameters for running reports using Reports Runtime are meant for character mode only while others are meant for bitmap only. For example, the TERM parameter is useful for reports running in character mode because Reports Runtime needs to know what type of terminal to format the distributed output for. TERM is not useful for bitmap reports, however, because the output for the bitmap report will be graphical, not character-based. Other parameters are interpreted differently based on whether the report is running in bitmap or character mode. For example, the PAGESIZE parameter measures size of page in inches, centimeters, or picas if you are running your report in bitmap mode, but measure in characters per page if you are running reports in character mode.

As you may have already surmised from your exposure to the DESTYPE Reports Runtime parameter, there are several different destinations to which a report can be sent. Some of these destinations include the printer, a file, email, the Report Previewer, the screen, or default system output (usually the screen). For the most part, production reports have one primary destination: the printer. Usually, the report goes to a business analyst that prefers hard copy to something on the computer screen. Another common destination is email. For some production purposes, this destination may be sufficient, although email is not 100 percent reliable. Still, a good many production reports find their way into email boxes in the office.

You can run your report interactively and send it to the printer or email as well. After starting the Reports Runtime executable, select the File | Print or File | Mail menu option, and Reports Runtime will prompt you to enter which report you want to run. After processing the report, the executable will send the report to the printer or email address you define.

Other destinations for reports are usually used for development of the report. For example, you may have written the SQL for a report but you are not sure if the data you queried is sufficient. Also, you may want to inspect other visual aspects of the report before allowing others to see it. Setting the destination to be the Report Previewer works best in this situation, or simply running the report using Reports Runtime interactive mode works well also. Bear in mind that if you send a report to the Report Previewer destination, you must run the report with BATCH=no.

A special output destination you may not find yourself using often but that has many uses nonetheless is sending the output to file. One interesting application for this destination in production is to send the output of the report execution to file, then simply print the file. This execution may be useful for those situations where your users frequently complain of lost reports, or want a copy of a report from several days ago. Rather than having to rerun the report to satisfy these requests, you can simply output your reports to file every day, print the file, and keep a copy around for a specified period of time.

The output to file destination option also allows you to store multiple copies of the report, corresponding to different file or printer types. For example, you may run the report and send the output to file once for PostScript printers, once for Portable Document Format files, and once for HTML files for the Web. To send report output to file, you can either run the report from the operating system command prompt or set the runtime options in Reports Runtime using the Edit | Runtime Options menu option. In the following code block, you will see the appropriate parameters to set if you would like to run a report called cats.rep from the command line, sending the output to file that can be put onto a Web site and made available for all:

/home/oracle/> r30run module=cats.rep userid=jason/stacy destype=file \
> desname=cats.html desformat=HTML

It is often useful in the development of reports to view the report output onscreen before printing a hard copy. The Report Previewer in Reports Runtime allows you to accomplish this task. To see report output online using Reports Runtime and the Report Previewer, execute the following steps. You can start the Windows-based Reports Runtime executable usually by clicking on whatever icon corresponds to Reports Runtime. From within the application, you click on the File | Run menu option to execute a specific report. Using the open file interface, choose the report you wish to run by clicking it once to highlight and then click the Open button. Your report must be in report executable format with the extension .rep in this situation. Reports Runtime will prompt you to provide user information to log on to the Oracle database to which the report runs on. After connecting, Reports Runtime executes the report against the Oracle database and then shows the output for the report in a Report Previewer window. Figure 23-1 shows the Reports Runtime executable as it appears on your display.

Alternately, you can run the report from the command line with BATCH=no, and DESTYPE=previewer. Reports Runtime will simply come up in interactive mode, processing your report and then showing the result in the Report Previewer. You cannot bring up the report in the Report Previewer if BATCH=yes.

In this section, you will cover the following points on Report Builder concepts:

• Executable components of Oracle Reports
• Invoking Report Builder and its components
• Main objects in a report

With some of the business elements and facts about running existing reports covered, turn your attention now to understanding how to use Report Builder. This is the tool you use to create new reports in Oracle. It has several components that are designed to help you develop reports quickly, customize their appearance, and view how they will look in printed form online. This section will cover the executable components of a report, along with the components of Report Builder. Methods used to invoke Report Builder are covered as well. Finally, the section details the main objects in a report.

The discussion of Reports Runtime in the prior section functions as an introduction to the executable components of Oracle Reports within the Developer/2000 product. There are several other components within Developer/2000 that comprise the Reports toolset. The following discussions tell you what the components are and also give you a bit of information about their purpose for Oracle Reports in Developer/2000 2.0.

The following executable items are included in Oracle Reports for Developer/2000 2.0:

• Report Builder Tool for use when building new reports or modifying existing reports. Executable is r30des.
• Reports Runtime Tool for use to run reports in production from operating system command lines or within scripts. Executable is r30run. A similar executable allows you to run a report with the use of the Reports Server, and that executable is r30cli.
• Reports Queue Manager Tool for use to manage queued reports. Executables include r30rqv for UNIX, and r30rqm32 for Windows.
• Reports Server Tool for use on multi-tier architectures for running reports. Allows for scheduling or report submission. Executables include r30mts.
• Reports Web CGI Tool allowing you to execute reports on the Reports Server from your Web browser. Executable is r30cgi. Additional executable called r30ows provides the interface between Reports Server and Oracle Web Server.
• Report Converter Tool allowing you to convert reports or PL/SQL libraries on one storage type for the client to another storage type for the server, and vice-versa. Can also be run standalone to take a built report and compile it to run in batch or interactively using Reports Runtime. Executable is r30conv.
• Reports Background Engine Tool allowing you to execute reports locally. Use is similar to Reports Runtime, but on some operating system platforms like Windows, this executable may give you better performance. Executable is r30rbe or r30isv.

This section focuses on the use of Report Builder for building new reports. There are two ways for building new reports with Developer/2000. You can build a report manually if you have an intermediate or advanced knowledge of how Report Builder works, developing the data definition for your report, the style of the report, and other features. Alternately, you can use the Report Wizard as a guide for building your report. It may be useful to start building your reports with Report Wizard, then customize later using other components of Report Builder.

Within Report Builder, there are several components for developing new reports and modifying existing ones that you need to understand. Under the Program menu in Report Builder, you will see the same modules you found in Procedure Builder, the Object Navigator, Program Unit Editor, Stored Program Unit Editor, and Database Trigger Editor. Recall also that you cannot use Stored Program Unit Editor and Database Trigger unless you are connected to the database.

The components of Report Builder that are used in the design of reports include the Report Builder Object Navigator, Report Editor, Template Editor, and the Property Palette. Another component of Report Builder is the Parameter Form Builder, which is used for developing parameter forms for your reports. Another set of components in Report Builder include the wizards for developing reports and other items quickly. They include Report Wizard, Chart Wizard, and Web Wizard (Developer/2000 2.0). These items can all be found under the Tools menu when the Object Navigator module is in the foreground and active on Report Builder.

The Object Navigator is the master module on Report Builder. It helps you find your way through the development of a report. The Object Navigator has several drill down items available for your use, including several that should by now look familiar, including reports, templates, external SQL queries, debug actions, stack, built-in packages, and database objects. Understanding and using the Object Navigator is key to good usage of Report Builder. Figure 23-2 shows you the Object Navigator module in Report Builder. The Object Navigator module opens automatically as part of invoking Report Builder. See the next discussion for how to invoke Report Builder.

Another important component of Report Builder is the Report Editor module. This interface allows you to do many things in developing your reports. You can view the data definition or data model using Report Editor, you can look at the parameter form, you can view the report layout, and you can even see the report as it will look on paper. This final choice is often referred to as the Live Previewer. You toggle between these three views on your report within the Report Editor module with the use of the three buttons in the upper left-hand corner of the Report Editor module, as shown in Figure 23-3. To open the Report Editor, click on Tools | Report Editor on the Report Builder menu.

The Property Palette is another Report Builder tool allowing you to view and modify various properties and facets of your report. Overall categories that you can modify include general information, substitution file properties, report properties, and character mode properties. To modify an item on the Property Palette, you simply need to click on the property you want to change and its value will automatically be highlighted in such a way as to allow you to modify it easily. To open the Property Palette, click on Tools | Property Palette on the Report Builder menu. Figure 23-4 shows the Property Palette of Report Builder.

The Parameter Form Builder is another Report Builder tool discussed here. This module helps you build a parameter form for the current report. Figure 23-5 shows the Parameter Form Builder module running in Report Builder. Notice that several items should look familiar from your discussion of runtime parameters in Oracle Reports. Within this interface, you can enter values for these parameters that will then be used to compile your report. Clicking on Tools | Parameter Form Builder from the Report Builder menu will bring up your Parameter Form Builder. Figure 23-5 shows the Parameter Form Builder running within Report Builder. Later in this chapter, you will find out more about Report Wizard, a tool within Report Builder that helps you build new reports quickly and easily.

The final component of Report Builder is the Report Template Editor. You can use this interface to design and modify templates used to enhance the layout and appearance of your reports. Several canned templates are included with Oracle Reports, and you may modify these report templates as you need to, or create new templates with the editor as well. Figure 23-6 shows the Report Template Editor module in Report Builder.

Report Builder gets run usually on a Windows-based machine. To execute it, you will usually click on an icon. Each operating system is different, so there is no standard way to invoke Report Builder. However, usually behind the scenes on the GUI there is an executable file. For many operating systems, that runtime executable file for Report Builder is r30des.

Recall that several parameters were presented in conjunction with the use of Reports Runtime. You can use these parameters for running Report Builder as well as Reports Runtime as well. For example, if you wanted to kick off Report Builder, connect to the database, and open the all_tables.rdf report in one fell swoop, you might use the following command line from your operating system to do so:

$> r30des userid=jason/stacy@devdb report=all_tables.rdf

A report may have several items comprising it. To see the items comprising a report, you can open a report and view the items for that report within the Object Navigator. The following list provides insight on the main objects you will see in most reports you develop, corresponding rather directly with the nodes in the Object Navigator.

In this section, you will cover the following points on creating reports with Report Wizard:

• Creating simple tabular reports with Report Wizard
• Methods for building report queries
• Summarizing report values
• Modifying style and content of reports
• Other report styles available in Report Wizard

Simpler reports can be designed quickly using a feature built into the Reports tool called the Report Wizard. This utility is a graphical user interface that walks you through the steps of creating your basic report, prompting you to plug in your desired features every step of the way. This feature masks some of the challenges a beginner faces in learning Reports well enough to develop one, but there is some value for advanced Reports developers as well. This section covers the use of Report Wizard and the steps you must do every step of the way. You have a running example for this section. You are a wildlife conservator in Queensland, Australia, tracking kangaroos in the wild. Your database consists of several tables, including KGROO, KGROO_LOC, and KGROO_JOEYS. You will now generate some reports about your kangaroos using Oracle Reports.

The most basic type of report in the Report Wizard is the tabular report. It may consist of a simple table, with headers and column values. Optionally, you can also set up a banner at the top of the report, along with other visually appealing options. The Report Wizard makes many of these options easy by providing several canned formats, which you will cover later. You encounter a welcome screen when you start Report Builder for the first time. There are several radio buttons indicating your options. They are to create a report using the Report Wizard, create a report manually, and other things. If you opt to create a report with the Report Wizard, you will then enter the Report Wizard automatically. Figure 23-7 shows you the first screen you will encounter in the Report Wizard.

The first screen in Report Wizard helps you define the report style. There are several different styles available for creation within Report Wizard. Each one is listed with a radio button next to it that allows you to select the report style. For now, you will focus on the first style in the upper left-hand corner of this interface: the tabular report. Clicking once on the radio button next to the word "Tabular" on the style window and then clicking the Next button defines the style. Later in this section, you will be introduced to the other styles available in Report Wizard, and shown how these other styles correspond to the common styles in business reports described in the beginning of the chapter.

Once you define the style, Report Wizard puts you into the next screen. Your next screen in Report Wizard helps define the data your report contains. Your options for defining data are numerous. You can write your own SQL statement, import a SQL statement from flat file, or build a SQL statement visually using the Query Builder. The next discussion covers each of these options in more detail. For now, assume your report is really simple—merely a select statement to obtain all data from your KGROO and the KGROO_JOEYS table to show physical description and descendents of all kangaroos in the system. The following code block illustrates the SQL statement you will type into the text box in the center right on the Report Wizard data definition window in Figure 23-8. Once complete, click on the Next button.

SELECT k.kgroo_name, k.color, k.weight, k.pouch_sz, j.joey_name
FROM kgroo k, kgroo_joeys j
WHERE k.kgroo_name = j.kgroo_name;

The next window in Report Wizard is the field definition window. It helps you define the columns that appear in your report, based on the columns appearing in your SQL query. Figure 23-9 displays the field definition window of Report Wizard. There are two text boxes. The one on the left contains lists of columns in your SQL query. Initially, the box on the right will be empty. You add or remove columns in your SQL query from the left to the list of columns in your report on the right with the right and left arrow buttons between the two windows. When complete, you click the Next button.

Once your field definitions are defined, you may want to perform some calculations on your data. This feature sees most of its usage in spreadsheet accounting applications that pull data from the Oracle database and perform calculations on that data. Some of these options include showing the sum of a column, a count for items in the column, average value, maximum value, or minimum value. In this example, you won’t see too much of that, so for now don’t worry too much about understanding the features on this screen. We will cover summarization of report values shortly. Figure 23-10 shows the Report Wizard totals definition window.

After defining any totals you want to obtain on your fields, you then define the column headers you want to use to identify data in your report with the labels definition window. Report Wizard generates samples based on dropping any underscores from your column names, then displays them in text boxes next to the column names as they appeared in your SQL query. You are free to change these labels to whatever you like. In addition, you may define the column width and any format masks, such as money formats and others, to make your report more readable. Figure 23-11 displays this window.

The final touches on your report are possible with the last window of Report Wizard. This is the template definition window. Your report inherits the characteristics of the template selected, including banners, logos, and other visually appealing items. Report Wizard supplies many canned templates to work with, and the wizard allows you to preview them before making your final choice. If you do not like the canned templates, you may also provide one of your own or even none at all by selecting the appropriate radio button. The template definition window is shown in Figure 23-12. Once you have selected your template, click the Finish button.

The tool then creates your report and shows the result in the Live Previewer, which will be discussed in the next section. Optionally, you can print your report by clicking on the Print button from the Live Previewer or by selecting the File | Print menu item from the Report Builder main menu bar.

As stated, several options exist for creating the query serving as the foundation for your report. Return to the data definition interface for Report Wizard in Figure 23-8 now and see if you can determine first what they might be. We have three methods for defining the SQL query. The first has already been identified: simply typing the desired SQL statement into the text box on the data definition window of Report Wizard. You can then validate that SQL statement against the database by first clicking on the Connect button and entering your username, password, and SQL*Net or Net8 connect string, and then clicking on the Check Syntax button. With this method covered, turn your attention now to the other two methods for defining your report data: importing SQL from file and using Query Builder.

This option is only slightly more complex than coding the SQL statement into the text box on this interface window. You may have SQL statements stored in flat files on your computer. You can turn these into reports by importing the statements from your file in the following way. First, from the data definition window on Report Wizard, click the Import SQL Query button. A pop-up window appears to help you locate the SQL text file. Once you have identified your file, highlight it and click Open on the interface, and your SQL text will appear automatically in the text box back on the data definition window in Report Wizard.

Query Builder is a tool designed to help you define data based on visual database table representations rather than SQL statements. Some developers find Query Builder particularly useful in developing queries according to the data model for the database. Since Query Builder uses visual aids to develop SQL, theoretically it is useful to a developer whose understanding of SQL is limited. However, your passage of OCP Exam 1 should have made you an effective user of SQL. To use Query Builder to define the data in your report, you click on the Query Builder button. If you aren’t connected to the database already, Report Wizard prompts you to do so at this time. Figure 23-13 shows the data table selection interface that appears when you enter Query Builder as well. You then choose which tables to include in your report. In this case, revisit the wildlife conservator in the outback.

You choose the schema owner of the tables you want to include in the query by clicking on the list box toward the top of the data table selection interface. The default schema you will see is your own. You choose your table by clicking on its name in the list once to highlight it and then click on the Include button to include it. Once you have included all the tables you want, you then click on Close. You then click on the columns in the table box within the Query Builder interface to include those columns in your query. Query Builder will place a check mark next to the column name to show the column is included. After selecting the columns you want in the report, you then define the relationship that exists between the columns.

In the example, the two tables selected share a common column, KGROO_NAME. To do so, you click on the Set Table Relationship button indicated by a table pointing to another table. A dialog box appears for you to key in the columns that relate. Be sure to precede the column names in both text boxes with their respective table names, especially if the column names in both tables are identical. You can choose the comparison operation, outer join condition, and whether to use the relationship in your query visually using check boxes and radio buttons. If you are happy with an ordinary table equijoin operation, simply picking the columns from both tables will establish the relationship. This is worth mentioning before you get fancy with Query Builder. Once complete, click OK and Query Builder will display the two tables again, this time with an arrow between them to display the relationship, as shown in Figure 23-14. You then click OK on the Query Builder interface to define the SQL statement. The SQL you defined will then appear in the text box back on the data definition window of Report Wizard.

Recall in Figure 23-10 that you can define totals on your report to summarize report values. Two text boxes appear in this window, with several buttons between them to define the summarizations available. The buttons operations include Sum, Average, Count, Minimum, Maximum, and Percent Total. These buttons map to group functions of similar names, such as sum( ), avg( ), count( ), min( ), and max( ). If used, the report will contain a summary line at the end where the result of the corresponding group function will be displayed along with an identifier.

Once finished, the Report Builder displays the report output in the Live Previewer interface. Your development effort needn’t stop there. If you are dissatisfied with the looks of the report, you can return to the Report Wizard to alter the columns displayed, add new format templates, change the style, or just about anything else. Simply start Report Wizard again by clicking on the Tools | Report Wizard menu item on the main Report Builder, or click on the Report Wizard button in the Live Previewer button bar at the top of the interface, shown as a piece of paper with a pencil. The full Report Wizard interface is shown, with both Next and Back buttons and tabs to navigate through the wizard steps.

Any aspect of the report can be changed with Report Wizard in this mode. You can skip over to the appropriate area you wish to reconfigure by clicking on the appropriate tab at the top of the Report Wizard interface. When you have completed your changes, you click on Finish again and your changes are made to the report. Report Builder then applies the changes automatically and dynamically changes the appearance of the report in the Live Previewer according to your specifications.

You can, for example, change the style of the report from tabular to something else by clicking on the Style tab on Report Wizard and selecting a different style radio button. You can also change what columns appear in the report by changing the SQL statement generating data for the query by clicking the Data tab and modifying the data defining SQL statement. Or, you can change the columns appearing in the report by clicking the Fields tab and adding or removing columns in the data definition, or change the totals appearing in the report by clicking on the Labels tab and adding or removing items there. You can change the report template as well. In all cases, Report Wizard dynamically updates the report to contain the new features you specified as soon as you click the Finish button, and displays the new report version in the Live Previewer. Bear in mind that changing the style of your report may in some cases require a redefinition of the report’s data. To redefine the data in your report, refer to the previous discussion on defining data.

Several other report styles are available in Report Wizard. Recall from Figure 23-7 that the Report Wizard style definition window lists eight choices for report styles. They are Form-like, Mailing Label, Form Letter, Group Left, Group Above, Matrix, and Matrix with Group, in addition to the basic tabular format shown as output from queries in SQL*Plus. A description of each report, along with its appearance and ideas about usage appear here. You can alter a report style with the use of Report Wizard after you have already created the report as well, or create a report of these other types by going through Report Wizard in the same way as you went through a tabular report.

A form-like report prints each row of output on a separate page for your report. This can be especially handy in situations where your report contains few rows with many columns, in which the column data is very wide. The labels for each field are placed to the left of the data, as opposed to the top, as in a tabular report. The following code block shows a sample of what form-like output might look like in an imaginary example:

NAME Jason Scott Couchman
OCCUPATION Consultant for Fortune 500 Companies with Oracle experience
RESIDENCE Tennessee, has lived in North Carolina and grew up in New York

A mailing label report is straightforward—it is a list of addresses designed to be reproduced on mailing labels. The addresses can be printed several across the page, and then several down. Reports can print the labels across first, then down, or down first, then across. This is a handy feature for mailing lists maintained in an Oracle database. The following code block illustrates a simple example:

Flumpster Dumpster Bumpster
10 Oak Lane 15 Toad Street 49 Main Avenue
Mead, AL 30494 Akron, FL 403945 Sunset, CA 94049

The form letter report complements the mailing label report. You can pull a key bit of text, along with names from a database, to produce form mailings efficiently. Since you have undoubtedly seen a form letter at least once, an example will be foregone in this situation.

Group-left reports are handy for producing output from SQL statements with the group by clause in them. The group-left report suppresses redundant printings of the same value in the leftmost column of the report. You can use the group-left report to display master-detail or parent/child relationships as well. Going back to the kangaroo example, you can see that group-left reports are handy for printing out the names of each kangaroo in such a way that those who have many joeys will not have their names repeating once for each joey in the report. The following code block shows an example of a group-left report that does all these things, literally displaying a parent/child relationship:

KGROO NAME KGROO COLOR JOEY NAME JOEY COLOR
---------- ----------- --------- ----------
SKIPPY BROWN SHELBY RED
ALEXANDER BROWN
THOR BROWN
FRANCIS RED DINGO RED
RINGO RED

A group-above report is a method you can use to demonstrate a master/detail relationship in your database. You define a master group, and for every master record obtained, Oracle will obtain the related values from the detail groups. These reports can be useful for situations where you have a set of data to which other elements in another set of data belong. Consider the following example of a group-above report where the different kangaroo colors are listed across the top and the names of kangaroos are grouped according to the colors above them:

RED BROWN
--------- ----------
SHELBY SKIPPY
FRANCIS ALEXANDER
DINGO THOR
RINGO

A matrix report is a chart with two axes that displays for sets of data. On the horizontal axis, the report displays one set of data, while on the vertical axis the report displays another set. Within the two axes, Oracle report displays a "matrix" or cross-product of results, where the third group of data is the locations of data within the matrix and the fourth is the actual data. The following code block shows the appearance of a matrix report of numbers whose quotient is a whole number. Key to the matrix report is the fact that the number of rows and columns are not known until the results are obtained. In the following matrix report example, imagine that your sales staff fluctuates because everyone sells on commission, and new products are added every day. Thus, there is really no way to know who sold what before the matrix report is run.

YOGURT BANANA CHEESE HOTDOG LIVER
----------------------------------------------
CASEY | 10 0 0 9 9
LACEY | 0 11 7 2 3
FRED | 1 4 0 5 5
AHMED | 504 3 5 67 40
LARRY | 4 105 0 3 0

There are many other types of reports available in Oracle Reports. A matrix with a group report is a combination of a matrix and a group-above report. These types of reports can be good for master/detail records. You can develop multimedia reports as well, combining sound, video, and graphics to give an enticing effect along with data capture. An OLE report can be used as well to embed charts and graphs into your report along with data.

This chapter gives you an introduction to Oracle Reports as a tool to design, develop, and run reports against your Oracle database. The execution of existing production reports is covered, along with design and modification of reports using Report Builder and the use of Report Wizard. This section provides a foundation for use of Oracle Reports that will serve you well in the coming chapters, on the OCP exam, and in your career with Developer/2000. All in all, the material covered in this chapter covers about 15 percent of OCP Exam 5 test content, with an additional 3 percent of content dedicated to your understanding of Developer/2000 from Chapter 11.

The first section in this chapter covers the execution of existing reports using Reports Runtime. One topic covered is the style and structure of common business reports. The most basic type of business report is the tabular report. You will see tabular reports every time you select information from the Oracle database, Oracle Reports will clean it up and present it to you in an elegant manner. A related type of business report is the group report. You may have several master/detail relationships in your database, such as parents to children, managers to employees, or servers to clients. There is often redundancy in showing all the individual details associated with a few masters on a simple tabular report. Thus, you can use group reports to reduce the number of times a master record is displayed. Another important type of report is the matrix report. These reports are useful when analyzing a cross-product of two different datasets. An example of a matrix report would be two lists of cities and the mileage between them.

The chapter described a common situation in the business world related to reports where the organization will have several reports already running in production that need maintenance. There is a runtime executable called Reports Runtime that allows you to run the reports from your operating system command line, or from within a script. This functionality is especially useful when running reports according to a batch schedule. You can define how your report will run with use of runtime parameters. Some parameters are required for execution, such as USERID, MODULE (formerly REPORT), DESTYPE, DESNAME, and DESFORMAT. Others are optional, and are used primarily for formatting output in the report. Some examples of these types of parameters are CURRENCY, THOUSANDS, and DECIMAL. Still other parameters are used to define how the report performs during the run and to give you status on the report’s execution. These parameters include READONLY, LOGFILE, BUFFERS and ARRAYSIZE.

A report runs in one of two modes: character and bitmap. Character mode displays all US7ASCII or other text-based characters on a report page only, while bitmap reports have the capacity to display graphics like logos and that sort of thing. The mode in which a report executes is determined by the MODE parameter. This parameter can be set with the values character, bitmap, or default. Other parameters may have double meaning, depending on whether you run your report in character mode or bitmap. Such parameters include PAGESIZE, which can mean size of the page in inches like 8.5 × 11, or number of characters that will fit on the page like 66 × 80.

A report can find its way to many different destinations. The chapter covered several of those destinations, including the printer, email, file, the screen, the Reports Previewer, and default system output (usually also the screen). The destination for a report is defined with the DESTYPE, DESNAME, and DESFORMAT parameters. DESTYPE identifies the destination of the report, such as file, printer, or mail. DESNAME gives Reports Runtime the name of the printer, file, or Oracle Office email user to which the report will be sent. DESFORMAT is for use mainly with printing your reports, as it specifies what sort of print driver to use when printing the file.

Useful in Reports Runtime is to send the report to the Report Previewer. It can be helpful to look at the report on the screen before printing it in production sometimes, if you have made an enhancement to the report, or are trying to look for errors. To view the report in the Report Previewer, run Reports Runtime interactively (as opposed to batch mode). From the Reports Runtime GUI, you can then click on File | Run and look at the report on the screen. Alternately, you can issue the command to run Reports Runtime from the operating system command line with the BATCH parameter set to no and the DESTYPE parameter set to previewer, and Reports Runtime will execute your report and show it in the Report Previewer.

There are many executable components in Oracle Reports. Reports Runtime is one. Report Builder and Report Converter are others. Report Builder is used to develop new reports and modify existing ones, while Report Converter is designed for use when making a built report into one that can be executed using Reports Runtime. Reports Server, Reports Background Engine, and Reports Queue Manager can all be used to execute reports within an multi-tier architecture where an application server handles reports separate from the client machine and database server. Reports Web CGI and Oracle Web Application Server handle Web-enabling for your reports processing needs. Finally, Reports Converter can be used to change reports of one storage type on the client to another storage type for the server, or vice-versa.

Once you are used to running reports in production and know all the executable components of Oracle Reports you need to know, you will need to start thinking about Report Builder for use when creating new reports. Report Builder has several components you should understand. Some of these components include the Object Navigator, Report Editor, Template Editor, and Property Palette. The Report Editor allows you to view and modify your report layout and content. The Property Palette identifies various properties of the report, such as general information, substitution, character mode, and other report properties. Another component of Report Builder is the Parameter Form Builder. This tool helps you build a runtime parameter form for the report. Several of the parameters you can set in the Parameter Form Builder are the same as parameters you can issue from the command line when using Reports Runtime to execute your report.

The chapter covers the components of a report that you should know for certification as well. Every report has a data model, or the definition of the data from your database that will appear in the report. This model includes the tables that will be used on your report and any relationships between them you define and use. In addition, the report has a layout mode, which shows how the report will look on your page. This view shows the general appearance of the report on the page, including the columns, groups, or matrices you define, along with cover or trailing pages, headers and footers, logos, graphics, or other visual enhancements. The parameter form is another component of your report. This is a set of parameters that define how Oracle Reports will display the data content to the report. Your report may contain triggers that define events occurring at various times in your report execution, such as between pages, at the beginning, or at the end. Your report may contain PL/SQL program units or libraries as well.

The final aspect of using Oracle Reports covered in this chapter is using Report Wizard to define reports of simple to medium complexity. For this section, you concentrated on building tabular reports, though Report Wizard allows you to create reports of other types as well. Report Wizard is a component of the Report Builder that helps you define different aspects of your report, such as the data definition and other report body content, a template to use with the report, layout and style, and other items. There are two ways to define your report with use of Report Wizard. The first occurs from the Oracle Reports welcome screen, where you may opt to build a new report from scratch using Report Wizard, moving from definition of one component to another with the Back and Next buttons at the bottom of each screen. The second method of moving through the interface is to invoke it after Report Builder is running with the Tools | Report Wizard menu item.

There are tab pages available in Report Wizard. They are style definition, data definition, field definition, totals definition, labels definition, and template definition. Style definition is where you identify what basic format you want your report output to take, such as tabular, group, mailing label, form letter, matrix, and others. Data definition is where you identify the SQL query your report will use to glean data from your Oracle database. You can define this query yourself using the SQL skills you developed for OCP Exam 1 or use the Query Builder tool to identify tables from your database and define relationships between them visually, then check off the columns you want to appear in the report. Field definition is your chance to eliminate columns that are part of the data definition from what actually appears on the page of the report. For example, to use a join statement in your SQL query, you may have included some columns to satisfy the join condition but don’t want to show those columns in the report output. The field definition interface allows you to suppress those columns. The totals definition is used particularly for accounting or spreadsheet-type tabular reports where you want to show totals or other summary data on one or more columns. The labels definition gives you the chance to alter a column name in your tabular report to whatever you like. Finally, the template definition allows you to modify the overall look of your report to include graphics, logos, headers, footers, and other items to enhance the report’s visual appeal. Several built-in templates exist in Oracle Reports already, and later material in this unit will cover how you can develop new templates and make them available for use within Report Wizard.

• Tabular reports where the data appears in columns as in the output from SQL statements
• Form-like reports where the column name appears on the left, the value appears on the right, and each row appears on its own page
• Mailing labels and form letters
• Group reports to show master/detail relationships
• Matrix reports showing two sets of data and their cross-product

• Prebuilt or existing reports are run in Oracle Reports using Reports Runtime. This tool can be run either from the Windows-based operating system by clicking on the appropriate icon or the Start button, or from an operating system command line by passing appropriate parameters.
• The following items explain command line parameters for Oracle Reports Runtime:

• MODULE, BATCH, and USERID identify what report to run, whether the report runs in batch, and what user to log in to the Oracle database as to run the report.
• PARAMFORM and CMDFILE identify where Reports Runtime should pull parameters from to run the report.
• DESNAME, DESTYPE, and DESFORMAT identify the destination name, type, and format for the report.
• ARRAYSIZE and BUFFERS identify memory handling for array fetches and overall memory size for report execution.
• CURRENCY, THOUSANDS, and DECIMAL identify special characters Reports Runtime should use for numeric representation in the body of the report.
• ONSUCCESS, ONFAILURE, READONLY, AUTOCOMMIT, NONBLOCKSQL, LONGCHUNK, and ROLE define how Reports Runtime will manage transaction processing, data retrieval, and database privileges when connected to the database.
• ORIENTATION, BACKGROUND, and PRINTJOB define how Reports Runtime aligns report data on the page, whether the report runs as a background process, and whether the Print dialog box will display to define further print options.
• MODE, PAGESIZE, and TERM determine whether the report runs in bitmap or character mode, what the size of each page will be, and the terminal type if the report is running in character mode.
• LOGFILE, ERRFILE, PROFILE, and RUNDEBUG identify names of runtime log, error, and performance statistics for report execution, and also determine if Reports Runtime will perform extra report debugging over and above syntactic errors (i.e., format inconsistencies).

• Reports can go to many different types of destinations, including a printer, email, screen or standard output, Report Previewer, or file.
• Sometimes it is useful to send report output to the Report Previewer. This is accomplished either by running the report in Reports Runtime interactively with the File | Run menu option or from the operating system command line by passing the parameters BATCH=no and DESTYPE=previewer.
• The runtime command for Reports Runtime is usually r30run for Developer/2000 2, and r25run for Developer/2000 1.x.
• Other executable components of Oracle Reports for Developer/2000 2.0 include:

• Report Builder to create new reports using r30des
• Reports Runtime to run compiled reports using r30run, or r30cli when running reports with Reports Server.
• Reports Queue Manager to manage report queuing using r30rqv for Unix, r30rqm32 for Windows.
• Reports Server to run reports from a reports application server, which can schedule report execution, using executable r30mts.
• Reports Web common gateway interface and Oracle Web Application Server to allow distribution of Oracle Reports on the Web, using executables r30cgi and r30ows.
• Report Converter to change report formats from client to server or to compile a report into something Reports Runtime can execute, using executables r30conv or r30con32.
• Report Background Engine to execute reports locally for better performance than using Reports Runtime using executable r30bre or r30isv.

• Executable components for Oracle Reports for Developer/2000 1.x include the ones available for Developer/2000 2.0 with appropriate downgrades on version numbers for Reports 2.5 vs. Reports 3.0.
• Report Builder is the executable used to develop new reports and modify existing ones. It has several components:

• Object Navigator to view report components and navigate report development
• Report Editor to modify layout, template, and content of the report, and also to view what the report will look like when executed (Live Previewer)
• Property Palette to display and modify properties and aspects of the report
• Parameter Form Builder to define certain runtime parameters that affect content display in the report
• Template Editor to modify templates that may be associated with reports
• Program Unit Editor, PL/SQL Interpreter, Stored Program Unit Editor, and Database Trigger Editor.

• Main objects in a report include the following items:

• Data model and layout model to display the data content and layout appearance of the report
• Parameter form to define special characters such as currency indicators for report content
• Report trigger, program units, and attached libraries to associate PL/SQL blocks with the report and define events that may execute them
• You can see what a report will look like while developing it in Report Builder with the use of the Live Previewer.

• You can speed through development of simple reports with the use of Report Wizard. This interface helps with the development of reports by focusing your attention on six report components, corresponding to six interfaces within Report Wizard. Those six interfaces are as follows:

• Style definition interface Identify the style of the report, be it tabular, form-like, mailing label, form letter, group left, group above, matrix, or matrix with group.
• Data definition interface Identify the data from Oracle database that will be included in the report.
• Field definition interface Identify which columns from the data definition will appear on the report.
• Totals definition interface Identify summary items for columns in the report.
• Labels definition interface Identify column names for columns in the report.
• Template definition interface Identify a template for use when printing the report to enhance the report output visually.

• Three methods exist for building report data definition queries. You can write the SQL yourself, import it from SQL file, or build a query with the use of Query Builder.
• Query Builder is a tool that allows you to include tables and columns from the Oracle database and associate them visually by means of a graphical interface. Once you define the tables you want to include and associate them in the manner you wish, Query Builder then assembles the SQL statement for you.
• The SQL group functions that are associated with summary options in Report Wizard include sum( ), avg( ), max( ), min( ), and count( ).
• Once created, you tell Report Wizard to build your report by clicking the Finish button in the Report Wizard interface. The report you created is then displayed in the Live Previewer.
• You can return to Report Wizard to modify your report after the fact by clicking on the Tools | Report Wizard menu item in Report Builder.