Reporting Language Primer > Creating Subtotals and Grand Totals > Creating Column and Row Totals

Creating Column and Row Totals

Topics:

The exercises in this topic demonstrate how to add column totals and row totals to your reports, using the following commands:

You can add these commands either to the verb phrase in a report request or to the ON TABLE phrase, as shown in the some of the following examples. You also can use the optional keyword AND to connect more than one command in a single request. The AND is included in some examples for clarity and omitted in others for brevity.

Creating a Column Total

Perhaps the simplest kind of total is one that adds the numeric values in a column. You can create totals for all columns that contain numeric data or only for particular columns.

To total the current salaries of all employees, issue the following request: 

TABLE FILE EMPLOYEE
PRINT CURR_SAL AND COLUMN-TOTAL
BY LAST_NAME
END

Run the request. The output is:

This report contains one column of numeric data. However, if it had contained another numeric field, for example, gross pay for each employee, that column would be totaled also. By default, COLUMN-TOTAL calculates values for all columns of numeric data.

Suppose that your next report has more than one column of numeric data, but that only one contains data that you want to summarize. To provide you with maximum control over the data you present, you can restrict totaling to that one column simply by naming the field you wish to total in the ON TABLE phrase.

Issue the following request:

TABLE FILE EMPLOYEE
PRINT DAT_INC AS 'DATE OF,INCREASE'
PCT_INC AS '%,INCREASE SALARY'
SALARY
BY LAST_NAME
ON TABLE COLUMN-TOTAL SALARY
END

Notice that this request identifies two columns of numeric data, PCT_INC and SALARY, yet it only asks to total employee salaries, since adding their percentages of increase would not provide meaningful information.

Run the request. The output is:

As requested, the report displays a single column total at the bottom of the SALARY column.

Creating a Row Total

Similarly, you can create row totals by including the command ROW-TOTAL in your report request.

To create a report with totals at the end of each row, issue the following request: 

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT
ACROSS DEPARTMENT
BY CURR_JOBCODE
ON TABLE ROW-TOTAL
END

Run the request. The output is:

Notice that if you use more than one (1) verb object, the output displays a row total for each of the fields, in this case, GROSS and DED_AMT.

Creating Column and Row Totals

You can add column totals and row totals to a report by including both commands in your request. To subtotal gross salary for each department, as well as for each job code, issue the previous request as follows: 

TABLE FILE EMPLOYEE
SUM GROSS
ACROSS DEPARTMENT
BY CURR_JOBCODE
ON TABLE ROW-TOTAL AND COLUMN-TOTAL
END

Run the request. The output is:

Once again, you could produce the same results by including the totaling commands in the verb phrase and omitting the ON TABLE phrase, as follows: 

SUM GROSS AND ROW-TOTAL AND COLUMN-TOTAL

 WebFOCUS

Feedback