Within an OLAP DML program, the ARG function lets you reference arguments passed to a program. The function returns one argument as a text value.
Note:
Typically users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARG function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .The number by position of the argument whose value you want to reference. ARG(1) returns the first argument to the program, ARG(2) returns the second argument, and so forth. When the program is called with fewer than n arguments, ARG returns a null value. ARG also returns a null value when n is zero or negative.
Example 7-20 Assigning Arguments
Suppose you have a program that produces a sales report. You want to be able to produce this report for any two periods of months, so you do not want to limit the month dimension to any particular month in the program. Instead, you use ARG functions in the LIMIT command so that the starting and ending months for the two periods can be supplied as arguments when the program is run.
Notice the UPCASE function preceding the ARG functions. UPCASE allows the arguments to be specified in upper- or lowercase, even though dimension values in the analytic workspace are in uppercase. A prefixed & (ampersand) would have a similar effect because it tells Oracle OLAP to substitute the values of ARG before the LIMIT command is executed -- in this case, a value of the month dimension. However, an & (ampersand) has the disadvantage of preventing compilation of program lines in which it appears, and slower execution results.
DEFINE salesrpt PROGRAM PROGRAM PUSH month product district TRAP ON cleanup LIMIT month TO UPCASE(ARG(1)) TO UPCASE(ARG(2)) LIMIT product TO 'CANOES' LIMIT district TO all REPORT grandtotals DOWN district sales LIMIT month TO UPCASE(ARG(3)) TO UPCASE(ARG(4)) REPORT grandtotals DOWN district sales cleanup: POP month product district END
To run the program, you specify the program name (salesrpt) followed by two sets of months to mark the beginning and the end of the two periods of sales to be reported. Then, when the LIMIT MONTH statements are executed, Oracle OLAP passes the months specified on the command line as return values for ARG(1), ARG(2), ARG(3), and ARG(4) in the LIMIT commands.
salesrpt 'Jan95' 'Mar95' 'Jan96' 'Mar96'
This statement produces the following output.
PRODUCT: Canoes
------------SALES--------------
------------MONTH--------------
DISTRICT Jan95 Feb95 Mar95
---------------------------------------------
Boston 66,013.92 76,083.84 91,748.16
Atlanta 49,462.88 54,209.74 67,764.20
Chicago 45,277.56 50,595.75 63,576.53
Dallas 33,292.32 37,471.29 43,970.59
Denver 45,467.80 51,737.01 58,437.11
Seattle 64,111.50 71,899.23 83,943.86
---------- --------- ---------
303,625.98 341,996.86 409,440.44
========== ========== ==========
PRODUCT: Canoes
------------SALES---------------
------------MONTH---------------
DISTRICT Jan96 Feb96 Mar96
---------------------------------------------
Boston 70,489.44 82,237.68 97,622.28
Atlanta 56,271.40 61,828.33 77,217.62
Chicago 48,661.74 54,424.94 68,815.71
Dallas 35,244.72 40,218.43 46,810.68
Denver 44,456.41 50,623.19 57,013.01
Seattle 67,085.12 74,834.29 87,820.04
---------- --------- ---------
322,208.83 364,166.86 435,299.35
========== ========== ==========