Reporting Language > Sorting Tabular Reports > Sorting Columns

Sorting Columns

Topics:

Controlling Display of an ACROSS Title for a Single Field
Positioning ACROSS Titles on Report Output
Using Multiple Horizontal (ACROSS) Sort Fields
Collapsing PRINT With ACROSS

How to:

Sort Columns

Reference:

Usage Notes for Sorting Columns

You can sort report information horizontally using the ACROSS phrase. This creates columns in your report. The total number of ACROSS columns is equal to the total number of ACROSS sort field values multiplied by the total number of display fields.

A request can include up to 128 sort phrases consisting of any combination of BY and ACROSS phrases.

The maximum number of display fields your report can contain is determined by a combination of factors. In general, if a horizontal (ACROSS) sort field contains many data values, you may exceed the allowed width for reports, or create a report that is difficult to read. For details, see Displaying Report Data.

You can produce column totals or summaries for ACROSS sort field values using ACROSS-TOTAL, SUBTOTAL, SUB-TOTAL, RECOMPUTE, and SUMMARIZE. For details, see Including Totals and Subtotals.

Syntax: How to Sort Columns

ACROSS sortfield

where:

sortfield: Is the name of the sort field.

Reference: Usage Notes for Sorting Columns

You cannot use text fields as sort fields. Text fields are those described in the Master File with a FORMAT value of TX.
You can use a temporary field created by a DEFINE command, or by the DEFINE attribute in a Master File, as a sort field. However, you cannot use a temporary field created by a COMPUTE command as a sort field. You can accomplish this using the BY TOTAL phrase or indirectly by first creating a HOLD file that includes the field, and then reporting from the HOLD file. HOLD files are described in Saving and Reusing Your Report Output.
For an ACROSS phrase, the SET SPACES parameter controls the distance between ACROSS sets. For more information, see .
If you specify several sort fields when reporting from a multipath data source, all the sort fields must be in the same path.
In styled output formats (PDF, HTML, DHTML, PPT, PPTX, and XLSX), the width of ACROSS titles and ACROSS values above the data columns is defined as the largest width of all data columns, and associated column titles, within the ACROSS groups. To change the size of the ACROSS groups, apply SQUEEZE, WRAP, or WIDTH definitions to the data columns within each group.
Each sort field value is displayed only once in the report unless you change this default using the SET BYDISPLAY command. For example, if there are six employees in the MIS department, a report that declares
```
PRINT LAST_NAME ACROSS DEPARTMENT
```
prints MIS once, followed by six employee names.

Example: Sorting Columns With ACROSS

The following illustrates how to show the total salary outlay for each department. This request is sorted horizontally with an ACROSS phrase.

TABLE FILE EMPLOYEE
SUM CURR_SAL ACROSS DEPARTMENT
END

The output is:

DEPARTMENT
MIS                    PRODUCTION
---------------------------------
$108,002.00           $114,282.00

Notice that the horizontal sort displays a column for each sort field (department).

Controlling Display of an ACROSS Title for a Single Field

How to:

Control Display of an ACROSS Title for a Single Field

Using the SET ACRSVRBTITL command, you can control the display of an ACROSS column title in an ACROSS group. The behavior of the title is determined by the number of verb columns in the ACROSS group. The field count is affected by the following features, which add internal matrix columns to the report:

Fields in a heading or footing.
Fields whose display is suppressed with the NOPRINT phrase.
Reformatted fields (which are normally counted twice).
A COMPUTE command referencing multiple fields.

Syntax: How to Control Display of an ACROSS Title for a Single Field

SET ACRSVRBTITL = {HIDEONE|ON|OFF}
ON TABLE SET ACRSVRBTITL {HIDEONE|ON|OFF}

where:

HIDEONE: Suppresses the title when there is only one display field, or there is only one display field and the request contains one or more of the features that add internal matrix columns to the report. This value is the default.
ON: Always displays the title even if there is only one display field.
OFF: Suppresses the title when there is only one display field. Displays the title when there is only one display field and the request contains one or more of the features that add internal matrix columns to the report. This is legacy behavior.

Example: Hiding an ACROSS Title With ACRSVRBTITL

The following request against the GGSALES data source has a display field in the heading:

SET ACRSVRBTITL=HIDEONE
TABLE FILE GGSALES
HEADING
"Sales Report for <CATEGORY with ACRSVRBTITL=HIDEONE"
" "
SUM DOLLARS AS Sales
BY CATEGORY
ACROSS REGION
WHERE CATEGORY EQ 'Food'
ON TABLE SET PAGE NOPAGE
ON TABLE SET ACROSSTITLE SIDE
ON TABLE SET ACROSSLINE SKIP
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, SQUEEZE=ON,$
TYPE=TITLE,JUSTIFY=LEFT,BACKCOLOR=RGB(102 102 102),COLOR=RGB(255 255 255),STYLE=BOLD,$
TYPE=HEADING, SIZE=11, STYLE=BOLD,JUSTIFY=CENTER, $
TYPE=ACROSSTITLE,STYLE=BOLD,$
TYPE=ACROSSVALUE,BACKCOLOR=RGB(218 225 232),$
END

Using the default value for ACRSVRBTITL, HIDEONE, suppresses the ACROSS title Sales, even though the heading displays a field value that adds a report column to the internal matrix.