Unit V

Preparing for OCP Exam 5: Building Reports

Chapter 23

Introducing Oracle Reports

Designing and Running Reports *

Style and Structure of Common Business Reports *

Exercises *

Using Runtime Executable to Run Prebuilt Reports *

Character Mode and Bitmap Mode *

Exercises *

Identifying Various Report Destinations *

Exercises *

Viewing Report Output in the Report Previewer *

Exercises *

Report Builder Concepts *

Executable Components of Oracle Reports *

Oracle Reports for Developer/2000 2.0 *

Exercise *

Invoking Report Builder and Its Components *

Invoking Report Builder *

Exercises *

Main Objects in a Report *

Exercises *

Creating Reports with Report Wizard *

Creating Simple Tabular Reports with Report Wizard *

Exercises *

Methods for Building Report Queries *

Importing SQL from File *

Using Query Builder *

Exercises *

Summarizing Report Values *

Exercise *

Modifying Style and Content of Reports *

Exercises *

Other Report Styles Available in Report Wizard *

Form-Like Reports *

Mailing Label Reports *

Form Letter Reports *

Group Left Reports *

Group Above Reports *

Matrix Reports *

Other Types of Reports *

Exercises *

Chapter Summary *

Two-Minute Drill *

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

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.

Designing and Running Reports

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

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.

Style and Structure of Common Business Reports

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.

Exercises

  1. Identify common types of business reports and describe some features of each.
  2. Which type of report is commonly used for SQL*Plus output? What sort of report might be used to display a select statement containing a group by clause?

Using Runtime Executable to Run Prebuilt Reports

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.

Parameter Description
MODULE filename
REPORT filename
The name of the file that is the report to be run, sometimes called a runfile. These files usually carry the .rdf or .rep suffix. REPORT and MODULE are synonymous, though REPORT is provided for backward compatibility.
BATCH y/n Whether Reports Runtime is running in batch. If so, no terminal input or output will be accepted. If not, the interactive window opens.
USERID user/pwd@db Valid user information for use when Reports Runtime accesses the database to create the report.
PARAMFORM y/n Determines whether Reports Runtime shows the runtime parameter form in the Reports Runtime interactive window when you run the report. Not used if BATCH=y.
CMDFILE filename The name of a file containing parameters and values for the execution of Reports Runtime.
TERM termname The type of terminal used to run Reports Runtime. Valid only when the report is run in character mode, on environments such as UNIX.
ARRAYSIZE num Size in K of the Oracle array Reports Runtime will use when executing the report. Usually a larger array helps reports process faster. Valid values for num range between 1 and 9999, default is 10.
DESTYPE type Specifies the destination of the report. Valid values for type include screen, file, printer, preview, sysout, and mail.
DESNAME name Name of a printer, filename, or Oracle Office username 1K in length that is the destination of the report.
DESFORMAT fmt Allows you to specify a printer driver for use when you specify DESTYPE to be a file. Also specifies characteristics of your printer identified by DESNAME.
COPIES num Specifies the number of copies Reports Runtime should produce. Only used if DESTYPE=printer. Valid values for num range between 1 and 9999, default is 1.
CURRENCY char A character symbol Reports Runtime will use to specify currency on a number.
THOUSANDS char A character symbol Reports Runtime uses to separate a number hundreds from thousands, hundred thousands from millions, etc. on a number (i.e., comma, as in 1,000,000 instead of 1000000).
DECIMAL char The character symbol Reports Runtime uses to indicate the decimal place in a number.
READONLY y/n Equivalent to set transaction read only, allows reports containing multiple select statements to have read consistency across them.
LOGFILE filename Name of a log file to which all screen prints of reports in character mode will be saved. Default is dfltrep.log. If the file already exists, its contents are not overwritten. Instead, new screen prints are added at the end.
BUFFERS num Amount of memory used to run your report. Default is 640K. Range is between 1 and 9999.
PROFILE filename The name of a file that stores performance statistics on the report execution. Items included in the profile include total elapsed time, total time Reports Runtime spent running, time spent connecting to Oracle, parsing, and fetching data, SQL processing time, and CPU time.
RUNDEBUG y/n Tells Reports Runtime to perform extra debugging to check for overwrite, layout, mode inconsistencies, or bind variable problems that don’t produce runtime errors but will make the report look bad.
ONSUCCESS action
ONFAILURE action
How Reports Runtime should end its transaction based on the success of failure of the report’s execution. Valid values for action include commit, rollback, and noaction.
ERRFILE filename File to which Reports Runtime writes all errors received this run.
LONGCHUNK Size in K of chunks Reports Runtime retrieves from LONG type columns. Default 10K. Range is between 1 and 9999.
ORIENTATION dir Orientation of report when printed. Valid values for dir include portrait, landscape, or default.
BACKGROUND y/n Specifies whether the report will be run as a background process.
MODE md Specifies whether report runs in bitmap or character mode. Valid values for md include bitmap, character, and default.
PRINTJOB Defines whether the print job dialog interface will appear before the report is run to define printer options. Will not work for background report runs or when DESTYPE=mail.
AUTOCOMMIT y/n Defines whether database changes should be committed automatically.
NONBLOCKSQL y/n Allows or disallows other programs to execute while report is being created.
ROLE name/pwd Specifies a role to be used when running this report.
PAGESIZE num x num Identifies size of report page in inches, centimeters, picas, or characters.

Table 1: Reports Runtime command line parameters

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

