Calculating Row and Column Totals
 In this section: Producing Row Totals for Horizontal (ACROSS) Sort Field ValuesHow to:Calculate Row and Column TotalsReference: Using ROW-TOTAL With ACROSS and Multiple Display Commands

You can produce totals for rows or columns of numbers in a report. Use:

• ROW-TOTAL to display a new column containing the sum of all numbers in each row.
• COLUMN-TOTAL to display a final row on the report, which contains the totals for each column of numbers.

You can use row totals and column totals in matrix reports (created by using a BY and an ACROSS in your report request), rename row and column total titles, and include calculated values in your row or column totals. You can also create row totals using ACROSS-TOTAL.

Note that when producing totals in a report, if one field is summed, the format of the row total is the same as the format of the field. For example, if the format of the CURR_SAL field is D12.2M, the format of the row total for CURR_SAL is also D12.2M. When you are summing fields with different formats, the default format of D12.2 is used for the total.

You can rename the default column titles using the AS phrase and align the labels for row and column totals. For details, see

 Top of page
Syntax: How to Calculate Row and Column Totals
```display_command fieldname AND ROW-TOTAL [alignment][/format] [AS 'name']
display_command fieldname AND COLUMN-TOTAL [alignment][AS 'name']```

where:

display_command
Is one of the following commands: PRINT, LIST, SUM, or COUNT.
fieldname
Is the name of the field for which to calculate row and/or column totals.
alignment
Specifies the alignment of the ROW-TOTAL or COLUMN-TOTAL label. Possible values are:

/R right justifies the label.

/L left justifies the label.

/C centers the label.

Note that these alignment settings are ignored in HTML output. If you are working in WebFOCUS, to take advantage of column alignment features, you can include the command SET STYLE=OFF in the report request or generate your output in PDF, or in another format that supports these features.

format
Reformats the ROW-TOTAL.
name
Is the label for the ROW-TOTAL or COLUMN-TOTAL.

You may also specify row or column totals with the ON TABLE command. Field names are optional with COLUMN-TOTAL, and cannot be listed with ROW-TOTAL. Use the following syntax:

```ON TABLE COLUMN-TOTAL [alignment][AS 'name'][field field field]
ON TABLE ROW-TOTAL [alignment][/format] [AS 'name']```
 Top of page
Example: Calculating Row and Column Totals

The following request illustrates the use of ROW-TOTAL and COLUMN-TOTAL. The column and row total labels are "TOTAL" by default. You can change them using an AS phrase.

```TABLE FILE SALES
SUM RETURNS DAMAGED AND ROW-TOTAL AND COLUMN-TOTAL
BY PROD_CODE
END```

The output is:

```PROD_CODE  RETURNS  DAMAGED      TOTAL
---------  -------  -------  ---------
B10             13       10         23
B12              4        3          7
B17              4        2          6
B20              1        2          3
C13              3        0          3
C17              0        0          0
C7               5        4          9
D12              3        2          5
E1               4        7         11
E2               9        4         13
E3              12       11         23

TOTAL           58       45        103```
 Top of page
Example: Specifying Column Totals With ON TABLE

The following request illustrates the use of COLUMN-TOTAL with the ON TABLE command.

```TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY LAST_NAME
ON TABLE COLUMN-TOTAL
END```

The output is:

```LAST_NAME               CURR_SAL
---------               --------
BANNING               \$29,700.00
BLACKWOOD             \$21,780.00
CROSS                 \$27,062.00
GREENSPAN              \$9,000.00
IRVING                \$26,862.00
JONES                 \$18,480.00
MCCOY                 \$18,480.00
MCKNIGHT              \$16,100.00
ROMANS                \$21,120.00
SMITH                 \$13,200.00
\$9,500.00
STEVENS               \$11,000.00

TOTAL                \$222,284.00```
 Top of page
Example: Using Row and Column Totals in a Matrix Report

The following request illustrates the use of ROW-TOTAL and COLUMN-TOTAL in a matrix report (created by using the BY and ACROSS phrases together).

