Sorting Columns

You can sort report information horizontally using the ACROSS phrase. This creates columns in your report. You can have up to five ACROSS phrases per report request. Each ACROSS phrase can generate up to 255 columns of data. 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.

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 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 Customizing Tabular Reports.
Sort phrases cannot contain format information for fields.
If you specify several sort fields when reporting from a multi-path data source, all the sort fields must be in the same path.
Each sort field value is displayed only once in the report. 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.

Top of page

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 Underlines for ACROSS Objects

How to:

Control Underlining for ACROSS Objects

The SET ACROSSLINE command allows users to turn off/on optional underlining in reports to highlight ACROSS objects. The feature is only available for Hotscreen reports and report output formats WP, HTML, and PDF.

Top of page

Syntax: How to Control Underlining for ACROSS Objects

Issue the following command in any supported profile, or in a FOCEXEC, or at the command prompt:

SET ACROSSLINE= (ON|OFF|SKIP)

where:

ON: Underlines ACROSS objects in report headings with a dashed line. ON is the default value.
OFF: Replaces the underline with a blank line.
SKIP: Specifies no underline and no blank line.

Top of page

Example: Underlining ACROSS Objects With a Dashed Line (SET ACROSSLINE=ON)

SET ACROSSLINE=ON
TABLE FILE GGSALES
SUM UNITS BY PRODUCT
ACROSS REGION
END

The output is:

                      Region
                      Midwest   Northeast    Southeast       West
Product
___________________________________________________________________
Biscotti               86105       145242       119594        70436
Cappuccino                 .        44785        73264        71168
Coffee Grinder         50393        40977        47083        48081
Coffee Pot             47156        46185        49922        47432
Croissant             139182       137394       156456       197022
Espresso              101154        68127        68030        71675
Latte                 231623       222866       209654       213920
Mug                    86718        91497        88474        93881
Scone                 116127        70732        73779        72776
Thermos                46587        48870        48976        45648

Top of page

Example: Removing Underlines for ACROSS Objects (SET ACROSSLINE=SKIP)

SET ACROSSLINE=SKIP
TABLE FILE GGSALES
SUM UNITS BY PRODUCT
ACROSS REGION
END

The output is:

                     Region
                     Midwest    Northeast    Southeast       West
Product
Biscotti               86105       145242       119594        70436
Cappuccino                 .        44785        73264        71168
Coffee Grinder         50393        40977        47083        48081
Coffee Pot             47156        46185        49922        47432
Croissant             139182       137394       156456       197022
Espresso              101154        68127        68030        71675
Latte                 231623       222866       209654       213920
Mug                    86718        91497        88474        93881
Scone                 116127        70732        73779        72776
Thermos                46587        48870        48976        45648

Top of page

Example: Replacing the Underline With a Blank Line (SET ACROSSLINE=OFF)

SET ACROSSLINE=OFF
TABLE FILE GGSALES
SUM UNITS BY PRODUCT
ACROSS REGION
END

Turning ACROSSLINE=OFF replaces the (default) dashed line with an extra blank line between the report heading and the detail lines:

                     Region
                     Midwest    Northeast    Southeast        West
Product
 
Biscotti               86105       145242       119594        70436
Cappuccino                 .        44785        73264        71168
Coffee Grinder         50393        40977        47083        48081
Coffee Pot             47156        46185        49922        47432
Croissant             139182       137394       156456       197022
Espresso              101154        68127        68030        71675
Latte                 231623       222866       209654       213920
Mug                    86718        91497        88474        93881
Scone                 116127        70732        73779        72776
Thermos                46587        48870        48976        45648

Using Multiple Horizontal (ACROSS) Sort Fields

You can sort a report using more than one sort field. When several sort fields are used, the ACROSS phrase order determines the sorting order. The first ACROSS phrase sets the first sort break, the second ACROSS phrase sets the second sort break, and so on. Each successive sort is nested within the previous one.

Top of page

Example: Sorting With Multiple Horizontal (ACROSS) Phrases

The following request sorts the sum of current salaries, first by department and then by job code.

