• 33
• Oracle Reports 2.5
• The Oracle Reports 2.5 Environment
• The Data Model Editor
• The Layout Editor
• Initializing the Reports Environment
• Creating a report
• Constructing a Tabular Report
• Creating a Master-Detail Report
• Using Secondary Queries
• Adding Report Break Levels
• Customizing Reports with Boilerplate Text and Graphics
• Working with Summary and Formula Columns
• Formatting Reports
• Exploring Complex Reports
• The Mailing Label Report
• The Form Letter Report
• The Matrix Report
• Creating Dynamic Reports
• Using Dynamic Query Parameters
• Defining Runtime Queries with Lexical Parameters
• Ten Top Tips for Oracle Reports 2.5
• Summary

33

Oracle Reports 2.5

For many years, the predictors of the future have envisioned the concept of the paperless office wherein all correspondence and corporate records are stored online and all paper reporting has been eliminated. Reality, however, has shown that hard-copy reports continue to be required at an ever-increasing rate.

Oracle Reports is the Developer/2000 tool that has been provided to produce reports of data in the Oracle database. These reports can be previewed on the user screen before being printed or can be printed directly. Optionally, report output may also be saved in a file to be used at a later date.

This chapter explains how to set up the Oracle Reports environment and the methodology for constructing several reports. These reports will cover all aspects of report development using Oracle Reports 2.5. Finally, this chapter summarizes a set of key tips to follow when creating a report.

The Oracle Reports 2.5 Environment

Oracle Reports operates in a Graphical User Interface (GUI) environment such as Microsoft Windows. Functions may be performed by clicking iconic buttons or via menu picks. The menus used by Reports dynamically change based on the current context of the tool and are fairly intuitive as to their specific purpose. The individual menu items will be explained as needed throughout this chapter. The Oracle Reports Designer interface consists of three primary components or tools: The Object Navigator (described in Chapter 32), The Data Model Editor and The Layout Editor.

The Data Model Editor

The Data Model Editor (shown in Figure 33.1) is used to define all of the data elements that are to be included in the report. These data elements are queries, links, and miscellaneous columns that are organized into sets called groups. The Data Model Editor is operated using a set of iconic buttons that can be selected with the mouse.

Figure 33.1. The Data Model Editor.

On the left side of the window is a set of eight buttons used to create and modify the data elements. These buttons (top to bottom, left to right) are described in Table 33.1.

Table 33.1. Data Model Editor buttons.

Button

Usage

The buttons at the top of the editor window are used to perform functions that may also be found as functions in the menu. The first button is used to open an already existing report, and the next is used to save the current report. The next two buttons run the report (the user defines the output destination) and print the report directly to the default printer. The next button clears the selected objects from the editor canvas. The sixth button presents a dialog that is used to create the default layout based on the structure of the data model. Finally, the last button invokes the context-sensitive help facility within Oracle Reports.

The Layout Editor

The Layout Editor (shown in Figure 33.2) is used to construct the format for the report. The editor presents each of the layout elements exactly as it will appear on the final report. Like the Data Model Editor, the Layout Editor is controlled through a set of iconic buttons as well as from the menus. The buttons along the left side of the editor window are used to manipulate the layout objects. These buttons (top to bottom, left to right) are described in Table 33.2.

Figure 33.2. The Layout Editor.

Table 33.2. Layout Editor buttons.

Button

Usage

Below the editor buttons is a square (with the letter T in the center of it) that displays the attribute characteristics of any objects on the Layout Editor canvas. This display indicates the fill color, outside line color, and the text color of the object—and can be changed with the palette selector buttons that appear directly below it.

At the top of the Layout Editor window is another series of iconic buttons for report control. The first four buttons (which are the same as the Data Model Editor) are Open File, Save File, Run Report, and Print Report. The next three buttons perform the standard Windows functions of cut, copy, and paste, whereby objects can be moved between the report and the clipboard. Next, the Default Layout button presents a dialog for the designer to choose the data model columns to be included in the report, and based on the user-defined options and the report type selected, the default report will be created.

The next four buttons are used to select one of the four parts of the report that are to be edited. These parts in order are the body, margin, header, and footer. The body of the report is usually the main report and is the only essential part of the report that must be created. The margin is used to define items that should appear at the top or bottom of every report body page. The header is a page or set of pages that precede the report body, and the footer follows the report body. The header and footer are often used to present a summary of the information contained in the report body. Another use of these elements is to present the conditions that were used to define the report queries or to provide a banner page.

