0886-0890

Previous Table of Contents Next

Page 886

To build this report, create a new report using File New Report. This creates a new report called Untitled in the Object Navigator. (When you initially enter Reports, a new report module is automatically opened.) After setting the development environment (set the global page width to greater than 20 inches using Tools Properties), open the Data Model Editor using Tools Data Model Editor.

NOTE

The first step in building any report is the definition of the data elements that make up the report. In the case of this report, all data is supplied from a single query. Click the SQL icon (second from top on the left side of the Data Model Editor toolbox) and create the query box on the canvas, as shown in Figure 36.7.

Figure 36.7.
Creating a query box.

Double-click the newly created query box, thereby opening the query definition form, which can be completed as shown in Figure 36.8.

After you click the OK button within the Query Editor, a new box appears in the Data Model Editor, listing all the columns that were generated by the SELECT statement in the query. One data element is defined for each column of the SELECT statement, which is in turn used to create the report columns. Double-click any of the columns to open the column definition form. For any columns that might result in null values, you can define a default for the report. (In this case, set avg_delay and max_delay equal to zero for this report.)

Page 887

Figure 36.8.
Defining the query.

At this point, you can construct the report layout using the default Layout Editor by selecting Tools Default Layout. The first tab in this form displays six report types (click the Tabular report radio button), and the second lets you select which columns should be included in the report. On the second tab, make sure that all columns are selected (shown in inverse color ) and modify the label and widths as shown in Figure 36.9.

Figure 36.9.
Default report layout.

After you click the OK button in the default layout form, the Layout Editor appears with the report shown in WYSIWYG (what-you-see-is-what-you-get) format. Note that the data columns all appear across a line with the appropriate headings above each column. Also note that the entire report is surrounded by two boxes (called frames ). The outermost frame defines the

Page 888

query group belonging to the group in the data model. The inner frame is a special type called a repeating frame (designated with a downward-pointing triangle), which repeats for every record returned by the query.

You can resize and move each element within the report using the mouse. For now, just make sure that all the report columns and labels fit within a standard 8-inch_wide page (allow for margins) by dragging the item's sizing points with the mouse. Techniques for custom-sizing and font selection are discussed later in this chapter in the section titled "Customizing Reports with Boilerplate Text and Graphics."

To complete the report, add the titles at the top of the page. To create the titles, select the margins icon at the top of the Layout Editor page. After you click this button, the area above the report body is visible and you are unable to edit any of the report body. Select Format Fonts from the menu and choose a large font for the report title. Create the title by choosing the text icon from the layout toolbox and typing the report title at the top of the margin. To complete a text item, click the mouse cursor outside the textbox. Similarly, you can create the subtitle using a slightly smaller font. Finally, position the title and subtitle at the center of the margin area and then click the report body icon to close the margin definition.

Run the report by choosing File Run from the menu or by clicking the traffic light icon. You can send the report directly to a printer or send it first to the screen or a file to be printed at a later time. Additionally, you can send the report to another user via mail. You should use
a final destination option, Preview, when the report is most likely going to be printed. When a report is sent to the screen, the window fonts are used for display, but using Preview causes the printer fonts to be used, which might be different. After you are satisfied with the report, you can save it using File Save As.

Creating a Master-Detail Report

The master-detail report is a report that organizes the data according to specific break groups as shown in Figure 36.10. The price list is listed by product within a product class within a product line. Break groups are shown in the Data Model Editor as separate boxes containing the data columns belonging to the individual break group.

Using Secondary Queries

One way to create break groups is to define secondary queries associated with the previous query so that for each record returned for a query, a second data selection is performed to retrieve the related data. To build this report, once again create a new report and set the dimensions for the report using the Report Properties dialog.

Page 889

Figure 36.10.
Master-detail report with breaks.

After the report is created, create three separate query groups (shown in Figure 36.11).

 Query:    Q_product_lines Text:    select pl_id, pl_name            from product_lines           order by pl_sequence Query:    Q_prod_classes Text:    select pc_pl_id, pc_id, pc_name            from product_classes            order by pc_sequence Query:    Q_products Text:    select i.item_pl_id                   PRODLINE_ID,                 i.item_pc_id                   PRODCLASS_ID,                 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 items i,             products p,             color_patterns c,             sizes s 

Page 890

 where 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 

Figure 36.11.
Defining multiple queries.

After the three queries are established as separate groups in the data model, you must link them together to specify the relationship between each query element. To do this, select the data link tool in the Data Model Editor (appears as two overlapping ovals) and move the mouse to the pl_id column in the G_product_line group. Hold down the left mouse button, drag the mouse to the pc_pl_id column in the G_prod_classes group, and release the mouse button. A line appears, connecting the two query groups; at runtime, the select statement for the second query is modified to include the data relationship. Repeat the same process to connect the third query to the second by establishing links between pc_pl_id and PRODLINE_ID and between pc_id and PRODCLASS_ID. The resulting data model is shown in Figure 36.12.

Previous Table of Contents Next