How to Use the Cross Table


  1. Click the Cross Table button on the toolbar, cross_new_cross_table_button.png.

    Comment: You can also select Insert > Visualization > Cross Table from the menu.

    Response: A first attempt to set up a suitable cross table is made by the application.

  2. Adjust the cross table to display the categories and measures of your choice.

  1. From the Data panel, drag the column of interest into the cross table.

  2. Response: Three drop targets are shown.
    vis_drop_target_cross_table_y.png vis_drop_target_cross_table_x.png vis_drop_target_cross_table_values.png

  3. Drop the column onto relevant drop-target. Using the first drop-target will place the column on the vertical axis, and the second drop-target will place the column on the horizontal axis. A column dropped on the third target will be used for defining the actual numeric values inside the cross table.

    Note: You can also drag-and-drop the columns to the column selectors. This way you can set up hierarchies, which is a very powerful feature in a cross table.

  1. Right-click the cross table to display the pop-up menu.

  2. Select Properties.

  3. Click to go to the Axes page.

  4. On the Horizontal or Vertical axis, click the column selector and choose the column containing binary data.

  5. Click Label Rendering....

  6. Make sure the binary column is selected in the Column drop-down list of the Label Rendering dialog (only applicable if you have selected more than one column on the axis).

  7. Change Show as to Image.

  8. Click OK.

  1. Right-click the cross table to display the pop-up menu.

  2. Select Properties.

  3. Click to go to the Axes page.

  4. On the Horizontal or Vertical axis, click the column selector and choose the column containing the identifier to the image.

  5. Click Label Rendering....

  6. Make sure the identifier column is selected in the Column drop-down list of the Label Rendering dialog (only applicable if you have selected more than one column on the axis).

  7. Change Show as to Image from URL.

  8. In the Label Rendering dialog, click Settings....

  9. Enter the URL where {$} represents each cell value, so that the resulting URL when the values from the identifier column replaces the {$} points to your image. For example, if the images reside on a network drive which is common for everybody in your company and the identifier column contains the names of the images, then the URL to enter might be Q:\Images\{$}.

  10. Click OK twice.

Grand totals for columns and rows are calculated using the same aggregation or expression that is used for the calculation of the cell values. The totals are not based on the aggregated values shown in the cross table cells; they are by default based on the underlying data table rows.

Note: There is an option, though, to base a total on the aggregated cell values in the cross table. When you use this option, the total is calculated as the sum of the cell values, no matter expression used for the calculations of the cell values. See 'To display grand totals and subtotals as the sum of the cell values' below.

  1. Right-click the cross table to display the pop-up menu.

  2. Select Grand Total for Columns or Grand Total for Rows.

    Response: The selected grand total is displayed in the cross table, column grand totals at the bottom of the cross table and row grand totals furthest to the right.

    Comment: You can also make these selections on the Totals page of the Cross Table Properties.

To display column subtotals, that is, partial summarizations, in the cross table, the vertical axis must be set up as a hierarchy with at least two levels. Subtotals are calculated using the same aggregation or expression that is used for calculation of the cell values. The subtotals are not based on the aggregated values shown in the cross table cells; they are by default based on the underlying data table rows.

Note: There is an option, though, to base subtotals on the aggregated cell values in the cross table. When you use this option, the subtotal is calculated as the sum of the cell values, no matter expression used for the calculations of the cell values. See 'To display grand totals and subtotals as the sum of the cell values' below.

  1. Right-click the cross table to open the pop-up menu.

  2. Select Subtotals for Columns.

    Response: A submenu is opened. It lists the columns and/or hierarchies levels you can display subtotals for on the vertical axis. The options All and None are also available.

  3. Select the columns (or levels in a hierarchy) for which you want to show subtotals. Select All to display subtotals for all the columns (or hierarchical levels) on the vertical axis.

    Response: The selected subtotals are displayed after the values in question.

    Comment: You can also make these selections from the Totals page of the Cross Table Properties. There you can also specify the position of the subtotals; before or after the values.

It is possible to base totals on the aggregated cell values in the cross table. Then the totals will be calculated as the sum of the cell values, no matter expression used for the calculation of the cell values.

  1. Right-click the cross table to display the pop-up menu.

  2. Select Properties.

  3. Go to the Totals page.

  4. Beneath Display totals, select the totals to be visible in the cross table.

  5. If any subtotals are displayed, select their position, Before values or After values.

  6. Settings of calculations lists the expressions used for calculating the cell values for the different expressions on the Cell values axis, and what data the calculations are based on. Select, one at a time, each expression, whose totals you want to calculate as a sum, and then select As the sum of cell values beneath Calculate totals.

  7. Result: Any totals for the expressions selected in the last step are calculated as sums. It is also indicated in the list that they are calculated as Sum of cell values.

See Coloring in Cross Tables and Heat Maps to learn how to set up coloring for cross tables specifically. See Coloring Overview if you want to learn more about coloring in general.