Character Mode and Bitmap Mode

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.

Exercises

  1. Name two ways you can run an existing report.
  2. Identify the purpose of the DESTYPE, RUNDEBUG, PROFILE, and ONFAILURE parameters. Which parameter can be used to identify a file containing parameters for the report run? Explore the Reports Runtime interactive session and determine how to change these parameters using the Runtime Options dialog box
  3. Identify some parameters used only for Reports Runtime when running in character mode. Identify some parameters used only for Reports Runtime when running in bitmap mode. Identify a parameter whose usage depends on which mode the report is running under.
  4. Can parameters be used when starting execution of Report Builder? Why or why not?

Identifying Various Report Destinations

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

Exercises

  1. Identify some different report destinations.
  2. What uses are there for sending the report to the file destination?

Viewing Report Output in the Report Previewer

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.

Figure 1: Reports Runtime with report in Report Previewer

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.

TIP: Make sure you understand the DESTYPE, DESNAME, and DESFORMAT parameters before proceeding!

Exercises

  1. How do you bring a report up into the Report Previewer using Reports Runtime interactively? How can you accomplish the same task from the command line?
  2. Can you view a report in the Report Previewer without being connected to the Oracle database? Why?

Report Builder Concepts

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

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.

Executable Components of Oracle Reports

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.

TIP: For Developer/2000 1.6 preparation, go back to Developer/2000 and make a mapping of executables in 2.0 that existed in 1.x, and study that listing. In most cases, the change in the name of the executable will be as simple as substituting "25" for "30".

Oracle Reports for Developer/2000 2.0

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

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.

Exercise

  1. Identify the components of Developer/2000 and describe the uses for each component. Which component handles running existing reports from the operating system command line?
  2. Spend some time exploring each Developer/2000 component for reporting.

Invoking Report Builder and Its Components

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.

Figure 2: Object Navigator module in 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.

Live Previewer button

Data Model button

Layout Model button

Parameter Form button

Figure 3: Report Editor module in Report Builder

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.

TIP: The contents of the Property Palette are referred to as the "property sheet" for the object in question. For the rest of the unit, you will see references to invoking the Property Palette to modify object properties. Memorize that you are making modifications to the property sheet in that situation for OCP!

Figure 4: Property Palette module in 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.

Figure 5: Parameter Form Builder in Report Builder

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.

Figure 6: Template Editor in Report Builder

Invoking 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

TIP: Make sure you understand this point about using parameters for r30run and r30des for the OCP Exam 5!

Exercises

  1. Identify the main use of Report Builder and name and describe its components. What are the different views on a report data available as part of using the Report Editor?
  2. What menu allows you to start the tools and components of Report Builder? How do you start the Object Navigator?
  3. How do you invoke Report Builder? Experiment with invoking Report Builder on your operating system in conjunction with the USERID, CMDFILE, and REPORT parameters.

Main Objects in a Report

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.

Exercises

  1. Identify the purpose of the layout model and data model of a report. Of the two, which model is most central to determining what information from the Oracle database will be included on the report?
  2. In which component of the report can you define the character used to separate the hundreds place from the thousands place in a number?

Creating Reports with Report Wizard

In this section, you will cover the following points on creating reports with 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.

Creating Simple Tabular Reports with Report Wizard

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.

Figure 7: Report Wizard style definition window

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.

Figure 8: Report Wizard data definition window

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.

Figure 9: Report Wizard field definition window

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.

Figure 10: 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.

Figure 11: Report Wizard labels definition 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.

Figure 12: Report Wizard template definition window

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.

Exercises

  1. Identify the components of Report Wizard. When might it be useful to use this tool?
  2. What type of report does this narrative lead you to create?

Methods for Building Report Queries

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.

TIP: You cannot check syntax on your SQL statement without first connecting to the database. If you attempt to check syntax when not logged on, Report Builder prompts you to log on.

Importing SQL from File

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.

Using Query Builder

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.

TIP: To add tables to your query later, click on the Select Data Tables button in the top part of the Query Builder interface shown in Figure 23-14.

Figure 13: Query Builder table selection interface

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.

TIP: The only database objects you can see in the Select Data Tables interface are tables, snapshots, views and synonyms.

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.

Select Data Tables button

Figure 14: Query Builder interface with relationship defined

TIP: Optionally, you can click on the Show SQL button next to the Set Table Relationship button to see the SQL statement before exiting Query Builder.

Exercises

  1. What are the three methods for building report queries?
  2. In order to avoid Cartesian products, what activity should you perform in Query Builder when defining your SQL statement?

Summarizing Report Values

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.

Exercise


  1. Identify the summary functions available in Report Wizard.

Modifying Style and Content of Reports

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.

Exercises

  1. How do you invoke Report Wizard from the Report Builder menu bar? How does it differ from Report Wizard running automatically from the welcome screen at startup?
  2. What are the different ways you can navigate through Report Wizard?
  3. If you make a change to a report using Report Wizard, when will the change take effect?

Other Report Styles Available in Report Wizard

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.

Form-Like Reports

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

Mailing Label Reports

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

Form Letter Reports

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

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

Group Above Reports

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

Matrix Reports

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

Other Types of Reports

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.

Exercises

  1. Which type of report best suits the select statement containing a group by clause?
  2. Which two types of reports are useful for mass mailings?
  3. Explain the content of a matrix report. A group report. A matrix with group report.

Chapter Summary

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.

Two-Minute Drill

Hosted by uCoz