Page 912
Figure 36.38.
The User Parameter property definition dialog.
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.
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:
Page 914
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 .