• 42
• Creating Reports
• Overview of the Report Designer
• The Report Designer Sections
• Building a Basic Report
• Adding Functionality to Reports
• Summary

42

Creating Reports

Within an Oracle Power Objects (OPO) application, reports represent data or graphical screens that can be printed. Reports are designed with the Report Designer, which is extremely similar to the Form Designer with respect to how objects are created on the report. Reports are fairly easy to create, yet they are far more limited than forms in the content that they can contain. This chapter introduces the components of the Report Designer, it demonstrates how to effectively create reports, and it highlights some limitations of building reports.

Overview of the Report Designer

The Report Designer is the graphical interface that helps the developer easily create reports. OPO's Report Designer is identical to the Form Designer with respect to how objects are created on the report, with the exception of the report's window format. The report window is sectioned into horizontal zones which are called bands. This type of report is referred to as a banded report, where each band represents a different section of the report. Banded reports are extremely useful for printing repetitive, columnar data such as records from a table. However, there is little freedom to design reports that are arranged similar to forms or that contain complex relationships.

The Report Designer Sections

When the report designer is invoked by clicking the New Report toolbar button or by selecting File | New Report from the menu, the developer is presented with a control palette, a properties window, and a report window as illustrated in Figure 42.1.

Figure 42.1. The Report Designer.

Notice this looks like the Form Designer, except for the horizontal bands separating the report. Each band corresponds to a different section of the report such as the headers, data, and footers. Objects, such as static text and text fields, are placed in these sections to create listing-type reports of database tables. The various report sections are described below:

Table 42.1. The report sections.

Section

Description

Building a Basic Report

The simplest way to create a basic report is to drop a table or several columns from a table onto the Detail section of a report. This action creates static text and text field objects for each column in the table. Next, select all of the static text objects and move them to the PageHeader section. To get the PageHeader to display on the first page of the report, it is also necessary to set the PageHeader's FirstPgHdr property to True. Additionally, give the report a title by adding a static text to the ReportHeader. Figure 42.2 illustrates this basic report:

Figure 42.2. A basic report.

Once the report has been designed, it can be run by clicking on the Run Report toolbar button, or by selecting the Run | Run Form menu command. When this report runs, it displays a listing of the ENAME, JOB, and SALARY columns from the EMP table. This same report that was created above is shown running in Figure 42.3.

Figure 42.3. Running a basic report.

As you can see, the report runs as expected. The detail section is expanded and each record of the EMP table is displayed. However, the Report Designer has one small error, the ReportHeader is misplaced after the PageHeader.

While the report is running, it can be printed by clicking on the Printer toolbar button. Viewing a report from an application is accomplished by calling the report's OpenPreview() method. Also, the report can be sent straight to the printer by calling its OpenPrint() method. Although these actions enable an application to view or print reports, there are several limitations. When printing a report, there is currently no way to prevent the common printer dialog from being displayed. Thus, reports cannot be automatically printed without user interaction. Additionally, an entire report is restricted to be of the same format—there can be only one detail section for each report. Repeater controls can be used to display a different list of table records. However, repeater controls will not display their data beyond their physical boundaries, and they won' t repeat themselves in order to show all their records. Lastly, only a single report can be invoked at a time which prevents reports from being queued.

Adding Functionality to Reports

Another concept relevant to reports is grouping. A report can partition the detail section into groups by the value of one of its columns. To add a group to a report, select the Report Group control from the control palette and click on the report. Adding a report group adds a GroupHeader and GroupFooter around the report's Detail section. The Detail section is grouped by selecting the GroupHeader and specifying its GroupCol property. Using the previously built report as an example, the GroupCol can be set to DEPTNO, which will cause the detail section to group the employees by their respective departments.

The report will now generate a detail section for each group. For this reason, moving the column labels from the PageHeader into the GroupHeader section allows each grouped Detail section to have its own column headings. Additionally, an identifier for the group can be attached by dropping the grouping column from the table into the GroupHeader. In the example, the detail section could be grouped by the DEPTNO column. Since DEPTNO is an integer, the displayed value may not be meaningful to the user. An additional text field object can be added to the GroupHeader to display the name of the department. This is done by setting the text field's DataType to String, and the DataSource to the following derived value:

=SQLLOOKUP(Òselect dname from dept where deptno = Ò & DEPTNO )

The identifier DEPTNO in the SQLLOOKUP function refers to the name of the text field that was created when the DEPTNO column was dropped onto the GroupHeader.

Another useful concept in designing reports is adding summary information for the Detail section. A text field can be added to the GroupFooter section that will perform an aggregate function over one of the Detail section text fields. Continuing this example, a text field can be added to the GroupFooter to show the sum of the departments' salaries. The text fields DataType should be set to Double, and the DataSource should be set to the following derived value:

=SUM( SAL )

The identifier SAL in the SUM function refers to the name of the salary field in the Detail section.

Other static text objects can be added to label the department name and salary summation text fields. The example of this extended report is illustrated in Figure 42.4.

Figure 42.4. Building an extended report.

Running this report provides a look at what is effectively a master-detail report. The name of each department is displayed along with a list of employees in that department. The running report is shown in Figure 42.5.

Figure 42.5. Running an extended report.

Summary

As a whole, OPO provides an easy way to create efficient application reports through the Report Designer. Basic table listings, master-detail, and cross-tab reports can be built with little effort. More advanced reports that display complex data sets can also be designed by using repeaters and embedded forms. However, these types of objects don' t lend themselves to correctly repeating and displaying all of their data. Reports can also display a bitmap background, dynamically set the value and text of objects through basic code, and display long raw data columns in bitmap controls. Still, reports are limited because the banded-report model doesn' t fully allow the developer to create reports that display information exactly like forms. Other limitations include the following: chart objects don' t function in reports, the common printer dialog box cannot be suppressed, and reports cannot be queued. Reports also display some of the following graphical problems: the ReportHeader is placed after the PageHeader, objects such as text fields always have a border, and text fields don' t always wrap text correctly.

Overall, OPO's report model is extremely useful for creating reports that are simple in content and appearance. Trying to create complex reports, or replicating reports provided by other tools, may be frustrating and beyond the Report Designer's abilities. A possible alternative would be to use a third-party product such as Crystal Reports.