All Products
Search
Document Center

Quick BI:Cross Table

Last Updated:Apr 29, 2024

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 such as SUM, AVG, COUNT, MAX, and MIN to calculate data in the cell at which a column and a row intersect. This topic describes how to add data to a cross table and configure the style.

Prerequisites

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

Overview

  • Scenarios

    Cross tables are used to analyze the relationships between multiple dimensions and measures to help decision-making.

  • Benefits

    • Computing capabilities: You can configure advanced computing capabilities such as month-on-month comparison, cumulative calculation, percentage, ranking, top N, percentile, and total subtotals.

    • Data visualization: You can configure the table theme, tree display mode, cell freezing, auto line wrap, column width, and other display styles for cross tables.

    • Interactive options: You can add custom remarks or comments to cross tables. You can also add links to cross tables to interact with external systems.

    • Event capability: Data feedback reporting events.

    • Conditional formatting: You can use various formats, such as font colors, background colors, icons, color scales, and data strips, to improve user experience.

    • Data filtering: You can filter dimensions or measures to create cross tables, or filter data that is displayed in cross tables.

  • Example效果示意图

Create a cross table

A cross table consists of rows and columns. You can specify dimensions or measures for rows and columns.

  • Specify dimensions for Rows and measures for Columns.

    1. On the Data tab, select measures and dimensions based on your requirements.

      • In the Dimensions list, double-click Area, Product Type, and Product Box. You can also drag these dimensions to the Rows area.

      • In the Measures list, double-click Order Amt, Shipping Cost, and Profit. You can also drag these measures to the Columns field.

    2. Click Update to create the LBS heat map.

      p587715

  • Specify dimensions for Rows, and specify measures and dimensions for Columns.

    1. On the Data tab, select measures and dimensions based on your requirements.

      • In the Dimensions list, double-click product_type and product_box. You can also drag the dimensions to the Rows section.

      • In the Dimensions list, drag province to the Columns section.

      • In the Measures list, double-click Order Amt, Shipping Cost, and Profit. You can also drag these measures to the Columns field.

    2. Click Update to create the LBS heat map.

      p587817

  • Specify measures for Rows and dimensions for Columns.

    1. On the Data tab, select measures and dimensions based on your requirements.

      • In the Dimensions list, drag area, province, and product_type to the Columns field.

      • In the Measures list, find Order Amount, Shipping Cost, and Profit Amount and drag them to the Lines field.

    2. Click Update to create the LBS heat map.

      p588250

Note

The numbers of dimensions and measures of a cross table are not limited.

You can export up to 10,000 rows of data from a cross table. The period of time that is required to export the data varies based on the configurations of your on-premises machine and the size of the data you want to export. For more information, see Export data.

  • In the Filter section, set filter conditions. For more information, see Filter by field.

    p606996

Configure parameters on the data tab

On the Data tab, you can perform the following operations:

  • Add multiple fields.

    Click Shift/command to add fields to the target area.

    p737449

  • Hide fields.

    Dimensions and measures that are specified in the Rows section or Columns section can be hidden.

    • If you hide a dimension or measure in the Rows field, this dimension or measure is not displayed in the table. The values of the dimension or measure can be used for calculations and grouping.

    • If you hide a dimension or measure in the Columns field, this dimension or measure is not displayed in the table. The values of the dimension or measure can be used to calculate grand totals.

  • The alignment mode.

    • Horizontal alignment supports setting left alignment, center alignment, right alignment and automatic.

      p607275

    • Vertical alignment supports setting top alignment, center alignment, bottom alignment and automatic.

      p607277

  • Sort data.

    • You can sort global data or group data in ascending order, descending order, or custom order. You can also sort data by field or by using advanced settings. For more information, see Sort data. You can determine whether to sort data based on your business requirements.

    • Sort By Field: You can sort dimensions by measure or by another dimension.

      image

      • You can sort data based on a measure that you added on the Data tab.

        (1) Select a measure that is added to the Field tab.

        (2) Select one of the following sorting orders: ascending, descending, ascending in the group, and descending in the group.

        image

      • You can sort data by measure or dimension in your dataset.

        As shown in the following figure, you can sort by profit amount.

        p737453

      Note

      Sort By Field is available only for dimensions.

    • Advanced Sorting: You can use the Advanced Sorting feature in aggregate tables and item tables.

      Advanced Sorting is available for dimensions in the Rows section and Columns section.

      image

  • Aggregate calculation: supports sum, average, count, deduplication, maximum, minimum, population standard deviation, sample standard deviation, population variance, sample variance, start time point, and last time point.

    image.png

    • Start Time: You can set the Aggregate Calculation Method parameter to Start Time and select a date field. After you set this parameter, the field is aggregated and displayed as the data at the start time in the cross table.

      image.png

  • Advanced computing, which supports date cumulative, cumulative, percentage, ranking, top N, percentile, and same-period comparison.

    p607283

    • For more information about date rollup, see Date rollup.

    • The cumulative calculation is used to accumulate the results returned from the data. For more information, see Cumulative calculation.

    • Percentage: You can set Percentage to view the percentage of each data.

      In this example, view the proportion of the order amount in each province. The following figure shows the effect:

      交叉表-占比.gif

    • Ranking: The grouped metrics are ranked based on their numerical values. This way, you can compare data in an efficient manner.

      For more information, see Data ranking.

      p607300

    • TopN: The top N data records are retained. For example, if you select Top5, the top 5 data records are retained. You can also customize the ranking method and the number of N data records. For more information, see TopN.

      交叉表-topn.gif

    • Percentile calculation is used to rank the results returned from data in the form of percentiles. You can view the position of a piece of data in a group of data. For more information, see Percentiles.

      image.png

    • Same-month comparison: You can configure the same-month comparison for the measure. For more information, see Same-month comparison.

