0912-0914

Previous Table of Contents Next

Page 912

Figure 36.38.
The User Parameter property definition dialog.

Defining Runtime Queries with Lexical Parameters

You can modify the report query 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 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; 

Page 913

Finally, modify the 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,           &SELECTION_CRITERIA from warehouses w,           warehouse_history h      where w.wh_code = h.hist_wh_code          and h.hist_year = :P_year 

You reference the lexical parameter 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, you must enter a default value for NULL values to assist with compilation in the designer.

Ten Tips for Oracle Reports 2.5

Oracle Reports 2.5 is a powerful tool that you can use to generate useful reports against Oracle databases. Although there are no hard and fast rules regarding how you should use the tool, experience has borne a number of tips that make use of this tool much easier:

• Attempt to lay out the report on paper. This assists 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. You can then resize and reposition the data columns 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 try to resize or reposition objects in the Layout Editor, use the Size Objects and Align Objects tools. You can select several columns at once, quickly make them all the same custom size, and then align and space them 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 Edit Undo to reverse the action rather than try 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.

Page 914

Summary

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

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

Previous Table of Contents Next