The next button in this window is used to toggle the Confine mode in the Layout Editor. By default, all objects within a frame cannot be moved outside of the parent frame unless the confine mode is off.

The next button is used to turn the Flex mode on and off. With the flex mode on, whenever a frame is resized, all child objects within the frame will be resized accordingly. The last button is used to access the context-sensitive online help system.

Initializing the Reports Environment

Before you develop any reports, the development environment should be set up based on the individual preferences as well as any standards defined by the programming organization. Use the menu pick Tools | Options to present the Tools Options dialog Box, as shown in Figure 33.3. This dialog box consists of three separate tabs, Design Preferences, Runtime Parameters, and Runtime Settings).

Figure 33.3. Tools options.

The preferences tab is used to set the specific user preferences for the developer. These options define the parameters used by the reports designer. The first checkbox is used to disable PL/SQL compilation. Typically, Oracle Reports compiles each PL/SQL program unit it is closed. When you disable compilation, all program units will be compiled when the report is generated, thereby improving the performance of the tool while the report is being designed.

The next option, Suppress Define Property Sheets, is used to instruct the reports designer whether a property sheet should be automatically opened whenever an object is created. When this box is checked, the object will be created with a default name and the user must double-click the object to update the property sheet.

Next, the Suppress List Retrieval on Dialog Box Entry checkbox, prevents a list from being displayed whenever a database retrieval dialog box is selected. When suppressing the object list, the designer must know the exact name of the database object to be retrieved. This can be used as a security mechanism to prevent unauthorized access to a user's objects.

Next, a listbox is used to define the Unit Of Measurement to be used in the Layout Editor. The individual options are centimeter, inch, and point. The unit of measure is used to define the default sizes of the objects on the Layout Editor screen.

The Color Palette Mode is used to define how color palettes should be handled in Oracle Reports. The options for the list box are Editable, Read Only - Shared, and Read Only - Private. Editable means that the color palette of the active report will replace the system color palette causing the active report to be shown accurately while the appearance of any inactive reports may not be accurate. Read Only - Shared means that each report's color palette will be appended to the system palette until the space reserved for the palette becomes full. If any reports are then opened that use a different color palette, they might not appear accurately. Finally, Read Only - Private operates the same as shared mode except that Reports assures that the palette used is always valid for the active report and any inactive reports might not appear correctly because their color palettes have been cleared to make room for the new report. For the most part, these options are of minimal concern because reports will be printed typically in black and white or in a set of very basic colors.

The Object Access block of this form defines where report program modules will be stored. The storage may be defined as File, Database, or File/Database, and the tool may be used to access reports, PL/SQL Libraries, and/or queries.

The preferences dialog may also be used to set up format masks that can be used to display data in the report. These format masks will then be available to the developer in the Layout Editor. The format for a field in the layout may also be defined at runtime; therefore, it is usually not necessary to define them here.

The last part of the preferences box is the definition of the default layout parameters. The first two, horizontal gap and vertical gap, define the space between layout frames and displayed fields as defined by the value and the selected unit of measurement for the report. The most difficult aspect of working with Reports is the manipulation of frames and making sure that all elements are enclosed by the frames where they belong. A novice might want to use a relatively large gap value, whereas a more experienced user may use a smaller value.

The horizontal and vertical interfield values are used to define the amount of whitespace that should appear between fields on a report. Setting these values to zero will cause the fields to be strung together with no whitespace between. Finally, these preferences can be saved by clicking the Save Preferences button next to the Format Mask edit button.

Creating a report

To create a new report, either select File | New | Report from the menu or type Ctrl+E. This will create a report named "Untitled" in the Object Navigator. After the report is created, its properties should be defined using the Tools | Properties menu selection. The Report Properties Dialog Box (shown in Figure 33.4) defines the dimensions for the report. The first selection in this form is for the unit of measure for the report. Valid values are inch, centimeter, or point and represent the coordinate system to be used for the report. Next, the page height and width are defined based on the actual printable size of a page for the printer (typically 8 inches by 10.5 inches for most laser printers). The total size of the report is determined by multiplying these values by the logical page size of the report. In other words, if the report physical page size is 8 inches by 10.5 inches and the logical page size is 3 by 2 (as shown in Figure 33.5), the total logical page size would be 24 inches wide by 21 inches high.

