0901-0904

Previous Table of Contents Next

Page 901

In the previous example, the line total was calculated by the query and returned with the other report data. This value, which is the product of QTY and PRICE, can be calculated by Reports instead. In addition, it is useful for the report to update the inventory levels for each item as it is included in the shipping document. To make this possible, you must modify the query to return the warehouse and item identifiers and to remove the item_total from the query. The resulting query follows :

 select o.order_no                                                ORDER_NO,          c.cust_no                                                 CUST_NO,          c.cust_name                                               CUST_NAME,          c.cust_address                                            ADDRESS,          c.cust_city', `c.cust_state' `c.cust_postal_code  CITY,          o.order_date                                              ORDER_DATE,          o.order_ship_date                                         SHIP_DATE,          wh.wh_code                                                WH_CODE,          wh.wh_name                                                WAREHOUSE,          oi.oi_qty                                                 QTY,          i.item_no                                                 ITEM_NO,          i.item_pl_idi.item_pc_id'-'          ltrim (to_char (i.item_prod_no, `099999'), ` `)'-'          ltrim (to_char (i.item_cp_no, `09'), `  `)'-'          i.item_size_code                                          CATALOG_NO,          p.prod_name'  `          decode (cp.cp_name, `N/A', null, cp.cp_name)'  `          s.size_desc                                               PRODUCT,          i.item_price                                              PRICE     from products p,          sizes s,          color_patterns cp,          items i,          customers c,          warehouses wh,          order_items oi,          orders o    where p.prod_pl_id = i.item_pl_id      and p.prod_pc_id = i.item_pc_id      and p.prod_no = i.item_prod_no      and cp.cp_no = i.item_cp_no      and s.size_code = i.item_size_code      and i.item_no = oi.oi_item_no      and oi.oi_order_no = o.order_no      and wh.wh_code = o.order_wh_code      and c.cust_no = o.order_cust_no      and o.order_no = 1001 

To create the item_total column, open the Data Model Editor, select the Formula Column tool, and then click within the G_order_items group. A column, designated CF_1, is added to the group . Double-click the column name, and the column definition dialog box appears. Change the name of the column to item_total and define it as a numeric column with ten character positions (digits). To set up the formula for this column, click the Edit button to open the PL/SQL Editor. In order to view the entire editor, click the OK button for the column. This editor provides an area to create PL/SQL functions and procedures for the report. For this field, enter the text as it appears in Figure 36.24. Note that references to report columns are accomplished by preceding the column name with a colon (:).

Page 902

Figure 36.24.
Creating a formula column.


The buttons that appear across the top of the editor perform the functions outlined in Table 36.3.

Table 36.3. The PL/SQL Editor buttons.

Button
Function
Compile Checks the syntax of the program unit.
Apply Applies any changes to the report. This function was included
for compatibility with Reports 2.0.
Revert Restores the program unit to its contents at the time of the last
compile or apply function.
New Creates a new program unit.
Delete Deletes this program unit.
Close Closes the PL/SQL Editor window.

The next field needed for this report is the subtotal , which is calculated as the sum of all the item totals. To create this column, use the Summary Column tool and click within the G_orders group. Double-click this new column to display the Summary Column dialog box, shown in Figure 36.25. Modify the name to order_total and choose the sum function (other functions include avg, min, max, and so on); then select item_total as the data source and reset at the G_orders group.

The tax is computed in a formula column as 0.07 * order_total and the invoice_amount is also calculated as order_total + tax + 5. (The shipping cost is assumed to be $5.00 for all orders.) To complete the report, open the Layout Editor, double-click the F_item_total field, and assign it to the new item_total data source. Create a frame at the lower-right corner of the page to contain the summary fields, and then create the appropriate boilerplate text and fields as needed. (For the format mask for each of these fields, enter 990.00. This is explained in the following section.)

Page 903

Figure 36.25.
Summary Column dialog.


To work with existing program units for a report, expand the program units category in the Object Navigator. To edit any one of these program units, double-click the page icon to the left of each program unit name.

Formatting Reports

The reports discussed up to this point display the data exactly as it appears in the database. Typically, however, it is necessary to apply special formatting to the individual fields or frames within a report. You can use this type of formatting to change how a field appears or whether the field or frame appears at all. Also, you might want to prevent partial report groups from being split on two pages or a report to break automatically whenever a value changes. Figure 36.26 illustrates an example of how to apply special formatting to an existing report.

To explore the techniques discussed in this section, open the report r37oun03.rdf, built during the discussion of creating break groups. The first formatting concept illustrates how to define the displayed format for a field value (this was briefly mentioned in the last section) and is shown in Figure 36.27. Double-click the LIST_PRICE field in the Layout Editor to present this box. Enter 9,990.00 in the Format Mask field. This causes the number to be displayed with all leading zeroes suppressed up to and including tens. (For example, the number 3000 displays as 3,000.00, whereas the value .63 displays as 0.63.) You can apply many different masks to a field; find them by selecting Help within Reports and doing a search on format mask.

To suppress a field from displaying, you can create a format trigger for the field, as illustrated in Figure 36.28. Access this button using the General Layout tab in the Layout Field property sheet (double-click the field in Layout Editor). The illustrated example suppresses the pattern name if the value is equal to `N/A'. The text to suppress this display follows:

Page 904

 function F_PATTERN_NAMEFormat_Trigger return boolean is begin    if :PATTERN_NAME = `N/A' then       return (FALSE);    else       return (TRUE);    end if; end; 

Figure 36.26.
Report formatting techniques.


Finally, to prevent the product class groups from being split between two pages, double-click the R_prod_class repeating frame to display the property sheet, and then select the General Layout tab. (See Figure 36.29.) Select the Page Protect checkbox and click the OK button to cause the entire group to print on a single page.

NOTE
Other options are Page Break Before, Page Break After, and Keep with Anchoring Object. The first two options are fairly obvious, but the third is not. Multiple items in a report are typically implicitly anchored in that it is usually implied that an object maintains the same relative position from its surrounding objects. When there are many objects nearby, Reports "implies" the position of any object using an arbitrary algorithm (which can cause any object to print improperly). To secure the position of an object, use the Anchor tool to connect a child (dependent) object to its parent (fixed) object.
Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net