You can change the width of a column containing aggregated values, and also the width of a column on the left of the aggregated values columns.

  1. For the column you want to resize, place the cursor on the column divider to the right of the column header.

  2. Response: The cursor changes appearance.

  3. Click and drag the cursor horizontally to the wanted column width. A red, vertical line indicates the width.

  4. Comment: It is possible to specify an exact width in pixels. Click the header, and in the opened pop-up menu, type the wanted value in the Column width field.

    Tip: To optimize the use of the screen estate with all content kept visible, you can double-click the column divider to adapt the column width to the content. Alternatively, click the header, and in the opened pop-up menu, select Size to fit. In a hierarchically structured cross table, this option is only available for columns at the last hierarchy level.

You can simultaneously change the column widths to the same size, or to the smallest possible widths.

  1. Place the cursor on any of the column dividers between the column headers.

  2. Response: The cursor changes appearance.

  3. Press Ctrl, and click and drag the cursor horizontally. A red, vertical line indicates the width.

  4. Response: The new column width is applied to every column in the cross table.

    Tip: To optimize the use of the screen estate for the entire cross table, you can right-click the visualization, and select Resize Columns to Fit. This action will make each column width as small as possible, that is, adjusted to the smallest size that keeps the content of the cells and the headers visible.

You can sort the aggregated values in the cross table columns as well as in the cross table rows. The sorting can be done in ascending or descending order. In hierarchically structured cross tables, the sorting of values is by default applied within each of the smallest subcategories. However, it is possible to perform a global sorting, that is, ignore any specified hierarchy when sorting. See the next procedure, 'To sort globally in the cross table'.

  1. Click the header of the cross table column or row whose values you want to sort.

  2. In the opened pop-up menu, beneath Sorting, click cross_sorting_arrow_up.png to sort from lower to higher values, or click cross_sorting_arrow_down.png to sort from higher to lower values.

    Response: The values are sorted, and an arrow in the header indicates in which order.

  3. Comment: You can click None to return to the default order. Moreover, you can press Shift + click the header to switch the sort order.

The values in a cross table can be sorted globally. It means that the values are sorted regardless of any specified hierarchy.

Note: Only the values in a cross table column can be sorted globally.

  1. Click the header of the cross table column whose values you want to sort.

  2. In the opened pop-up menu, beneath Sorting, click cross_sorting_arrow_global_up.png to sort from lower to higher values, or click cross_sorting_arrow_global_down.png to sort from higher to lower values.

  3. Response: The values  are sorted, ignoring any hierarchy. Hence the hierarchical structure can no longer be displayed. An arrow in the header indicates the sort order.

    Comment: You can click None to return to the default order. Moreover, you can press Shift + click the header to switch the sort order.

When you want to further explore the data in the cross table, it is possible to mark all cell values instantly in a cross table column or row.

  1. Click the header whose values you want to mark.

  2. Comment: You can click a header on the horizontal axis, or a header on the vertical axis.

  3. In the opened pop-up menu, select Mark values.

    Comment: Clicking a header on the lowest level in a hierarchy will mark all cell values in the column or row. Clicking a header on a higher level in the hierarchy will mark all values subordinate to the header.

  4. Comment: Instead of opening the pop-up menu, you can press Alt + click the header to mark the values.

The headers can be displayed horizontally or vertically. By changing to a vertical header orientation for narrow columns, the screen estate can be used more efficiently.

You can also specify the horizontal and vertical alignment of the text within the header. An example of a cross table with different header orientations and text alignments is shown below.

cross_table_headers.png

  1. Click the header of a column or row.

  2. In the opened menu, beneath Header, use the buttons to specify the horizontal text alignment, the vertical text alignment, and the header orientation.

  3. Response: All headers on the same hierarchical level as the clicked header are adjusted at the same time.

    Comment: By default, all headers on the same hierarchy level are adjusted. It is possible, though, to adjust only individual headers. See below.

  1. Click the header you want to adjust individually.

  2. In the opened menu, beneath Header, select the Style this individually check box.

  3. Use the buttons to specify the horizontal and vertical alignment of the text within the header, and the header orientation for this header.

  4. Response: Only the clicked header is changed.

    Comment: A selected Style this individually check box means that the alignment or orientation changes made above only affect this individual header, and adjustments made to other headers do not have an effect on this header. If you clear the check box, the header will be changed in the same way as the other headers again.

You can specify the text alignment for all cross table cells or for the cells in an individual column.

  1. Click the header of a column for which the text alignment in the table cells should be changed.

  2. In the opened menu, beneath Values, select the Align this column individually check box, if you want to adjust only the table cells in this column. If the check box is not selected, the text alignment in all cross table cells is adjusted.

  3. Click the tick mark (default), Left, Center, or Right button to specify the horizontal alignment.

  4. Response: The horizontal alignment of the table cell texts in the column or columns in question is adjusted accordingly.

See also:

What is a Cross Table?

Cross Table Properties