Figure 33.4. Report Properties dialog box.

Figure 33.5. Logical pages versus physical pages.

The remaining parameters for this form should be modified for only very special cases. The maximum pages parameters are used to define the maximum sizes of a logical page in the report. Next the panel print order is used for defining the order that multiple pages that define a logical page will be printed. A value of Across/Down would cause the physical pages in Figure 34.5 to be printed in the order 1, 2, 3, 4, 5, 6, whereas Down/Across would print in the order 1, 4, 2, 5, 3, 6. The final parameter in this form defines the direction that the layout objects are to be placed on the layout. By selecting Default, the objects will be added to the canvas according to the default direction of the NLS language being used by the developer. Other options may be selected as Left to Right or Right to Left.

Constructing a Tabular Report

The first and most basic report that can be developed using Reports is the tabular report. As shown in the example in Figure 33.6, the tabular report appears as a columnar listing of the selected rows. This report lists all of the warehouses for Down East Tackle with statistical information regarding shipments during the year 1995.

Figure 33.6. Tabular report.

To build this report, create a new report using the menu pick File | New | Report. This will create 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 > 20 inches using Tools | Properties), open the Data Model Editor using the Tools | Data Model Editor menu pick.

Figure 33.7. Creating a query box.

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

Figure 33.8. Defining the query.

After you click the OK button within the Query Editor, a new box will appear in the Data Model Editor listing all of the columns that were generated by the select statement in the query. One data element will be defined for each column of the select statement, which will in turn be 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, a default may be defined for the report (In this case, set avg_delay and max_delay equal to zero for this report.)

At this point, the report layout may be constructed using the default Layout Editor using the Tools | Default Layout menu pick. The first tab in this form displays six report types (click the Tabular report radio button), and the second is used to 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 33.9.

Figure 33.9. Default Report layout.

After you click the OK button in the default layout form, the Layout Editor will appear 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 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 will repeat for every record returned by the query.

Each element can be resized and moved within the report using the mouse. For now, just make sure that all of 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.

To complete the report, add the titles at the top of the page. In order 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 will be visible and you will be 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 tool box and typing the report title at the top of the margin. To complete entry of a text item, click the mouse cursor outside of the textbox. Similarly, the subtitle may be created 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. The report may be executed directly to a printer, or be first sent to the screen or a file to be printed at a later time. Additionally, the report may be sent to another user via mail. A final destination option, Preview, should be used when the report is most likely going to be printed. This is because when a report is sent to the screen, the windows fonts are used for display, but using preview causes the printer fonts to be used, which may be different. After you satisfied with the report, you can save it using the File | Save as menu pick.

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

Figure 33.10. Master-Detail Report with breaks.

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

After the report has been created, create three separate query groups (shown in Figure 33.11).

Figure 33.11. Defining multiple queries.

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

                    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

After the three queries are established as separate groups in the data model, they must be linked 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 the left mouse button down, drag the mouse to the pc_pl_id column in the G_prod_classes group, and release the mouse button. A line will now appear connecting the two query groups; at runtime, the select statement for the second query will be 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 now shown in Figure 33.12.

Figure 33.12. Linking related secondary queries.

To create the Master-Detail Report Layout, choose Master-Detail in the Default Layout tool and select all data columns except for the ID values in the report. Click the OK button and the Layout Editor will appear 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 has been 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, the frame containing the headings must be moved to the outermost frame of the report. One of the key features of Reports 2.5 is that the Layout Editor does not allow the designer to move an object outside of its enclosing frame under default operation. To move the frame out of the G_products enclosing frame, first click on the Confine Button (appears as a padlock) at the top of the Layout Editor window. Now, using the Frame Select tool, click on the frame containing the headings and drag it out of the report frame altogether. Now, you can resize and reposition the remaining objects towards the bottom of the report frame to make room for the column headings at the top of the outermost frame. Now, the column headings frame may be repositioned to the top of the report. The final result should look like that shown in Figure 33.13.

Figure 33.13. Master-Detail report layout.

To complete the report, click the Margin icon and add the title and subtitle. This report can now be run as explained in the previous example and then saved in a file or to the database.

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.

To create this report based on a single query, again 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 this:

select pl.pl_sequence                                        LINE_SEQ,

           pl.pl_name                                            LINE_NAME,

           pc.pc_sequence                                        CLASS_SEQ,

           pc.pc_name                                            CLASS_NAME,

           i.item_pl_id||i.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 will appear in the Data Model Editor showing all of 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; then release the mouse button to create a new break box, as shown in Figure 33.14.