Configure the styles of the cross table

The following section describes how to configure the styles of an LBS heat map. For information about the common style settings of charts, see Configure the chart title.

You can enter a keyword in the search box at the top of the configuration section to search for configuration items. You can click the image.pngicon to expand or collapse all categories.

p737459

  • In the Table Basic Style section, configure the style of the cross table.

    p746014

    Chart configuration items

    Description

    Custom Table Theme

    The theme of the table. You can select Zebra Crossing, Wireframe, Simplified Edition, or Simplified Edition. You can select Theme Color, Gray, or Custom.

    Text

    Set the cross table text style.

    Column Width

    Set the Column Width parameter. You can select Adaptive by Container or Custom.

    Enable

    If you select this check box, the table content supports automatic line wrapping.

  • In the Cell section, configure the display and sorting methods.

    p646575

    Chart configuration items

    Description

    Display mode

    The theme of the table. You can select Zebra, Wireframe, Simplified, or Simplified. You can select Theme, Gray, or Custom from the Main Color drop-down list. p225337

    Freeze

    Specifies whether to freeze the table header or column.

    Metric Display Group

    You can configure Metric Group Display. After you enable Mixed Dimension /Measure, you can also group the fields of the dimension. At the same time, the group supports setting the field description.

    Merge Same Cells

    You can choose to merge or not merge similar cells.

    p737461

    Display the metric name in a single metric column

    You can specify whether to display a single measure name when the dimensions and measures in a column are mixed.

    Sorting method

    You can set the Sort Mode of a cross table.

    image

    • All Sorting Methods: displays all sorting methods in the All Sorting Methods drop-down list. You can manually change the sorting methods.

      排序-全部

    • Global Sort Only: If you want to click a column header to perform global sorting on a field, you can select Global Sort Only. After you select the check box, click the icon imageto the right of the column header to select the ascending or descending order.

      排序-全局

    Merge Row Dimension

    You can specify Merge Row Dimensions. If you select this check box, the cross table combines the dimension information into one column and displays it by row.

    • PC

      You can specify the row dimension name, row dimension column width, and whether to display row dimension fields in data.

      image.png

    • Mobile platform

      You can specify the row dimension name, row dimension column width, and whether to display row dimension fields in data.

      image.png

      Note

      When the width of the cross table is more than half of the width of a mobile terminal screen, the cross table will be displayed across the screen.

    Serial Number Column

    After you select the check box, the report column header displays the serial number. p703809

    ① You can set the name of the serial number column.

    ② You can select Do Not Display Serial Number in Total Rows.

    Note

    You can select Do Not Display Sequence Number in Total Rows only if you select Total Column Timing for the cross table.

    Pager

    Click Pager icon to activate the Pager Edit page.

    • By default, the Pager Mode parameter is set to regular Mode. If the data volume is too large, we recommend that you select Simple Mode. In this mode, the total number of data records is not calculated.

      image.png

    • You can select First Row Dimension or All Dimensions.

      Note

      When you perform global sorting, the first-row pagination automatically becomes invalid.

    • You can select 10, 20, 30, 50, 80, 100, 200, or 500 entries per page.

      image.png

  • In the Header section, configure the style of the row header in the crosstab.

    image..png

    Parameter

    configuration items Content

    Description

    List Header

    Do not display the list header

    If you select this check box, the contents of the list header will not be displayed.

    Background Color

    Sets the fill color of the list header background.

    Text

    Sets the list header text style.

    Set the alignment mode

    Specifies the alignment of the head of the list. The left alignment, center alignment, and right alignment are supported.

    Custom Measure Header Name

    If a measure field exists in the row dimension, you can configure a custom measure header name. p755748

    Row Header

    Background Color

    Sets the row header background fill color.

    Text

    Sets the row header text style.

  • In the Conditional Formatting section, configure conditional formats for data. The following table describes the parameters.

    image..png

    Chart configuration items

    Description

    Series

    You can select the fields that you added on the Data tab.

    Shortcut Style

    You can select the marker icons or font display colors.

    Custom Style

    You can configure the Text/Background, Icon, Color Scales, and Strip.

    Conditional styles take effect on summary data

    If you select this check box, the specified condition style also takes effect on the summary data.

    Synchronize Style To

    You can synchronize the conditional format that you specified to other fields.

    image..png

    Chart configuration items

    Description

    Series

    You can select the fields that you added on the Data tab.

    Shortcut Style

    You can select the marker icons or font display colors.

    Custom Style

    You can configure the Text/Background, Icon, Color Scales, and Strip.

    Conditional styles take effect on summary data

    If you select this check box, the specified condition style also takes effect on the summary data.

    Synchronize Style To

    You can synchronize the conditional format that you specified to other fields.

  • In the Total /Subtotal section, set Column Summary and Row Summary in the Total /Subtotal section. Column Summary and Row Summary.

    Column totals support pinned at the bottom or pinned at the top. 总计..gif

