This topic provides an overview of cross tables and an example on how to use cross tables. This topic also describes how to configure cross table styles and how to delete a cross table. You can use cross tables to create retrieval tasks and download data of retrieval tasks.

Prerequisites

A dashboard is created. For more information, see Create a standard dashboard.

Overview

A cross table shows the sum of field values in a table and merges similar data. Data in a cross table is grouped into columns and rows. The rows are on the left side, and the columns are on the upper side. You can use aggregate operations including SUM, AVG, COUNT, MAX, and MIN to calculate data in the cell at which a column and a row intersect.

When you create a cross table, you must specify Rows and Columns.
  • You must specify dimensions for Rows.
  • You must specify measures for Columns.
Note The numbers of dimensions and measures of a cross table are not limited.

However, you can export a maximum of 10,000 rows of data. Note: The export operation is performed on your machine. Performance may vary based on the specifications of your machine and the format in which you export the data. If you want to export more than 500,000 cells, we recommend that you export the data without format or use the self-service data retrieval function.

Example

The following example shows how to use a cross table to check the packaging, order numbers, and order amounts of different products. In this example, the company_sales_record dataset is used.
  1. Perform the operations shown in the following figure to create a dashboard. Create a dashboard
  2. In the Create Dashboard dialog box, select Standard or Full Screen, and click OK.
    In this example, Standard is selected. Confirmation message
  3. On the dashboard edit page, click Division of square paper in the top menu bar and click the Cross Table icon. Cross table
  4. On the Data tab, select dimensions and measures based on your business requirements.
    • In the Dimensions list, double-click province, product_type, and product_box. You can also drag these dimensions to the Rows field.
    • In the Measures list, double-click order_number, shipping_cost, and profit_amt. You can also drag these measures to the Columns field.
  5. Click Update to update the cross table. International site

    You can click the 1 icon next to Columns to modify the display name, description, aggregation method, value display format, and text alignment mode of a measure. For more information, see Configure a chart.

  6. On the Style tab, configure parameters in the Basic Information, Display Settings, and Functionality Settings sections.

    For more information, see Configure cross table styles.

  7. On the Advanced tab, configure the following parameters. Advanced
    Parameter Option Description
    Advanced Settings Auto Refresh After you select Auto Refresh, the system refreshes the cross table as scheduled. For example, if you select Auto Refresh, enter 5, and then select Minutes, the system refreshes the cross table every 5 minutes.
    Filter Interaction After you configure Filter Interaction and click an area or field in the cross table, data in other charts that are associated with this table is updated. For more information, see Configure the filter interaction feature.
    Hyperlink After you configure Hyperlink, you are directed to the linked report when you click a field in the cross table. You can set Hyperlink to Parameter or External Link. If you select Parameter, you must configure global parameters. For more information, see Configure the hyperlink feature.
  8. Click Save in the upper-right corner of the page to save the settings.

Configure cross table styles

  • In the Basic Information section, configure Show Title and Description, Description, Endnote, and Show Link.
    Note If Show Link is selected, you also need to configure Link Text and Link Address. If you click a field in the cross table, you are redirected to the linked report or an external link.
  • In the Display Settings section, configure Custom Table Theme, Show Row Numbers, Merge Same Cells, Freeze, Wrap Text, and Pagination.
    If you select Auto (Table Head), all columns are frozen. SN
    Note If you select Merge Same Cells, Pagination is dimmed.
  • In the Functionality Settings section, configure Conditional Formatting, Sort Columns, and Show Column Totals. Configure Icon and Strip under Conditional Formatting.
    Icon
    1. In the Conditional Formatting section, configure Series.
      In this example, select profit_amt for Series, and then select Icon.
    2. Click the 1 icon to select tag icons. Cross table types
    3. Specify the values that you want to highlight, click the 1 icon, and then specify the font color and fill color for the values. profit_amt
      • If the value that you specified is greater than 1000, the cell of the value is highlighted in green and a green upward arrow appears.
      • If the value that you specified is from 500 to 1000, the cell of the value is displayed in gray and an orange hyphen appears.
      • If the value that you specified is less than 500, the cell of the value is highlighted in yellow and a red downward arrow appears.
      The following figure shows the updated cross table. Cross table

    Strip

    1. In the Conditional Formatting section, configure Series.
      In this example, select order_number for Series and select Strip. 1
    2. Configure MAX, MIN, and Fill Color.
      Note MAX and MIN can be filled automatically or manually. Wait a few seconds before the settings take effect.
      2
    Sort Columns
    1. Click the 1 icon next to Sort Columns.
    2. In the Sort Columns dialog box, configure the required parameters.
      • If you want to group the columns by type, drag the fields you need and specify a group name. 1
      • If you want to adjust the sequence of the columns, you need only to drag the fields.
      • Click Save.

    Show Column Totals

    You can configure subtotals, totals, and total functions. The total functions can be aggregate functions such as SUM, AVG, and MAX. You can also customize total functions.
    Note Before you configure subtotals, select Merge Same Cells in the Display Settings section.
    Show total

Delete a chart

  1. Click the More icon in the upper-right corner of the line chart.
  2. Select Delete. The line chart is deleted.