Reformatting Sort Fields

How to:

Reference:

When displaying a vertical (BY) sort column or horizontal (ACROSS) sort row on report output, you can reformat the sort field values by specifying the new format in the sort phrase. The reformatting affects only the sort field value as displayed on the sort row or column. That is, if the field used as a sort field is referenced in a heading, subheading, footing, subfooting, or summary line, it displays with its original format.

Syntax: How to Reformat a Sort Field

{BY [TOTAL]|ACROSS [TOTAL]} sortfield/fmt ...

where:

sortfield

Is the sort field.

fmt

Is the new display format.

Reference: Usage Notes for Reformatting Sort Fields

  • Reformatting is not supported in an ON phrase.
  • Reformatting is only applied to the row or column generated by the sort phrase, not to subheading, subfooting, or summary rows that reference the sort field.
  • Field-based reformatting (the format is stored in a field) is not supported for sort fields. For information on field-based reformatting, see Applying Dynamically Formatted Virtual Fields to Report Columns.
  • BY field reformatting is propagated to HOLD files. ACROSS field reformatting is not propagated to HOLD files.
  • The following features issue a warning, ignore the reformatting, and proceed with the request:
    • IN-GROUPS/RANGES-OF
    • ROWS
    • COLUMNS
    • FOR field/reformat
  • All decisions are made based on an original field, therefore any option on a reformatted sort field should be placed on an original sort field break point. As a consequence, whenever the sort field value should appear (for example, in SUBTOTALS) an original field value displays.

Example: Reformatting Sort Fields

The following request against the GGSALES data source includes the following reformatted sort fields:

  • BY CATEGORY, with CATEGORY reformatted as A3.
  • BY PRODUCT, with PRODUCT reformatted as A4.
  • ACROSS REGION, with region reformatted as A6.
TABLE FILE GGSALES
SUM UNIT
BY CATEGORY/A3
BY PRODUCT/A4
ACROSS REGION/A6
ON CATEGORY SUBTOTAL
ON CATEGORY SUBHEAD
"CATEGORY IS <CATEGORY "
" "
ON TABLE SET PAGE NOPAGE                        
END

On the output, the reformatting displays on the BY and ACROSS rows but is not propagated to the subheading and subtotal rows:

                   REGION
                     Midwes       Northe       Southe       West
CATEGORY  PRODUCT
-----------------------------------------------------------------------
CATEGORY IS Coffee

Cof       Capu              .        44785        73264        72831
          Espr         101154        68127        68030        71675
          Latt         231623       226243       211063       213920

*TOTAL CATEGORY Coffee
                       332777       339155       352357       358426

CATEGORY IS Food

Foo       Bisc          90413       149793       119594        70569
          Croi         139881       137394       156456       197022
          Scon         116127        70732        73779        72776
*TOTAL CATEGORY Food
                       346421       357919       349829       340367

CATEGORY IS Gifts

Gif       Coff          54002        40977        50556        48081
          Coff          47156        46185        49922        47432
          Mug           86718        91497        88474        93881
          Ther          46587        48870        48976        45648
*TOTAL CATEGORY Gifts
                       234463       227529       237928       235042


TOTAL                  913661       924603       940114       933835

Information Builders