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.
{SUM|COUNT} display_field WITHIN by_sort_field [WITHIN across_sort_field] BY by_sort_field [ACROSS across_sort_field]
where:
Is the object of a SUM or COUNT display command.
Is the object of a BY phrase.
Is the object of an ACROSS phrase.
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:
PCT PCT SOLD SOLD WITHIN WITHIN STORE_CODE PROD_CODE UNITS TABLE STORE ---------- --------- ----- ------ ------ K1 B10 13 2 30 B12 29 4 69 14B B10 60 9 15 B12 40 6 10 B17 29 4 7 C13 25 3 6 C7 45 6 11 D12 27 4 7 E2 80 12 21 E3 70 10 18 14Z B10 30 4 18 B17 20 3 12 B20 15 2 9 C17 12 1 7 D12 20 3 12 E1 30 4 18 E3 35 5 21 77F B20 25 3 38 C7 40 6 61
Information Builders |