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.
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 (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.
Button |
Usage |
|
Select |
Select data element(s) to be manipulated. |
|
Query |
Define report SQL queries. |
|
Summary Column |
Create column that will be computed based on a column at a lower level. |
|
Placeholder Column |
Create a column that will be calculated in a PL/SQL procedure. |
|
Magnify |
Zoom In/Out in the editor. Click within the editor to zoom in on an object, or click while holding the Shift key to zoom out. |
|
Link Data |
Create a logical link between separate database queries. |
|
Formula Column |
Create a column that will be calculated as the result of a PL/SQL function. |
|
Cross Product |
Define a matrix from two separate query groups. This button is used only for a special report called a matrix report. |
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 (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.
Button |
Usage |
|
Select |
Select the object(s) to be manipulated. |
|
Rotate |
Rotate the object. |
|
Magnify |
Zoom In/Out in the Editor. Click within the editor canvas to zoom in and click while holding the Shift key to zoom out. |
|
Rounded Rectangle |
Draw a rectangle with rounded corners. |
|
Ellipse |
Draw an ellipse or circle. |
|
Polygon |
Draw a multi-sided graphical object. |
|
Freehand |
Draw a freehand object while dragging the mouse. |
|
Frame |
Create a layout frame. |
|
Field |
Create a report field . |
|
Oracle Graphics |
Attach an Oracle Graphics Object to the report. |
|
Anchor |
Attach an object to another so that they will maintain the relative position from each other in the final report. |
|
Additional Default Layout |
Create an additional layout for the report. |
|
Frame Select |
Select all objects within a frame. |
|
Reshape |
Reshape object(s). |
|
Line |
Draw a line. |
|
Rectangle |
Draw a rectangle. |
|
Arc |
Draw a segment of a circle. |
|
Polyline |
Draw a series of connected lines. |
|
Text |
Insert constant text. |
|
Repeating Frame |
Create a repeating frame. |
|
Link File |
Attach an external file to the report. |
|
Button |
Create a button on the report. |
|
OLE2 Object |
Embed an OLE 2.0 object in the report. |
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 only time the confine mode should be turned off is during copy and paste operations or when the designer specifically needs to move an object to another level in the report. Otherwise, objects and frames could
end up overlapping, which will cause indeterminate problems at runtime.
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.
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).
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.
When working with character mode reports, the gap values are not used, resulting in frames and fields appearing to be exactly the same size. As a result, a complex character mode report can be very difficult to
work with.
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.
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.
To facilitate the design of a report that will use the default layout tool, you should specify the initial page width significantly wider than the actual physical size of the page. After the layout elements are
properly resized and placed correctly, the page size may be adjusted to the proper values.
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.
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.
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.
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
left side of Data Model Editor tool box) and create the query box on the canvas, as shown in Figure 33.7.
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.
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.
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.
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.
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 would be sent to retrieve 1110 rows of data in 111 blocks. This results in a total of 222 blocks being communicated across the network. Now, suppose that the same data can be retrieved in a single query that
will return 1000 rows in blocks that can hold approximately 16 (one third of 50) rows of data each. The resulting network traffic would be 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 savings can be quite substantial for a lengthy report.
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.
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.
For some reports, it might be necessary to construct a title area or footer area that is larger than the default 1/2 inch. By resizing the report body, the developer can enlarge or shrink the margins.
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.
One technique that works well (and saves a lot of time and frustration) is to create the lines smaller than needed and then use the Size Objects and Align Objects dialog boxes to adjust the lines properly. When
using the Align Objects tool, the alignment direction determines where the objects are located. For example, if the horizontal alignment is left, the objects will be positioned to align with the left edge of the leftmost object on the canvas, depending on
the container frames and confine mode in operation. Also, the Stack and Distribute options are extremely useful for aligning the objects against each other or for spacing them evenly, respectively.
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.
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.
Button |
Function |
|
Compile |
Checks the syntax of the program unit. |
|
Apply |
Applies any changes to the report. This function has been 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 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.
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.
Other options are Page Break Before, Page Break After, and Keep with Anchoring Object. The first two options are fairly obvious, while the third is not. Multiple items in a report are typically implicitly anchored
in that it is usually implied that an object will maintain 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 may 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.
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.
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 (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 (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.
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.
The month number has been included within the query so that the months may appear in calendar order rather than in alphabetical order (which would be the default without a sequence column).
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.
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.
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.
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:
|
Function |
Data Value | |
|
SUM |
Monthly total orders shipped | |
|
AVG |
Average shipping delay by month | |
|
MAX |
Maximum shipping delay by month |
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.
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:
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.