Cross Table

The handler creates the pivot table based on the source data set. When creating the table, the following criteria are met:

• A part of fields of the source data set is unchanged forming the strings of the pivot table.
• The values of cells of the other fields groups form the columns of the pivot table defining their headers.
• Data of the third group of fields is used for calculation of measures — aggregated values at the intersection of strings and columns of the pivot table.

Input

• Input data source: the port for connection of the input data set.

Output

• Output data set: the table with processed data is output to the port.

Wizard

The wizard window is divided into two areas: available fields (to the left) and selected fields (to the right).

• Available fields contain the input data set fields.
• Selected fields are divided into groups.
  • Columns.
  • Strings.
  • Measures.

The Available fields area always contains the Count synthetic field apart from the input data set fields, it can be added only to the Measures group. It will be calculated how many times each combination of columns and strings occurs in the source data set.

Columns

The values of these fields will be the columns headers. The fields included into this group must relate to the discrete data kind.

When using the cross table, new values can appear in the fields by which the columns were generated. The handler provides two approaches to solve this problem:

• Sliding unique values enable to create columns from the unique field (fields) values. When changing the source data set values, the whole structure of the resulting table will be fully reconstructed taking into account the new unique values. It is also possible to set the minimum number of the fields values from which the columns will be created. But in this case, the column with the remaining values will be left in the resulting table.

• Group of values: if the new values appear in the source field from the moment of the last node configuration, in this case, with the enabled Other checkbox, measures for such values will be aggregated in the Other column. The enabled Null checkbox displays the field data with null values in the output data set. Measures for them will be aggregated in the Null values column.

The lower wizard part includes the General Columns Settings area that has the following parameters:

• Field caption parts separator: it is required to select the character to separate the new fields captions when selecting several fields in the columns.
  • . (Dot).
  • | (horizontal line).
  • ->.
  • Space.
• Sliding unique values: the checkbox selection enables to create columns from the unique field (fields) values again. When changing the source data set values, the whole structure of the resulting table will be fully reconstructed taking into account the new values.
• Value number limit is used to limit the maximum count of columns in the output data set. The first n generated fields are selected if the set number of values exceeds the number of the unique values in the columns, the fictitious columns are not added.

Using and buttons, it is possible to change the order of fields in the group. The order of fields location has an impact on the resulting table structure.

Strings

The cross table rows will be generated from the fields values. The similar field (fields) values will be grouped in the same manner as it is organized in the Grouping handler.

The fields order in this group has an impact on data sorting in the resulting table by these fields.

Measures

The fields data in this group are processed according to the aggregation functions. The obtained values are displayed at the intersection of columns and strings. By default, the Sum function is selected for all numerical types, whereas for others - Count.

To select other aggregation functions, it is required to double click on the field. When selecting several function options, each of them will be calculated in a separate column.

The order of fields does not matter in the Measures group.