TABLE FILE EMPLOYEE
SUM CURR_SAL
ACROSS DEPARTMENT ACROSS CURR_JOBCODE
WHERE CURR_SAL GT 21500
END

The output is:

DEPARTMENT                                                          
     MIS                               PRODUCTION                   
CURR_JOBCODE                                                        
            A17              B04              A15              A17  
--------------------------------------------------------------------
     $27,062.00       $21,780.00       $26,862.00       $29,700.00

Collapsing PRINT With ACROSS

How to:

Compress Report Lines

Reference:

Usage Notes for SET ACROSSPRT

The PRINT command generates a report that has a single line for each record retrieved from the data source after screening out those that fail IF or WHERE tests. When PRINT is used in conjunction with an ACROSS phrase, many of the generated columns may be empty. Those columns display the missing data symbol.

To avoid printing such a sparse report, you can use the SET ACROSSPRT command to compress the lines in the report. The number of lines is reduced within each sort group by swapping non-missing values from lower lines with missing values from higher lines, and then eliminating any lines whose columns all have missing values.

Because data may be moved to different report lines, row-based calculations such as ROW-TOTAL and ACROSS-TOTAL in a compressed report are different from those in a non-compressed report. Column calculations are not affected by compressing the report lines.

Top of page

Syntax: How to Compress Report Lines

SET ACROSSPRT = {NORMAL|COMPRESS}

ON TABLE SET ACROSSPRT{NORMAL|COMPRESS}

where:

NORMAL: Does not compress report lines. NORMAL is the default value.
COMPRESS: Compresses report lines by promoting data values up to replace missing values within a sort group.

Top of page

Reference: Usage Notes for SET ACROSSPRT

Compression applies only to ACROSS fields, including ACROSS … COLUMNS. It has no effect on BY fields.
The only data values that are subject to compression are true missing values. If the value of the stored data is either 0 or blank and the metadata indicates that MISSING is ON, that value is not subject to compression.

Top of page

Example: Compressing Report Output With SET ACROSSPRT

The following request against the GGSALES data source prints unit sales by product across region:

TABLE FILE GGSALES                           
PRINT UNITS/I5                                     
BY PRODUCT                                   
ACROSS REGION                              
WHERE DATE FROM '19971201' TO '19971231';    
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso'; 
ON TABLE SET ACROSSPRT NORMAL 
ON TABLE SET PAGE NOPAGE
END

Each line of the report represents one sale in one region, so at most one column in each row has a non-missing value when ACROSSPRT is set to NORMAL:

                  Region                                         
                  Midwest     Northeast   Southeast   West       
Product           Unit Sales  Unit Sales  Unit Sales  Unit Sales 
-----------------------------------------------------------------
Capuccino                  .         936           .           . 
                           .         116           .           . 
                           .         136           .           . 
                           .           .        1616           . 
                           .           .        1118           . 
                           .           .         774           . 
                           .           .           .        1696 
                           .           .           .        1519 
                           .           .           .         836 
Espresso                1333           .           .           . 
                         280           .           .           . 
                         139           .           .           . 
                           .        1363           .           . 
                           .         634           .           . 
                           .         406           .           . 
                           .           .        1028           . 
                           .           .        1014           . 
                           .           .         885           .
                           .           .           .        1782
                           .           .           .        1399
                           .           .           .         551

Setting ACROSSPRT to COMPRESS promotes non-missing values up to replace missing values within the same BY group and then eliminates lines consisting of all missing values:

TABLE FILE GGSALES                           
PRINT UNITS/I5                                     
BY PRODUCT                                   
ACROSS REGION                              
WHERE DATE FROM '19971201' TO '19971231';    
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso'; 
ON TABLE SET ACROSSPRT COMPRESS ON TABLE SET PAGE NOPAGE                     
END

The output is:

                  Region                                        
                  Midwest     Northeast   Southeast   West      
Product           Unit Sales  Unit Sales  Unit Sales  Unit Sales
----------------------------------------------------------------
Capuccino                  .         936        1616        1696
                           .         116        1118        1519
                           .         136         774         836
Espresso                1333        1363        1028        1782
                         280         634        1014        1399
                         139         406         885         551