Performing Calculations in a Report

Key report calculation functions

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.

Function	Description
COLVAL	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.
SUBTOTAL	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.
RUNTOTAL	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.

Performing row calculations

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.

Example: Calculating the ratio of two columns

To produce a row of output that shows actual dollar sales, budgeted dollar sales, and the ratio of the two, use the following commands.

allstat
row product, sales, sales.plan, colval(2)/colval(3)

This ROW command produces the following output.

TENTS           32,153.52  42,346.89       0.76

Example: Referring to the relative position of a column

The following command uses a negative argument to refer to the position of a column relative to the current column.

row product, sales, sales.plan, colval(-2)/colval(-1)

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.

TENTS           32,153.52  42,346.89       0.76

Calculating column subtotals and totals

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.

Example: Calculating product totals for each month

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.

row indent 2 'Total', across month: over '-' -
   under '=' d 0 subtotal(1)

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
              ========== ========== ==========

Example: Calculating subtotals and totals

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
               ========== ========== ==========

Calculating running totals for columns

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.

Example: Calculating cumulative sales

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)

Initializing the subtotals

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.

zerototal

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.

zerototal 1, 2

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.

zerototal all, 2

Showing data calculated by functions

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:

• Specify the function in the expression to be reported.

• Create a variable that holds the result returned by the function, and then specify the holding variable in the expression to be reported.

Example: Storing a calculation in a holding variable

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.

total(units, district) / total(units)

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.

define tot decimal <district> temp
ld Total units ratio by district

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.

BOSTON               16.4
ATLANTA              21.2
CHICAGO              17.2
DALLAS               18.6
DENVER               15.6
SEATTLE              11.1