Figure 33.14. Creating report breaks.

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 will appear as shown in Figure 33.15.

Figure 33.15. The completed break report data model.

The report layout can then be created with the default layout tool, as illustrated in the previous example for secondary queries.

Customizing Reports with Boilerplate Text and Graphics

The reports that have been explored in this chapter thus far utilize 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 33.16 is an example of a report that has been customize with special boilerplate text and other graphical objects.

Figure 34.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 here:

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

  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 33.17. At this point, select the Layout Editor from the Tools menu, and the blank canvas will be 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, respectively. By resizing the margins, the report body can use the entire printable page for the report.

Figure 33.17. Defining Breaks for Shipping Document Report.

Once the report body has been resized, the report is laid out manually. Select the Frame tool and create a frame that is nearly the same size as the entire report body. Within this frame, create a repeating frame that is about the same size as the first frame. Double-click in the repeating frame to set its properties using the dialog box shown in Figure 33.18. Change the name of the frame to R_orders and set the source for the frame to G_orders. Also, make sure that only one order will be printed on a page by setting the maximum records per page to 1.

Figure 33.18. The Repeating Frame property sheet.

Next, the document title should be created at the top of the report. First, select the font to be used for the title bar using the Tools | Font menu option. Using the Font dialog box, shown in Figure 33.19, select the Arial font with the style Bold Italic and size 36 points. (Note that when a font is selected, a sample of the text appears within the dialog box, showing how the font will look.) When the font is selected, press the OK button to select the font.

Figure 33.19. The Font dialog box.

Using the Text tool, click within the Layout Editor canvas and type Shipping Document in the box that appears. After the text has been entered, click outside the textbox to complete the entry. Modify the attributes of this boilerplate text by choosing a black fill with white text. Resize the text by holding down the Shift key while clicking the mouse within the R_orders frame and then selecting the Tools | Size Objects menu option to display the dialog box. As shown in Figure 33.20, select the Largest radio button under Width to adjust the size of the text to match the enclosing frame.

Figure 33.20. The Size Objects dialog box.

At this point, position the title bar by selecting the Tools | Align Objects menu option. Select to align these objects to each other as well as to align them by the horizontal center, as shown in Figure 33.21.

Figure 33.21. The Align Objects dialog box.

To add the company logo, choose the Link File tool and drag a box at the appropriate position on the report. An x will appear on the canvas where the object is created. Double-click this object to present the External Boilerplate Properties dialog box (see Figure 33.21). Change the name of the object to B_logo and select Image for the object format; then enter the filename for the logo image (in this case, downeast.tif). Click the OK button, and the graphics image appears on the report canvas.

Figure 33.22. Linking an external graphics image file.

The company name and address header should then be added to the report with the Text tool and positioned next to the logo. For this example, the company name is created with a different font than the address, and the two objects can be aligned with the Align Objects tool. This heading is then joined as a single group by typing Ctrl+G or using the Arrange | Group menu option.

Next, lay out the remaining report sections by creating three frames on the report canvas as containers for the remaining report objects. These frames correspond to the boxes that appear on the report and serve as containers for the enclosed information. Therefore, the default properties for the frames do not need to be modified in any way. To create the boxes, select the Rounded Rectangle tool and drag a rectangle within the appropriate frame. When the mouse button is released, the object appears as a rectangle with rounded corners. Select the fill palette to fill the rectangle with black. Copy the rectangle to the Clipboard with Ctrl+C (or you can use the Edit | Copy menu option or the iconic button) and then paste it back to the canvas with the Ctrl+V key combination. Using the mouse, drag the new box above and to the left of the previous box and change the box's fill pattern to white (or gray for the order data). The object now appears as a rounded box with a drop shadow.

To draw the remaining graphical lines, select the Line tool and draw horizontal and vertical lines as needed. Make sure that the lines are exactly the same size as the boxes and that they are properly aligned with the edge of their respective boxes.

The remaining heading text and field prompts can then be added using the Text tool in the boxes as needed. Use the exhibit as a guideline to complete the boilerplate text.