```TABLE FILE EMPLOYEE
SUM CURR_SAL AND ROW-TOTAL AND COLUMN-TOTAL
BY BANK_NAME
ACROSS DEPARTMENT
END```

The output is:

```                           DEPARTMENT
BANK_NAME                  MIS              PRODUCTION       TOTAL
-----------------------------------------------------------------------
\$40,680.00       \$41,620.00       \$82,300.00
ASSOCIATED                 \$21,780.00       \$42,962.00       \$64,742.00
BANK ASSOCIATION           \$27,062.00                .       \$27,062.00
BEST BANK                           .       \$29,700.00       \$29,700.00
STATE                      \$18,480.00                .       \$18,480.00

TOTAL                     \$108,002.00      \$114,282.00      \$222,284.00```
 Top of page
Example: Including Calculated Values in Row and Column Totals

The following request illustrates the inclusion of the calculated value, PROFIT, in row and column totals.

```TABLE FILE CAR
SUM DCOST RCOST
COMPUTE PROFIT/D12=RCOST-DCOST;
ROW-TOTAL/L/D12 AS 'TOTAL_COST'
BY COUNTRY
ON TABLE COLUMN-TOTAL/L AS 'FINAL_TOTAL'
END```

The output is:

```COUNTRY     DEALER_COST  RETAIL_COST           PROFIT       TOTAL_COST
-------     -----------  -----------           ------  ---------------
ENGLAND          37,853       45,319            7,466           90,638
FRANCE            4,631        5,610              979           11,220
ITALY            41,235       51,065            9,830          102,130
JAPAN             5,512        6,478              966           12,956
W GERMANY        54,563       64,732           10,169          129,464

FINAL_TOTAL     143,794      173,204           29,410          346,408```
 Top of page
Reference: Using ROW-TOTAL With ACROSS and Multiple Display Commands

When a request has an ACROSS sort field, each ACROSS value displays a column for each field displayed on the report output. For example, the following request, each state has a column for units and a column for dollars:

```TABLE FILE GGSALES
SUM UNITS AS 'U' DOLLARS AS 'D' BY CITY
ACROSS ST
IF ST EQ 'CA'
IF BUDUNITS NE MISSING
END```

The output is:

```                       State
CA
City                         U        D
-----------------------------------------
Los Angeles             298070  3772014
San Francisco           312500  3870258```

When you specify a row total with ACROSS, the row total is calculated separately for each column in each ACROSS group. For example, in the following request the row total has a column for units and a column for dollars:

```TABLE FILE GGSALES
SUM UNITS AS 'U' DOLLARS AS 'D' BY CITY
ACROSS ST
IF ST EQ 'CA'
IF BUDUNITS NE MISSING
ON TABLE ROW-TOTAL
END```

The output is:

```                       State
CA                          TOTAL
City                         U        D         U        D
----------------------------------------------------------
Los Angeles             298070  3772014    298070  3772014
San Francisco           312500  3870258    312500  3870258```

When the request also has multiple display commands, each additional command adds additional columns to each ACROSS group on the report output.

The first column of the row total group is calculated by adding the first column from each display command under each ACROSS value, the second column adds the second column from each display command, and so on.

For example, the following request has a SUM command for units and dollars and another SUM command for budgeted units and budgeted dollars. The row total has a column for the sum of units and budgeted units and another column for the sum of dollars and budgeted dollars:

```TABLE FILE GGSALES
SUM UNITS AS 'U' DOLLARS AS 'D'         BY CITY
SUM BUDUNITS AS 'BU' BUDDOLLARS AS 'BD' BY CITY
ACROSS ST
IF ST EQ 'CA'
IF BUDUNITS NE MISSING
ON TABLE ROW-TOTAL
END```

The output is:

```                   State
CA                                           TOTAL
City                    U         D        BU       BD       BU       BD
------------------------------------------------------------------------
Los Angeles        298070   3772014    295637  3669484   593707  7441498
San Francisco      312500   3870258    314725  3916863   627225  7787121```

