0905-0911

Previous Table of Contents Next

Page 905

Figure 36.27.
Setting field format masks.

Figure 36.28.
Creating a report trigger.

Figure 36.29.

Page-protecting data groups.

Page 906

Finally, to cause a report to execute a page break every time the value changes, use the Object tab of the repeating frame to set the maximum records per page to 1.

Exploring Complex Reports

In addition to the reports previously discussed, Oracle Reports provides the capability to create several special report types. These special reports include a mailing label report, form letter report, and a matrix report.

The Mailing Label Report

The mailing label report (shown in Figure 36.30) is used to print address labels on special paper, which is usually perforated to print labels for multiple address records. (This particular example was printed with a report page size of 8 inches wide and 2.5 inches tall to illustrate the concepts for the report.)

Figure 36.30.
Mailing label report.

To build this report, create a new report with the proper page size for the label paper. In the Data Model Editor, create a simple query from the Customers table:

 select cust_no,           cust_name,           cust_address,           cust_city', `nvl (cust_state, cust_country)                 ` `cust_postal_code   city from customers      order by cust_no 

After the query is created, select the Default Layout tool, select mailing label as the report type, delete all column headings in the Data Selection tab, and then click the OK button. The label is formatted with all the fields within the label. Double-click the repeating frame to display the property sheet shown in Figure 36.31. Make sure that the print direction is selected as Down/Across, and then click the OK button. This causes the labels to be printed along the left side of the page until the bottom of the page; they are continued in the next column from the top, and so on, until the page is filled.

Page 907

Figure 36.31.
A mailing label Repeating Frame property sheet.

The Form Letter Report

The form letter report (see Figure 36.32) applies database data to a specific text format.

To build this report, create a new report using the following data model query:

 select cust_no,           cust_name,           cust_address,           cust_city', `nvl (cust_state, cust_country)                   ` `cust_postal_code city, cust_credit_limit      from customers      where cust_no = 1001 
 

Figure 36.32.
Sample form letter report.

Page 908

After the query is entered, select Form Letter style from the Default Layout tool and click the OK button. The resulting report layout appears in Figure 36.33. The field boxes that appear within the report frame are hidden when the report is run. You can display the values in the fields using the &fieldname lexical parameter embedded in the text of the letter.

Figure 36.33.
The form letter layout.

To create the letter text, select the Text tool and click within the report frame. You can type the actual letter within the box. Any time a report column is needed in the report, you can include it by specifying the layout field name with an ampersand (&) preceding the layout name. For example, the customer number is specified as &F_cust_no.

The Matrix Report

A matrix report appears like a columnar report, except that the column headings are retrieved as part of the database query. Figure 36.34 shows an example of a matrix report in the warehouse shipments report.

Figure 36.34.
A sample matrix report.

Page 909

This report groups the data horizontally, based on the values in the matrix columns . In cases where no data exists (such as the Shreveport warehouse in this example), the column appears as blank unless you specify a default value if the field is null.

To create this report, modify the system parameter ORIENTATION to have a default value of Landscape and define the page as 10.5 inches wide by 8 inches high. In the Data Model Editor, define the following query:

 select w.wh_name                          WAREHOUSE,        h.hist_month_no                    MONTHNO,        to_char (to_date (to_char (h.hist_month_no),                          `MM'),'MON')     RPT_MONTH,      h.hist_ord_shipped                   SHIPS from warehouses w,      warehouse_history h where w.wh_code = h.hist_wh_code     and h.hist_year = 1994 

This query results in four data columns in the G_history group. Separate this group into three distinct groups and position the groups as shown in Figure 36.35. Next, select the Cross Product tool to draw a box around the two matrix groups, as shown, and name the matrix group G_matrix.

NOTE

The month number was included within the query so that the months can appear in calendar order rather than in alphabetical order (which is the default without a sequence column).

Figure 36.35.
A matrix report data model.

Page 910

To complete the data model, create total columns for each month and warehouse as well as a total for the entire report. The report total is created the same way as any total is created outside the data groups ”by clicking in a blank area of the data model canvas. To create the matrix totals, select the Summary Column tool and click inside the title area of the matrix group box. The WH_SUM summary column appears within the matrix box; modify its properties to summarize SHIPS, to reset at the G_history group, and to set the product order at the G_history level as well. The MONTH_SUM should be based on the G_months group.

Finally, to complete the report layout, select Matrix as the style from the Default Layout tool, delete all column headings from the data selection window, and then deselect the month_no column. To cause the month names to appear across the top of the report, change the repeat direction to Across. Click the OK button to generate the default layout, which should look similar to Figure 36.36.

Figure 36.36.
The matrix report layout model.

Creating Dynamic Reports

As a final concept, this chapter describes techniques for producing reports that define their queries based on runtime selections by the operator. You can use this feature through dynamic query parameters for runtime modification of a query with lexical constructs.

Using Dynamic Query Parameters

You can define a user parameter for a report that defines the query. To accomplish this, open the r37oun05.rdf (shipping document with summary columns) report created earlier. Select

Page 911

the User Parameters heading in the Object Navigator and create a new user parameter using the Add Object tool at the left of the Object Navigator window. The new user parameter appears under the User Parameters heading, as shown in Figure 36.37.

Figure 36.37.
User parameter creation.

Double-click the icon to the left of this parameter to display the properties sheet for the parameter. (See Figure 36.38.) Change the name of the parameter to P_order_no and define it as numeric with a maximum width of 15 digits. To ensure that a valid entry is made, create a validation trigger as follows :

 function P_ordernoValidation_Trigger return boolean is    checkval    VARCHAR2(1); begin    select `x'       into checkval       from orders     where order_no = :P_order_no;    return (TRUE); exception    when NO_DATA_FOUND then       srw.message (1001, `A valid order number must be entered.');       return (FALSE); end; 

Modify the query to use :P_order_no instead of the hard-coded value that originally existed. The new report presents a default parameter form at runtime, where the user can enter a valid order number to produce a shipping document. If an invalid order number is entered, Oracle Reports presents an error message.

Previous Table of Contents Next