|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Writing Reports, 9 of 12
You can use three key report calculation functions to perform calculations on numeric data that you have already generated in the current report. These functions are summarized in the following table.
Enables you to make calculations that involve data values in the current row. Returns the value already generated in a specified column of the current row of a report. Returns a decimal value.
Calculates subtotals and totals for columns of data generated in previous rows. Returns one of 32 subtotals that are maintained for the current column, and resets that subtotal to zero. Returns a decimal value.
Calculates running totals for columns of data generated in previous rows. Returns the running total for the current column (like SUBTOTAL, except it does not reset the subtotal to zero). Returns a decimal value.
The report calculation functions make it easier and faster to create a report. By using the functions, you can often avoid recalculating expressions that have already been generated in preceding columns.
Tip: In addition to using the three report calculation functions to perform calculations on values in your report, you can use other OLAP DML functions, such as TOTAL and LAG, to access data in your analytic workspace.
You can use the COLVAL function to access a numeric data value in a previous column of the current report row and perform calculations on that value.
As the argument to the COLVAL function, you specify the column number you want to access. The labels column is column 1, and the data columns are numbered from left to right, starting with column 2.
You can also use a negative argument to refer to the position of a column relative to the current column.
To produce a row of output that shows actual dollar sales, budgeted dollar sales, and the ratio of the two, use the following commands.
This ROW command produces the following output.
The following command uses a negative argument to refer to the position of a column relative to the current column.
In the command above, COLVAL(-2) returns the value in the SALES column, and COLVAL(-1) returns the value in the SALES.PLAN column. The command produces the following output.
By using the SUBTOTAL function, you can create a row that shows the total of one or more numeric columns in a report.
Thirty-two subtotals are maintained for each column of numeric data in a report, which means you can include up to 32 levels of totals and subtotals in your report. As the argument to the SUBTOTAL function, you supply the number of the subtotal you want to access.
Each time you use the SUBTOTAL function to access one of the subtotals, the contents of that subtotal are reset to zero.
Suppose your program loops over the PRODUCT dimension to create this report of unit sales.
JAN95 FEB95 MAR95 ========== ========== ========== TENTS 200 203 269 CANOES 347 400 482 RACQUETS 992 1,076 1,114
By using the SUBTOTAL function, you can create a row that shows the total of each numeric column. The SUBTOTAL function returns decimal values, which are shown with two decimal places by default. However, because you are totaling integer data, you will want to format the totals with no decimal places. After the commands that produce the rows of data, execute the following command in your program.
The report looks like the following.
JAN95 FEB95 MAR95 ========== ========== ========== TENTS 200 203 269 CANOES 347 400 482 RACQUETS 992 1,076 1,114 ---------- ---------- ---------- Total 1,539 1,679 1,865 ========== ========== ==========
If your program loops over the DISTRICT dimension as well as over the PRODUCT dimension, then you can show subtotals for the products within each district and then a grand total for all the districts. You can use SUBTOTAL(1) for the total of the products within each district, and SUBTOTAL(2) for the grand total of all districts. The program might contain the following commands.
limit month to 'JAN95' to 'MAR95' limit product to 'TENTS' 'CANOES' 'RACQUETS' limit district to 'BOSTON' 'ATLANTA' heading skip, under '=' across month: c month blank for district do row under '-' valonly district blank for product row product, across month: units row indent 2 'Total', over '-' across month: - d 0 subtotal(1) blank doend row 'Grand Total', over '-' under '=' across month: - d 0 subtotal(2)
This program produces the following report.
JAN95 FEB95 MAR95 ========== ========== ========== BOSTON ------ TENTS 200 203 269 CANOES 347 400 482 RACQUETS 992 1,076 1,114 ---------- ---------- ---------- Total 1,539 1,679 1,865 ATLANTA ------- TENTS 253 276 320 CANOES 260 285 356 RACQUETS 1,037 1,196 1,158 ---------- ---------- ---------- Total 1,550 1,757 1,834 ---------- ---------- ---------- Grand Total 3,089 3,436 3,699 ========== ========== ==========
You can use the RUNTOTAL function to calculate a running total of the values in a column. As the argument to RUNTOTAL, you supply the number of the subtotal you want to access. Unlike SUBTOTAL, the RUNTOTAL function does not reset the subtotal to zero when you access it. You must reset the subtotal yourself when you want to start a new running total. To reset the subtotal, you use the ZEROTOTAL command.
To show cumulative unit sales and dollar sales over several months, you might want to create a report such as the following one.
Month Units Total Dollars Total Units Dollars ------ ------ ---------- ---------- ---------- JAN96 307 307 50,808.96 50,808.96 FEB96 209 516 34,641.59 85,450.55 MAR96 277 793 45,742.21 131,192.76 APR96 372 1,165 61,436.19 192,628.95 MAY96 525 1,690 86,699.67 279,328.62 JUN96 576 2,266 95,120.83 374,449.45
To create the report, use these lines in a program.
limit month to 'JAN96' to 'JUN96' limit product to all limit district to 'BOSTON' row under '-' r <l w 6 'Month', w 6 'Units', - 'Total Units', 'Dollars', 'Total Dollars'> for month row w 6 <month, units>, d 0 units + runtotal(1),- sales, sales + runtotal(1)
When you use a SUBTOTAL or RUNTOTAL function in your program, you should include a ZEROTOTAL command in the initialization section of the program. Including a ZEROTOTAL command will ensure that all the totals are set to zero before you begin your report. Before you start a new running total for a column, you must use the ZEROTOTAL command to reset the subtotal of the column to zero.
If you want to reset all the totals for all the columns to zero, then use the ZEROTOTAL command with no argument.
To reset a particular subtotal in a particular column to zero, specify the subtotal and the column number as arguments to ZEROTOTAL. For example, to set the first subtotal of the second column to zero, use this command.
To reset all the totals in a specific column to zero, specify ALL as the subtotal argument to ZEROTOTAL. For example, if you want to reset all the totals for the second column to zero, then use this command.
In a report, you can show the result returned by any OLAP DML function. To show the result, you can use either of the following approaches:
Suppose you want to show the ratio of the unit sales for each district to the total unit sales for all districts combined. You can use the following expression to make the desired calculation.
If you make the above calculation in a FOR loop for the districts in a report program, then the expression must be recalculated for each district. Your program will run more efficiently when you define a variable to hold the calculated total, and use a FOR loop to report the holding variable. First, define a holding variable.
Then use the following lines in a program to calculate the ratio, expressed as a percentage.
limit month to 'JAN95' limit product to all limit district to all . . . tot = total(units, district) / total(units) * 100 for district row district, d 1 tot
The above commands produce this output.