Cross Tab Tool

The Cross Tab tool pivots the orientation of data in a table by moving vertical data fields onto a horizontal axis and summarizing data where specified. The Cross Tab tool is the reverse application of the Transpose Tool, which pivots the orientation of the data from horizontal to vertical. There is no limit to the number of rows or columns that can be passed through the Cross Tab tool.

Version support

In versions before Designer 10.5, identical strings with differing capitalization were used to create separate columns with modified names. In 10.5 and later, if two or more cells contain the same string with different capitalization, an error will be produced at runtime. To resolve this, ensure that data in each cell is unique or that the capitalization is consistent for matching data.

Configure the tool

A list of all available data values display in the Group Data by these Values field. Select the values that should be used to group the data. Data with identical values are grouped together into a single row.
Select a value from the New Column Headers drop-down list. A new column is created for each unique value.
Select a value from the Values for New Columns drop-down list. These values are used to populate the new columns.
Select a Method for Aggregating Values for combining multiple values in a field. Available options depend on the data type of the value selected in the Values for New Columns field. See Data Types.

Options for String data include:
- Concatenate: Separates the values using the separator specified in the Separator field.
- First: Displays the first found value.
- Last: Displays the last found value.
Options for Number data include:
- Sum: Sums the values.
- Average: Calculates an average of the values.
- Count (without Nulls): Counts the number of values excluding null values.
- Count (with Nulls): Counts the number of values including null values.
- Percent Row: Calculates a percent based on the values.
- Percent Column: Calculates a percent based on the values.
- Total Column: Totals all of the values.
- Total Row: Adds a new row containing a total of the values.
When choosing one or more of the methods above, an abbreviation for that method will be prepended to the column header, unless SUM, First, or Last is the only method selected.

AVG_FieldValue, SUM_FieldValue, COUNT_FieldValue.
The character specified in the Separator field is automatically added between concatenated strings. This option is only available when concatenating strings.
The Field Size field is the maximum field length for concatenating strings. If the string is larger than the size specified, a warning will appear in the Results window and the data will be truncated.