For the FAQ about calculating the totals, see FAQ about totals.

  • In Filtering

    • After you select Field Content Filtering, you can click the Selected Fields drop-down list in the preview to filter or sort fields.

      拖动交叉表1

      If the Form Filling event is configured and the fields that are bound to the form are canceled, the form filling cannot be used. For more information, see Field mapping.

      • Supports setting the default selected dimension. p708705

      • You can set the Maximum Dimension parameter. p708706

        For example, if you set Maximum Available Dimensions to 6, you can select a maximum of six dimensions for display. p737462

    • After you select Enable Shortcut Filter, you can click the Filter drop-down list in the preview state to filter fields.

      更新图表-合4.2需求

      If you want to filter multiple fields, you can add multiple conditions in the dialog box that appears after you click Filter.

      Example: The following figure shows how to filter cities for which the shipping_cost value is greater than 300 and the back_point value is greater than 0.5 in the South China region. 4.2

      Note

      If you close the editing page and then reopen the page, the filter conditions that you specified in the preview mode cannot be saved.

      更新图表-合4.2需求

      If you want to filter multiple fields, you can add multiple conditions in the dialog box that appears after you click Filter.

      Example: The following figure shows how to filter cities for which the shipping_cost value is greater than 300 and the back_point value is greater than 0.5 in the South China region. p737464

      Note

      If you close the editing page and then reopen the page, the filter conditions that you specified in the preview mode cannot be saved.

    For the FAQ about calculating the totals, see FAQ about totals.

  • In the Auxiliary Display Filter section, you can set the Display Mode and ScrollBar Threshold parameter.

    • If you set Display Mode to Select, the scroll bar is displayed only when you move the pointer over the table.

    • If you want the scroll bar to remain on, you can select Resident Display.

      滚动条显示

    • If you want to make the scroll bar more visible, you can adjust the thickness of the scroll bar.

      image

Modify the styles in a cross table

You can also perform the following operations to change the styles of a cross table:

  • Click the column that you want to use to sort the data. The following configuration options are displayed in the drop-down list. Configure the sorting order based on your business requirements. 界面配置_4.3.2

  • Change the number of data records that are displayed on each page. 修改条数_4.3.2

  • Click and drag the boundaries of a column to change the column width.

Chart analysis configuration

Parameter

Data type

Specification

Data interaction

Drill

If you have set the drill-down field in the Fields panel, you can set the drill-level row display style here.

Filter Interaction

After you configure Linkage and click an area or field in the cross table, data in other charts or tables that are associated with this table is updated. For more information, see Configure the filter interaction feature.

Hyperlink

After you configure Hyperlink, you are redirected 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.

Advanced Settings

Event

If you select Events, you can click a field value to add data. If a column dimension exists, you can only add a row dimension to configure events. For more information, see Create an event to add data.

Display Secondary Indicator

If you select Display Secondary Indicator, you can configure the display modes of secondary indicators. For more information, see Display secondary indicators.

Export data

The export control feature is supported only when you turn on Export. For more information, see Export control.

p607105 (1)

Parameter

Parameter description

Export Name

You can specify a custom name for the exported file.

Data files for indexing

You can export a file in one of the following formats: EXCEL, Image, and PDF.

Note

If you want to export an EXCEL file, you can set Cross Table Data Type to Export with Format or Pure Data.

  • If you want to export more than 50,000 cells of data, we recommend that you use Pure Data.

  • If you want to export data as an EXCEL file, only the first 10,000 records can be exported.

Organization administrators can configure the export format of data. For more information, see Configure the export feature.

