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.
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. |