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.
display_command fieldname AND ROW-TOTAL [alignment][/format] [AS 'name'] display_command fieldname AND COLUMN-TOTAL [alignment][AS 'name']
where:
/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.
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']
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
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
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
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
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
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.
ACROSS sortfield ACROSS-TOTAL [AS 'name'] [COLUMNS col1 AND col2 ...]
where:
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
| WebFOCUS |