Watermarking

This parameter is required only when you set File Format to EXCEL. You can specify whether to display watermarks based on your business requirements.

Export Channel

You can select Local to export data to your on-premises machine. You can also export data to the Object Storage Service (OSS) server.

Display secondary indicators

After you configure measures, you can configure secondary indicators.

After you select Display Secondary Indicator, you can select measures from the Select Secondary Indicator drop-down list.

  • Comparison Date: Select a time field from your dataset.

  • Select Field to Compare: The comparison fields are displayed based on the granularity of the time field. Up to four comparison fields can be displayed. For example, if you select a day field for Comparison Date, you can set Select Field to Compare to Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year). If you select a month field for Comparison Date, you can set Select Field to Compare to Compare (Month to Month) or Compare (of Last Year).

  • Show: You can select Percent Variance, Variance, or Original Value. The default value is Percent Variance.

    • Variance: The data display format is the same as the format of the metric.

    • Percent Variance: A percentage is displayed by using two decimal places.

  • Calculate the Difference Between Percentages: By default, this parameter is not selected. Unit: pt. If you select Variance and choose Percent Variance as the comparison indicator after you select Calculate the Difference Between Percentages, pt is automatically displayed. Example: 5% - 4% = 1 pt.

  • Display Location: You can select Right Side of Indicator, Right, or Below Indicator. The default value is Right Side of Indicator.

    • Right Side of Indicator: You can add a new column on the right side of the metric that you selected for comparison. The column header is named based on the selected comparison method. The name can be Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year).

      If the metric that you selected for comparison is a column header, the secondary indicator is displayed on the right side of the selected metric. 1

    • Right: The secondary indicator is separately displayed on the rightmost side of all metrics.

      • If the metric that you selected for comparison is a column header, the last column dimension is split to display the results of Compare (Day to Day) and Compare (of Last Week). In the following figure, All is used as an example to show a split column dimension. In this example, All is split into All (Day to Day) and All (of Last Week).14

      • If the metric that you selected for comparison is a row header, the last row dimension is split to display the results of Compare (Day to Day) and Compare (of Last Week). This usage scenario is similar to the usage scenario in which the selected metric is a column header. The difference between the scenarios is that the secondary indicator is displayed below the selected metric, instead of on the right side of the metric.

    • Below Indicator: You can add multiple columns of secondary indicators below the metric that you selected for comparison. The alignment of the added indicators is the same as the alignment of the select metric. The text is aligned to the left and the values are aligned to the right.

      The added indicators share the same cell as the selected metric. You can add a row below the row of the selected metric. The row is named based on the comparison method. The name can be Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year). 1

  • Display Style: The display style is determined based on the interface language of your on-premises machine. If you use Chinese, select Positive num show red and negative num show green. If you use English, select Positive num show green and negative num show red. After you configure the display style, the configured color remains unchanged regardless of the language.

  • Synchronous Comparison Indicator: You can synchronize the configurations of the secondary indicators to multiple metrics.

Limits

When you create a cross table, you must configure Rows and Columns.

  • You must specify dimensions for Rows, such as province and product_type.

  • You must specify measures for Columns, such as order_number and profit_amt.

Note

The numbers of dimensions and measures of a cross table are not limited.

You can export up to 10,000 rows of data from a cross table. The period of time that is required to export the data varies based on the configurations of your on-premises machine and the size of the data you want to export. For more information, see Export data.

FAQ about totals

Usage notes:

  • If your original data is GMV (SUM), the default calculation method is automatic. In this case, the logic that is used to calculate totals and subtotals is GMV (SUM). If you change the calculation method from automatic to custom and select AVG as the aggregate method, the logic that is used to calculate totals is GMV (AVG).

  • If your original data is id (COUNT (DISTINCT)), the default calculation method is automatic. In this case, the logic that is used to calculate totals and subtotals is id (COUNT (DISTINCT)). If you change the calculation method from automatic to custom and select COUNT as the aggregate method, the logic that is used to calculate totals is id (COUNT (DISTINCT)).

What is the logic that is used to calculate the rate?

  1. Define the rate.

    In this example, the rate is calculated based on the following formula: Rate = Metric A/Metric B.

  2. Add a calculation field to your dataset and define the expression as rate = sum(A)/sum(B). 3

  3. On the Data tab, select rate (SUM). In the Totals section, select Column Totals and Column Subtotals, set Calculation Method to automatic, and then use rate (SUM) as the default calculation logic. The logic that is used to calculate totals and subtotals is sum(A)/sum(B).

    Note

    For example, if your expression is rate=A/B, and you select Column Totals and Column Subtotals, set Calculation Method to automatic, and use A/B (SUM) as the calculation logic. In this case, the calculation result cannot be trusted because the value of the calculated result may be more than 100%.