Calculating Row and Column Totals

In this section:

How to:

Reference:

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

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 Using Headings, Footings, Titles, and Labels.

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']

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

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

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

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

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

Producing Row Totals for Horizontal (ACROSS) Sort Field Values

How to:

Reference:

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.

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.

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

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 Identifying Row Totals (ACROSS-TOTAL) for Horizontal Sort Data.