CROSSTAB command

Used to group records based on unique values in two or more character fields, and to display the resulting groups in a grid of rows and columns. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

Syntax

CROSSTAB <ON> row_field <...n> COLUMNS column_field
<SUBTOTAL numeric_field <...n>> TO {SCREEN|table_name|filename|GRAPH|PRINT}
<IF test> <WHILE test> <{FIRST|NEXT} range> <APPEND> <COUNT> <OPEN> <LOCAL>
<HEADER header_text> <FOOTER footer_text>

Parameters

ON row_field <...n>

Specifies the field or expression to use for rows in the resulting grid of rows and columns. You can specify one or more fields or expressions as the basis for the rows. The ON parameter can be omitted.

COLUMNS column_field

Specifies the field or expression to use for columns in the resulting grid of rows and columns. You can specify only one field or expression for the columns.

SUBTOTAL numeric_field <...n>

Optional. Specifies one or more numeric fields or expressions to subtotal for each group. Multiple fields must be separated by spaces. Specify ALL to subtotal all the numeric fields in the table.

TO SCREEN | table_name | filename | GRAPH | PRINT
  • Specify TO SCREEN to display the results in the ACL display area. This is the default output if the TO parameter is not specified.

  • Specify TO table_name to save the results to an ACL table. You must specify the table_name value as a quoted string with a .FIL file extension to create an ACL table.

    For example: TO "Output.FIL"

    You can also specify an absolute or relative file path to save the .FIL file to an existing folder.

    For example: TO "C:\Output.FIL" or TO "Results\Output.FIL"

  • Specify TO filename to save the results to a file. You must specify the filename value as a quoted string with the appropriate file extension. For example, specify the .TXT file extension to create a text file.

    For example: TO "Output.TXT"

    You can also specify an absolute or relative file path to save the file to an existing folder.

    For example: TO "C:\Output.TXT" or TO "Results\Output.TXT"

  • Specify TO PRINT to send the results to the default printer.

  • Specify TO GRAPH to display the results in a graph in the ACL display area.

IF test

Optional. Specifies a condition that must be met. The command is executed on only those records that meet the condition.

WHILE test

Optional. Specifies that the command is executed only while a condition is true. The command processing terminates as soon as the specified condition evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

APPEND

Optional. Specifies that command output is appended to the end of an existing file instead of overwriting it. When using this parameter, you must ensure that the structure of the command output and the existing file is identical, if maintaining a consistent file structure is important (for example, when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

COUNT

Optional. Includes record counts as columns. Counts are useful when you use the SUBTOTAL parameter. Counts are automatically included if you do not select any subtotal fields.

OPEN

Optional. Specifies that the table created by the command is opened after the command executes. This parameter is only valid if the command creates an output table.

LOCAL

Optional. Specifies that the output file is saved in the same location as the ACL project. This parameter only applies when the command is run on an ACL Server table and the output file is an ACL table.

HEADER header_text

Optional. Specifies the text to insert at the top of each page of a report. The header_text value must be specified as a quoted string. The value overrides any value set for the ACL HEADER system variable.

FOOTER footer_text

Optional. Specifies the text to insert at the bottom of each page of a report. The footer_text value must be specified as a quoted string. The value overrides any value set for the ACL FOOTER system variable.

Remarks

The CROSSTAB command groups records that have the same combination of values in two or more character fields. The grid of rows and columns in the output results, similar to a pivot table, contains a single row-column intersection for each group, with a count of the number of records in the source table that belong to the group.

The CROSSTAB command can process either sorted or unsorted data. Both the row_field and the column_field in the output results are automatically sorted in ascending order. If you specify more than one row_field, the fields use a nested sort, starting with the first row_field you specify.

You can cross-tabulate a numeric field by using the STRING( ) function to create an expression that converts the numeric data to character data. For example:

CROSSTAB ON STRING(Trans_Amount, 22) COLUMNS Trans_Type TO SCREEN

creates output results that group and count identical transaction amounts for each transaction type. For more information, see STRING( ) function.

Example

The following example cross-tabulates an accounts receivable table on the Customer Number and Transaction Type fields, and subtotals the Transaction Amount field. The output results are grouped by customer, and within customer by transaction type, and include the total transaction amount for each customer for each transaction type.

OPEN Ar
CROSSTAB ON Customer_Number COLUMNS Trans_Type SUBTOTAL Trans_Amount COUNT
TO SCREEN


(C) 2015 ACL Services Ltd. All Rights Reserved.