The form layout is now complete, and the query data may be added to the form to finish the report. So far, all of the information that has been placed in the report is contained within the R_orders repeating frame, which is associated with the G_orders data block. Therefore, the data for this block can be added directly to the report using the Field tool. In the Order Data box at the bottom of the report, drag a box that will hold the order number field, ORDER_NO. This box will be created with an F_1 appearing within the box. Double-click this field to display the Object Properties dialog box for this object. As shown in Figure 33.23, change the name of the object to F_order_no and select ORDER_NO as the data source for this column.

Figure 33.23. The Layout Field Properties dialog box.

To complete the report data for this block, create fields for order_date, ship_date, and warehouse in the Order Data box, and create fields for cust_no, cust_name, address, and city in the Ship To box.

To associate data with a record group, a repeating frame must be created and associated with its appropriate data group. Unfortunately, because no boilerplate objects may intersect with a frame that does not entirely close it, five separate repeating frames need to be created within the Items box so that they do not intersect the separating lines. These frames, which should be associated with the G_order_items group, should be the same size and aligned at the top. Within each of these frames, create the appropriate layout field and set its properties as needed.

This completes the shipping document report, which may be saved as needed.

Working with Summary and Formula Columns

So far this chapter has only explored the display and manipulation of data retrieved directly as the result of a query. One of the most powerful features of Reports 2.5 is its ability to provide client-side computations to deliver added functionality to the reporting environment. An example of a report that includes these types of computations is shown in Figure 33.24.

Exhibit 33.24. A report with computational fields.

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 would be useful for the report to update the inventory levels for each item as it is included in the shipping document. To make this possible, the query must be modified to return the warehouse and item identifiers and to remove the item_total from the query. The resulting query is shown here:

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_id||i.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 10 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 33.25. Note that references to report columns are accomplished by preceding the column name with a colon (:).

Figure 33.25. Creating a formula column.

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

Table 33.3. The PL/SQL Editor buttons.

Button

Function

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

Figure 33.26. Summary Column dialog box.

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

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

Figure 33.27. Displaying existing program units.

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. This type of formatting can be used to change how a field appears or whether the field or frame appears at all. Also, there may be a desire to prevent partial report groups from being split on two pages or for a report to break automatically whenever a value changes. Figure 33.28 illustrates an example of how to apply special formatting to an existing report.

Figure 33.28. Report formatting techniques.

To explore the techniques discussed in this section, open the report r34oun03.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 33.29. 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 will be displayed as 3,000.00, while the value .63 will display as 0.63). There are many different masks that can be applied to a field, and these can be found by selecting Help from within Reports and doing a search on format mask.

Figure 33.29. Setting field format masks.

To suppress a field from displaying, the developer can create a format trigger for the field, as illustrated in Figure 33.30. This button can be accessed using the General Layout tab in the layout field property sheet (double-click 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 is shown here:

     function F_PATTERN_NAMEFormat_Trigger return boolean is

     begin

        if :PATTERN_NAME = 'N/A' then

           return (FALSE);

        else

           return (TRUE);

        end if;

     end;

Figure 33.30. Creating a report trigger.

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 33.31). Select the Page Protect check box and click the OK button to cause the entire group to print on a single page.

Figure 33.31. Page-protecting data groups.

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 Figures 33.6) 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 33.32. 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 as shown here:

     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 has been 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 will be formatted with all the fields within the label. Double-click the repeating frame to display the property sheet shown in Figure 33.33. 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 will be continued in the next column from the top, and so on, until the page is filled.

Figure 33.33. A mailing label Repeating Frame property sheet.

The Form Letter Report

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

Figure 33.34. Sample form letter report.

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

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

Figure 33.35. The form letter layout.

To create the letter text, select the Text tool and click within the report frame. The actual letter can then be typed within the box. Any time a report column is needed in the report, it may be included 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. An example of a matrix report is shown in the warehouse shipments report in Figure 33.36.

Figure 33.36. A sample matrix report.

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 will appear as blank unless the developer specifies 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 33.37. Next, select the Cross-Product tool to draw a box around the two matrix groups, as shown, and name the matrix group G_matrix.

Figure 33.37. A matrix report data model.

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 will appear within the matrix box; its properties should be modified 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 will look similar to the one shown Figure 33.38.

Figure 33.38. The matrix report layout model.

Creating Dynamic Reports

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

Using Dynamic Query Parameters

The developer can define a user parameter for a report that defines the query. To accomplish this, open the r34oun05.rdf (shipping document with summary columns) report that was created earlier. Select 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 will appear under the User Parameters heading, as shown in Figure 33.39.

