You are here: Tools > All Tools > Cross Tab

About the CrossTab Tool:

The CrossTab pivots the orientation of the data table. It transforms the data so vertical data fields can be viewed on a horizontal axis summarizing data where specified.

The CrossTab 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 amount of records or fields that can be put through the CrossTab tool.

Configuration Properties:

  1. Select the Grouping Field(s) to use in performing the CrossTab operation. A list of all available fields will appear in the window. Check the box that corresponds to any field(s) to GROUP on.

    The group by process combines database records with identical values in a specified field into a single record. All of the resulting data from the records in a group are then summarized.

  2. Choose the Header Field from the drop down list.  The Header field is records whose header field contains identical values will be summarized together in a new output field for the same group. These are the fields that will appear across the horizontal axis of the new table.
  3. Choose the Data Field to populate the Header Field, selected in Step2. The data field contains the values that will be summarized according to the methodology specified in step 4.
  4. Select the Methodologies for handling data fields. At least one Methodology MUST be selected. Choices in this list depend on the data field type chosen in step3

    Choices for String fields are:

    1. Concatenate : Strings will be arranged into one field, separated by the specified separator (step 5) and up to the specified field length (step 6)
    2. First: The output will be the first found value within the unique group and header field
    3. Last: The output will be the last found value within the unique group and header field

    Choices for Number fields are:

    1. Sum: Sums the data fields
    2. Average: Calculates an average based on the values of the data fields
    3. Count: Count of records in unique group and header field.
    4. Percent Row: Calculates a percent based on the value of the data field as it pertains to the data values of the row after the CrossTab.
    5. Percent Column: Calculates a percent based on the value of the data field as it pertains to the data values of the column after the CrossTab.
    6. Total Column: Totals all data fields within a group
    7. Total Row: Adds a new row containing a grand total

    Note: when choosing one or more of the methodologies above, the abbreviation for that process will be appended to the field name, example: AVG_FieldValue. UNLESS only one of the following methodologies: SUM, First or Last are selected. In which case, nothing is appended.

  5. Notice the String Separator box, this setting is specific for concatenating strings.

    The character specified in this box will automatically be inserted into the concatenated string where those strings were joined. You can insert any character here, or leave blank.

  6. Notice the Field Size box, this 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 to the extent of the specified field size.

Click Apply to have the configurations accepted.

Note: For information regarding Input, Output, Annotation and Error Properties, see Tool Properties.

       Related Topics Link IconRelated Topics