The Ratio-to-report function - An easy way to help report readers evaluate values
John Adolph Palinski
The Ratio-to-Report function is one of Oracle's analytic functions. It enables the developer to easily compute a ratio value on each result set row. This value offers another level of meaning to a report. Readers no longer have to evaluate rows and determine their relative weight. This makes the information on the report more meaningful and easier to interpret. For example, suppose you have four sales areas. The following describes the total commissions for each area:
Eastern 24000
Northern 20500
Southern 10750
Western 5050
In viewing the above result set, you can easily determine the largest and smallest values. However, it is a difficult to determine the scale of the value. Is not the following result set more meaningful?
Eastern 24000 40%
Northern 20500 34%
Southern 10750 18%
Western 5050 8%
The second result set has more meaning since it is easier to measure values using a scale (1% to 100%). The first result set is more difficult to evaluate since it contains discrete values (i.e 10750, 5050) without an evaluation measure. The Oracle Ratio-to-report function allows you to easily add a measurement value to your result sets making your reports easier to use.
The Ratio_to_report function is an Oracle windowing function. The following is a template of the function:
ratio_to_report (expression value)
over()
The Ratio_to_report parameter identifies the result set value to be analyzed. The Over() function must follow the Ratio_to_report function. The Over function identifies the calculation window. The Over function can contain parameters such as Partition By, Order By, Rows, Range, Between..And, Unbounded Preceding, Unbounded Following, and Current Row. These parameters determine the calculation window. For example, the following Select statement does not have an Over function parameter. The window consists of the entire result set.
SQL> select sales_area_name, sum(commission),
2 ratio_to_report(sum(commission))
3 over()
4 from salesareas sa, salesmen sm, salesman_commissions sc
5 where sa.sales_area = sm.sales_area(+)
6 and sm.salesman_number = sc.salesman_number
7 group by sales_area_name;
SALES_AREA_NAME SUM(COMMISSION) RATIO_TO_REPORT(SUM(COMMISSION))OVER()
-------------------- --------------- --------------------------------------
EASTERN 24000 .39800995
NORTHERN 20500 .339966833
SOUTHERN 10750 .17827529
WESTERN 5050 .083747927
SQL>
In this second example, the Partition By parameter is added. This Select statement is to show each salesman's commissions as a percentage of the total for a sales area. Thus the parameter is followed by the Salesman_area expression.
SQL> select sales_area_name, last_name, sum(commission),
2 ratio_to_report(sum(commission))
3 over(partition by sales_area_name) ratio
4 from salesareas sa, salesmen sm, salesman_commissions sc
5 where sa.sales_area = sm.sales_area(+)
6 and sm.salesman_number = sc.salesman_number
7 group by sales_area_name, last_name;
SALES_AREA_NAME LAST_NAME SUM(COMMISSION) RATIO
-------------------- -------------------- --------------- ----------
EASTERN HARRISON 1450 .060416667
EASTERN JEFFERSON 500 .020833333
EASTERN LINCOLN 3250 .135416667
EASTERN MADISON 18800 .783333333
NORTHERN BUCHANAN 4200 .204878049
NORTHERN PIERCE 2550 .124390244
NORTHERN PITCHER 750 .036585366
NORTHERN VAN BUREN 4250 .207317073
NORTHERN WASHINGTON 8750 .426829268
SOUTHERN ADAMS 6250 .581395349
SOUTHERN FILLMORE 250 .023255814
SOUTHERN MONROE 4000 .372093023
SOUTHERN TAYLOR 250 .023255814
WESTERN ADAMS 1100 .217821782
WESTERN JACKSON 3200 .633663366
WESTERN POLK 250 .04950495
WESTERN ROSS 250 .04950495
WESTERN TYLER 250 .04950495
18 rows selected.
SQL>
The previous examples should help you understand how to use the Ratio-to-report function. You will find they are extremely easy to use and will make your reports more meaningful.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training. He may be contacted at www.oracle-trainer.com.
