CROSSTAB command

Concept Information

Cross-tabulating data

Groups records based on identical combinations of values in two or more character or numeric fields, and displays 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>|ON ALL <EXCLUDE field_name <...n>>} COLUMNS column_field <SUBTOTAL numeric_field <...n>|SUBTOTAL ALL <EXCLUDE numeric_field <...n>>> TO {SCREEN|table_name|filename|GRAPH|PRINT} <LOCAL> <IF test> <WHILE test> <FIRST range|NEXT range> <APPEND> <COUNT> <OPEN> <HEADER header_text> <FOOTER footer_text>

Parameters

Name Description
ON row_field <...n> | ON ALL

One or more character or numeric fields or expressions to use for rows in the resulting grid of rows and columns.

  • ON row_field <...n> use the specified field or fields

    Multiple fields must be separated by spaces, and can be different data types.

    If you use more than one field, fields are included in the order that you list them.

  • ON ALL use all fields in the table

    If you use all fields, fields are included in the order that they appear in the table layout.
EXCLUDE field_name

optional

Only valid when using ON ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune ON ALL, by excluding the specified fields.

EXCLUDE must immediately follow ON ALL. For example:

ON ALL EXCLUDE field_1 field_2
COLUMNS column_field

The character or numeric 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> | SUBTOTAL ALL

optional

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.
EXCLUDE numeric_field

optional

Only valid when using SUBTOTAL ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune SUBTOTAL ALL, by excluding the specified fields.

EXCLUDE must immediately follow SUBTOTAL ALL. For example:

SUBTOTAL ALL EXCLUDE field_1 field_2
TO SCREEN | table_name | filename | GRAPH | PRINT

The location to send the results of the command to:

  • SCREEN displays the results in the Analytics display area

    Tip

    You can click any linked result value in the display area to drill down to the associated record or records in the source table.

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

  • filename saves the results to a file

    Specify filename as a quoted string with the appropriate file extension. For example: TO "Output.TXT"

    By default, the file is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the file to a different, existing folder:

    • TO "C:\Output.TXT"
    • TO "Results\Output.TXT"
  • GRAPH displays the results in a graph in the Analytics display area
  • PRINT sends the results to the default printer
LOCAL

optional

Saves the output file in the same location as the Analytics project.

Note

Applicable only when running the command against a server table with an output file that is an Analytics table.

The LOCAL parameter must immediately follow the TO parameter.
IF test

optional

A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition.

Note

The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test

optional

A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached.

Note

If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range | NEXT range

optional

The number of records to process:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

Use range to specify the number of records to process.

If you omit FIRST and NEXT, all records are processed by default.
APPEND

optional

Appends the command output to the end of an existing file instead of overwriting it.

Note

You must ensure that the structure of the command output and the existing file are identical:

  • the same fields
  • the same field order
  • matching fields are the same length
  • matching fields are the same data type

Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result.
COUNT

optional

Includes record counts as columns. Counts are useful when you use SUBTOTAL.

Counts are automatically included if you do not select any subtotal fields.
OPEN

optional

Opens the table created by the command after the command executes. Only valid if the command creates an output table.
HEADER header_text

optional

The text to insert at the top of each page of a report.

header_text must be specified as a quoted string. The value overrides the Analytics HEADER system variable.
FOOTER footer_text

optional

The text to insert at the bottom of each page of a report.

footer_text must be specified as a quoted string. The value overrides the Analytics FOOTER system variable.

Examples

Cross-tabulating an accounts receivable table with SUBTOTAL

You want to cross-tabulate an accounts receivable table on the Customer Number and Transaction Type fields. You also want to subtotal the Transaction Amount field.

The output is grouped by customer, and within each customer by transaction type. It includes 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

Cross-tabulating an accounts receivable table to find duplicate transactions

You need to find evidence of duplicate transactions in an accounts receivable table.

To do this, you cross-tabulate an accounts receivable table on the Transaction Amount and Transaction Type fields. The output groups and counts identical transaction amounts for each transaction type:

OPEN Ar
CROSSTAB ON Trans_Amount COLUMNS Trans_Type TO SCREEN

Remarks

For more information about how this command works, see Cross-tabulating data.

How it works

CROSSTAB groups records that have the same combination of values in two or more character or numeric fields.

The output contains a grid of rows and columns similar to a pivot table. It includes 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.

Sorting and CROSSTAB

CROSSTAB can process either sorted or unsorted data. Both the row_field and the column_field in the output are automatically sorted in ascending order.

If you specify more than one row_field, the fields use a nested sort. The order of the nesting follows the field order you specify, or the order of the fields in the table layout if you use ON ALL.