0891-0896
Page 891
Figure 36.12. Linking related secondary queries.
To create the master-detail report layout, choose Master-Detail in the Default Layout tool and select all the data columns except for the ID values in the report. Click the OK button, and the Layout Editor appears with the initial master-detail report. As in the previous example, two frames are created for each group in the report. Also, note that an extra frame was created, representing the column headings for the G_products group .
As shown in the example, however, the column headings should appear only at the top of each page. To do this, you must move the frame containing the headings to the outermost frame of the report. One of the key features of Reports 2.5 is that the Layout Editor does not let you move an object outside its enclosing frame under default operation. To move the frame out of the G_products enclosing frame, first click the Confine button (which appears as a padlock) at the top of the Layout Editor window. Next, using the Frame Select tool, click the frame containing the headings and drag it out of the report frame altogether. Then you can resize and reposition the remaining objects toward the bottom of the report frame to make room for the column headings at the top of the outermost frame. You can reposition the column headings frame to the top of the report. The final result should look like Figure 36.13.
To complete the report, click the Margin icon and add the title and subtitle . You can run this report as explained in the previous example and then save it in a file or to the database.
Page 892
Figure 36.13. Master-detail report layout.
Adding Report Break Levels
The previous report example illustrates how to connect secondary related queries; however, it is usually preferable to retrieve all data in a single query, especially in a client/server environment.
| NOTE |
In the previous example, suppose that the first query retrieved 10 rows of data in blocks that can hold up to 50 rows of data, and the second and third queries also retrieved the same amount of data in similar blocks. In its most simple form, 111 requests are sent to retrieve 1110 rows of data in 111 blocks. This results in a total of 222 blocks being communicated across the network. Suppose that the same data can be retrieved in a single query that returns 1000 rows in blocks that can hold approximately 16 (one third of 50) rows of data each. The resulting network traffic is a single request with 63 blocks returned with the 1000 rows of data or a total of 64 blocks across the network ”a performance gain of over 70 percent! When this savings is combined with the time required to process each request, the total savings can be quite substantial for a lengthy report. |
To create this report based on a single query, create a new report by first setting up the page and defining a query in the Data Model Editor. The query named Q_products should read as follows :
Page 893
select pl.pl_sequence LINE_SEQ, pl.pl_name LINE_NAME, pc.pc_sequence CLASS_SEQ, pc.pc_name CLASS_NAME, 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 PROD_NAME, c.cp_name PATTERN_NAME, s.size_desc SIZE_DESC, i.item_price_units PRICE_UNITS, i.item_price LIST_PRICE from product_lines pl, product_classes pc, items i, products p, color_patterns c, sizes s where pl.pl_id = pc.pc_pl_id and pc.pc_id = p.prod_pc_id and i.item_pl_id = p.prod_pl_id and i.item_pc_id = p.prod_pc_id and i.item_prod_no = p.prod_no and i.item_cp_no = c.cp_no and i.item_size_code = s.size_code order by p.prod_sequence, i.item_price
When the query is entered and accepted, a box appears in the Data Model Editor showing all the queried columns. Using the mouse, select the CLASS_SEQ column in the column box and then drag it to the right of the column box; release the mouse button to create a new break box, as shown in Figure 36.14.
Figure 36.14.
Creating report breaks.
Page 894
Using the mouse, drag the new box to align it with the original query columns box and then resize it to allow additional columns to be added to the box. Then, drag the other columns (except LINE_SEQ and LINE_NAME) into the new break box. Repeat the process by creating a third break box with CLASS_SEQ and CLASS_NAME remaining in the second break box. When completed, the data model appears as shown in Figure 36.15.
Figure 36.15.
The completed break report data model.
You can then create the report layout with the Default Layout tool, as illustrated in the previous example for secondary queries.
Customizing Reports with Boilerplate Text and Graphics
The reports explored in this chapter so far use the powerful, intelligent default capabilities of Oracle Reports 2.5. Many times, however, it becomes necessary to enhance the report with explanatory text and graphical objects. The Shipping Document report displayed in Figure 36.16 is an example of a report that was customized with special boilerplate text and other graphic objects.
Page 895
Figure 36.16.
Boilerplate text and graphics report.
To build this report, create a new report, and this time define the page size as 8 inches wide by 10.5 inches high. Within the data model, create the query as shown:
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_name WAREHOUSE, oi.oi_qty QTY, 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, i.item_price * oi.oi_qty ITEM_TOTAL
Page 896
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
Create a break at the order item level and separate the data columns as shown in Figure 36.17. At this point, select the Layout Editor from the Tools menu, and the blank canvas is displayed. Because no margin information is needed for this report, select the margins with the Margin iconic button and drag the top and bottom of the report body box to the top and bottom of the page. If you resize the margins, the report body can use the entire printable page for the report.
Figure 36.17.
Defining breaks for the Shipping Document report.
EAN: 2147483647
Pages: 391