If the different display commands do not all specify the same number of fields, some columns will not be represented in the row total. For example, in the following request, the second SUM command has a column for budgeted units but not for budgeted dollars. Therefore, the row total group has no column for dollars:

```TABLE FILE GGSALES
SUM UNITS AS 'U' DOLLARS AS 'D' BY CITY
SUM BUDUNITS AS 'BU'            BY CITY
ACROSS ST
IF ST EQ 'CA'
IF BUDUNITS NE MISSING
ON TABLE ROW-TOTAL
END```

The output is:

```                        State
CA                             TOTAL
City                         U         D        BU        BU
------------------------------------------------------------
Los Angeles             298070   3772014    295637    593707
San Francisco           312500   3870258    314725    627225```

In this case, you can use column notation to calculate the row total properly. For example, the following request calculates the row total column by adding the units, dollars, and budgeted units columns together:

```TABLE FILE GGSALES
SUM UNITS AS 'U' DOLLARS AS 'D' BY CITY
SUM BUDUNITS AS 'BU'            BY CITY
ACROSS ST
COMPUTE TOTAL/I10 = C1 + C2 +C3; AS 'ROW-TOTAL'
IF ST EQ 'CA'
IF BUDUNITS NE MISSING
END```

The output is:

```                        State
CA                            ROW-TOTAL
City                         U         D        BU
---------------------------------------------------------------
Los Angeles             298070   3772014    295637      4365721
San Francisco           312500   3870258    314725      4497483```
 Top of page
Producing Row Totals for Horizontal (ACROSS) Sort Field Values
 How to:Produce Row Totals for Horizontal (ACROSS) Sort Field ValuesReference: Usage Notes for ACROSS-TOTAL

You can produce row totals for horizontal (ACROSS) sort field values. Row totals for horizontal sort fields, referenced by ACROSS-TOTAL, are different from standard row totals because only horizontal sort field values, referenced by ACROSS, are included in the total. Integer, single precision floating point, double precision floating point, packed, and long packed fields can all be totaled.

 Top of page
Syntax: How to Produce Row Totals for Horizontal (ACROSS) Sort Field Values
`ACROSS sortfield ACROSS-TOTAL [AS 'name'] [COLUMNS col1 AND col2 ...]`

where:

sortfield
Is the name of the field being sorted across.
name
Is the new name for the ACROSS-TOTAL column title.
col1, col2
Are the titles of the ACROSS columns you want to include in the total.
 Top of page
Example: Producing Row Totals for Horizontal (ACROSS) Sort Field Values

The following illustrates how to generate a row total for horizontal (ACROSS) sort field values. Notice that the summed values in the TOTAL TITLE COUNT column only reflect the values in the (RATING) PG and R columns. The values in the COPIES column are not included since they are not horizontal (ACROSS) sort field values.

```TABLE FILE MOVIES
SUM COPIES BY CATEGORY
COUNT TITLE BY CATEGORY
ACROSS RATING ACROSS-TOTAL
COLUMNS PG AND R
END```

The output is:

```                    RATING
PG     R      TOTAL
CATEGORY  COPIES
---------------------------------------
ACTION        14      2      3      5
COMEDY        16      4      1      5
DRAMA          2      0      1      1
FOREIGN        5      2      3      5
MUSICALS       2      1      1      2
MYSTERY       17      2      5      7
SCI/FI         3      0      3      3  ```
 Top of page
Reference: Usage Notes for ACROSS-TOTAL
• Stacking headings in ACROSS-TOTAL is not supported.
• Attempting to use ACROSS-TOTAL with other types of fields (alphanumeric, text, and dates) produces blank columns.
• In cases of multiple ACROSS columns with ACROSS-TOTAL, there are additional columns with subtotaled values.
• The results of ROW-TOTAL and ACROSS-TOTAL are the same if there is only a single display field or single display command in the procedure.
• The maximum number of ACROSS-TOTAL components is five.
• ACROSS-TOTAL populates the ACROSSVALUE component in a StyleSheet. For an example of styling an ACROSS-TOTAL component, see
 WebFOCUS