Figure 33.39. User parameter creation.

Double-click the icon to the left of this parameter to display the properties sheet for the parameter (see Figure 33.40). 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 has been 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;

Figure 33.40. The User Parameter Property Definition dialog box.

The query should then be modified to use :P_order_no instead of the hard-coded value that originally existed. The new report will present a default parameter form at runtime, where the user may enter a valid order number to produce a shipping document. If an invalid order number is entered, Oracle Reports presents the error message shown in Figure 33.41.

Figure 33.41. The Validation Error message box.

Defining Runtime Queries with Lexical Parameters

The report query can be modified at runtime using lexical parameters. A lexical parameter is a placeholder column containing the actual text to be used in a query. To illustrate this concept, open the matrix report that was built earlier. For this report, create a parameter for the report year and a parameter for the user to enter one of the following values, depending on the data preference:

Also, create the appropriate validation triggers for the individual parameters.

Next, create a placeholder column at the report level called SELECTION_DATA. Set this field as a character field with a width of 100 characters and assign a default value of h.hist_ord_shipped. Next, create a before report trigger as follows:

     function Before_Report_Trigger return boolean is

     begin

        if :P_REPORT_TYPE = 'SUM' then

           :SELECTION_CRITERIA := 'h.hist_ord_shipped';

        elsif :P_REPORT_TYPE = 'AVG' then

           :SELECTION_CRITERIA := 'h.hist_ship_days / h.hist_ord_shipped';

        else

           :SELECTION_CRITERIA := 'h.hist_max_days';

        end if;

     end;

Finally, the query should be modified as this:

     select w.wh_name             WAREHOUSE,

          h.hist_month_no    MONTHNO,

          to_char (to_date (to_char (h.hist_month_no),

                              'MM'),'MON') RPT_MONTH,

          &SELECTION_CRITERIA

from warehouses w,

          warehouse_history h

     where w.wh_code = h.hist_wh_code

         and h.hist_year = :P_year

The lexical parameter is referenced in the query using an ampersand before the parameter name. Lexical parameters within a query substitute the text stored in the parameter directly into the query. For this reason, when using a lexical parameter, a default value must be entered for NULL values to assist with compilation in the designer.

Ten Top Tips for Oracle Reports 2.5

Oracle Reports 2.5 is a powerful tool that can be used to generate useful reports against Oracle databases. While there are no hard and fast rules regarding how the tool should be used, experience has borne out a number of useful tips that make a developer's utilization of this tool much easier:

• Attempt to layout the report on paper. This will assist with the development of the data model as well as the final layout. Understand where subtotals should be provided to create the data breaks up front.
  
  
• When the default layout is used, define the page size to be excessively wide. The data columns can then be resized and repositioned to fit within the printable page.
  
  
• If possible, formulate the data retrieval in a single query. Experience has shown that a single, somewhat inefficient query can perform better than several dependent well-tuned queries.
  
  
• Complete the data model before attempting to finalize the layout. The addition of a single column in a query might necessitate a redesign of the layout and thus a misuse of time.
  
  
• When adding an additional break level to an existing report, 90 percent of the time it is faster to redo the default layout. Adding another intermediate level frame is one of the most difficult tasks to be done. It can be done, but is often not worth the effort.
  
  
• Rather than trying to resize or reposition objects in the Layout Editor, use the Size Objects and Align Objects tools. Several columns can be selected at once and all made the same custom size quickly, and they can be aligned and spaced with minimal effort.
  
  
• To lock the relative position of multiple objects, select them and create a group to join them together.
  
  
• Use the Magnify tool to zoom in to view the relative positions of the objects or to zoom out to view the total report structure.
  
  
• When you make a mistake in the editor, use the Edit | Undo menu option to reverse the action rather than trying to correct it with the mouse.
  
  
• Before running any report, save it in a file to make sure that it can be recovered. Also, save different versions to facilitate recovery.
  
  

Summary

This chapter explored several techniques for developing Oracle Reports. Due to the limitations of space available, some of the simpler features might have been glossed over. Also, as with any powerful application development tool, there are many ways to produce the same result. Hopefully enough material has been presented to give you a fairly extensive understanding of this product.

It has been my experience that the only way to truly learn a tool such as this is through repetitive practice and experimentation. I encourage you to expand on this material to gain the expertise necessary to use this tool knowledgeably and efficiently.