Manipulating Display Field Values in a Sort Group

How to:

You can use the WITHIN phrase to manipulate a display field values as they are aggregated within a sort group. This technique can be used with a prefix operator to perform calculations on a specific aggregate field rather than a report column. In contrast, the SUM and COUNT commands aggregate an entire column.

The WITHIN phrase requires a BY phrase and/or an ACROSS phrase. A maximum of two WITHIN phrases can be used per display field. If one WITHIN phrase is used, it must act on a BY phrase. If two WITHIN phrases are used, the first must act on a BY phrase and the second on an ACROSS phrase.

You can also use WITHIN TABLE, which allows you to return the original value within a request command. The WITHIN TABLE command can also be used when an ACROSS phrase is needed without a BY phrase. Otherwise, a single WITHIN phrase requires a BY phrase.

Syntax: How to Use WITHIN to Manipulate Display Fields 

{SUM|COUNT} display_field WITHIN by_sort_field [WITHIN across_sort_field]
   BY by_sort_field [ACROSS across_sort_field]

where:

display_field

Is the object of a SUM or COUNT display command.

by_sort_field

Is the object of a BY phrase.

across_sort_field

Is the object of an ACROSS phrase.

Example: Summing Values Within Sort Groups

The following report shows the units sold and the percent of units sold for each product within store and within the table: 

TABLE FILE SALES
SUM UNIT_SOLD AS 'UNITS'
AND PCT.UNIT_SOLD AS 'PCT,SOLD,WITHIN,TABLE'
AND PCT.UNIT_SOLD WITHIN STORE_CODE AS 'PCT,SOLD,WITHIN,STORE'
BY STORE_CODE SKIP-LINE BY PROD_CODE
END

The output is: