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.

Top of page
x
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:

                              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