All Products
Search
Document Center

Quick BI:Cross Table

Last Updated:Jan 20, 2025

A cross table displays summary values for specific fields within a table and merges homogeneous data. It features one set of columns on the left and another set at the top. At the intersection of rows and columns, various summary calculations can be performed on the data, including sum, average, count, maximum, and minimum. This topic describes how to add data and configure styles for a cross table.

Prerequisites

A dashboard has been created. For more information, see Create a Dashboard.

Limits

A cross table consists of Rows and Columns:

  • Rows are determined by the data's dimensions, such as province and product type.

  • Columns are determined by the data's measures, such as order quantity and profit amount.

Note

There is no limit to the number of values for dimensions and measures in a cross table's rows and columns.

A cross table can export up to 10,000 rows of data. Export time may vary based on computer configuration or data volume. For more information, see Export Control.

Chart Overview

  • Scenarios

    Suitable for scenarios that require multidimensional and multi-metric cross-analysis, facilitating decision-making through the cross-analysis of multiple metrics.

  • Advantages

    • Computing power: Enables advanced calculations such as year-on-year, cumulative, percentage, ranking, TopN, percentile, grand total, and subtotal with a single click.

    • Visualization effects: Offers table themes, tree display, auto wrap, column width adjustment, and more.

    • Annotation capability: Allows for customizable text, metrics, and endnote information, along with external link paths for data-system interaction.

    • Event capability: Supports data feedback reporting events.

    • Conditional formatting: Enhances readability with formats such as text, background, icons, color scales, and data bars.

    • Interactive operations: Includes dimension/metric filtering and in-table filtering.

  • Example of Effects效果示意图

Chart Data Configuration

A cross table is composed of rows and columns. You can add dimension fields and measure fields to both.

  • To add dimension fields to Rows and measure fields to Columns, follow these steps:

    1. On the Data tab, select the desired dimension fields and measure fields:

      • In the Dimension list, locate Area, Product Type, and Product Package, and double-click or drag them to the Row area in sequence.

      • In the Measure list, locate Order Amount, Transportation Cost, and Profit Amount, and double-click or drag them to the Column area in sequence.

    2. Click Update. The system will automatically refresh the chart.

      image

  • To add dimension fields to Rows and both measure and dimension fields to Columns, follow these steps:

    1. On the Data tab, select the required dimension fields and measure fields:

      • In the Dimension list, locate Product Type and Product Package, and double-click or drag them to the Row area in sequence.

      • In the Dimension list, locate Province and drag it to the Column area.

      • In the Measure list, locate Order Amount, Transportation Cost, and Profit Amount, and double-click or drag them to the Column area in sequence.

    2. Click Update. The system will automatically refresh the chart.

      image

  • To add measure fields to Rows and dimension fields to Columns, follow these steps:

    1. On the Data tab, select the required dimension fields and measure fields:

      • In the Dimension list, locate Area, Province, and Product Type, and drag them to the Column area in sequence.

      • In the Measure list, locate Order Amount, Transportation Cost, and Profit Amount, and drag them to the Row area in sequence.

    2. Click Update. The system will automatically refresh the chart.

      image

Note

There is no limit to the number of values for dimensions and measures in a cross table's rows and columns.

A cross table can export up to 10,000 rows of data. Export time may vary based on computer configuration or data volume. For more information, see Export data.

  • Set filter conditions in Filter. For more information, see Field Filtering.

    image

Data Panel Configuration

The following operations can be performed on the data panel:

  • Adding Multiple Fields

    Press Shift/Command while clicking the desired fields to select multiple fields for the target area.

    Untitled.gif

  • Hide fields. Dimensions and measures in both row and column dimensions support hiding. Specifically:

    Both row and column dimensions can hide dimensions and measures, including the following:

    • Hiding a dimension or measure in the Rows field will remove its display from the table, but its values can still be used for calculations and grouping.

    • Hiding a dimension or measure in the Columns field will remove its display from the table, but its values can still be used to calculate grand totals.

  • Alignment options include horizontal alignment with align left, align center, align right, and automatic settings.

    • Horizontal alignment options include align left, align center, align right, and automatic alignment.

      image

    • Vertical alignment options include align top, align middle, align bottom, and automatic alignment.

      image

  • Sort

    • Options include no sort, ascending, descending, ascending within group, descending within group, custom, sort by field, and advanced sort. For more information, see Data Sorting.

    • Sort by field: Row dimensions can be sorted by measures or other dimensions.

      image

      • You can sort data using a measure from the data panel.

        (1) Choose a measure from the Field tab.

        (2) Select the desired sorting method, such as ascending, descending, ascending within group, or descending within group, from left to right.

        image

      • Data can be sorted by either measure or dimension in your dataset.

        For instance, you can sort by Profit Amount as illustrated below.

        Untitled.gif

      Note

      Note: Sort by field is only available for dimensions.

    • Advanced sorting is typically used in aggregate and detail tables.

      Both row and column dimensions support advanced sorting for dimensions and measures.

      image

  • Aggregation Calculation: This feature supports a variety of methods including sum, average, count, remove duplicates, maximum, minimum, population standard deviation, sample standard deviation, population variance, sample variance, along with calculating start and end times.

    image..png

    • Start Time: You can configure the column field's aggregation calculation to Start Time by selecting the appropriate date field. Once configured, the field will display the start time data in the cross table as a result of the aggregation calculation.

      image..png

  • Advanced calculations in ECS include date cumulative, cumulative calculation, percentage, ranking, TopN, percentile, and day-to-day comparison.

    image

    • Date cumulative calculation sums data from the start date to the present. For more information, see Date Cumulative.

    • Cumulative calculation adds results sequentially from the data set. For more information, see Cumulative Calculation.

    • Percentage allows you to configure the data's percentage to view each data point's proportion.

      In this example, you can view the proportion of order amounts by province, as shown below:

      Untitled.gif

    • Data ranking orders grouped metrics by their numerical values, facilitating data size comparison.

      For more information, see Data Ranking.

      image

    • TopN retains the highest-ranked data up to a specified number, such as the top 5. You can customize both the ranking method and the number N. For more information, see TopN.

      Untitled.gif

    • Percentile calculation ranks data points within the entire data set, making it easier to determine their relative position. For more information, see Percentile.

      image

    • Day-to-day comparison allows you to compare measures across different days. For more information, see Compare (day to day).

Chart Style Configuration

The following describes the style configuration of the chart. For general style configuration of charts, see Configure the Chart Title.

You can quickly find configuration items for configuration by entering keywords in the search box at the top of the configuration area. At the same time, click the right image..png to support the operation of expand/collapse all categories in the chart.

Untitled.gif

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

    image

    Chart Configuration Items

    Configuration Item Description

    Custom Table Theme

    Set the table theme. Four types are supported: Zebra, Wireframe, Simple, and Minimal. In the main color scheme, you can choose Theme Color, Gray, and Custom.

    Text

    Set the text style of the cross table.

    Column Width

    Set the Column Width method. Two methods are supported: Adaptive to Container and Custom.

    Note

    The column width settings for PC and mobile are independent. You can set the column width for PC or mobile separately based on actual conditions.

    Auto Wrap

    After checking, the table content supports auto wrap.

    Note

    The cross table supports recognizing line feeds in fields. You can also customize the line wrapping effect through line feeds.

    Dynamic Adaptive Table Height, Maximum Height

    Table height settings support two strategies: "Fixed Height" and "Adaptive Height".

    • Fixed Height: The table height defaults to the "Fixed Height" strategy. You can manually set the table container height by dragging. 111

    • Adaptive Height: Enable dynamic adaptive table height. The table container height can automatically adjust according to the data content. At the same time, you can set the maximum adjustable value of the table height. After setting the maximum table height, the table height will not exceed the set value during dynamic adaptation.

      Supports the following maximum heights: 192 px (approximately 5 lines), 352 px (approximately 10 lines), 672 px (approximately 20 lines), 1632 px (approximately 50 lines), and custom.111

    Note
    • You can configure the height adaptation settings for PC and mobile separately by switching the image icon at the top of the page.

    • The mobile terminal defaults to enabling the dynamic adaptive table height function. Only the maximum table height value can be adjusted.

    • This function may affect the overall layout of the report. Please choose whether to enable it based on actual needs.

  • In Cell, set the display and sorting methods of cells.

    image

    Chart Configuration Items

    Configuration Item Description

    Display Method

    The display method supports Tile Display and Tree Display.

    • After selecting Tile Display, the cross table will be presented in a tiled two-dimensional form.

      image

    • After selecting Tree Display, the Dimension fields can be displayed in a tree hierarchy.

      image

    • Support custom Row Label Name and Display Level.

      Untitled

    • Export by Expanded Level

      After checking, the formatted export will be exported to Excel according to the tree expansion and collapse status of the current page at the time of export.

      For example, when the expanded level is set to 3 levels, the actual data exported to the local is the data within 3 levels. image

      Note
      • If the cross table is set with Paging, only the current page can be displayed during export. You can close paging export or increase the number of display rows to solve this.

      • If the exported cross table does not have Subtotal set, the parent level cannot display data.

    Freeze

    Set whether to freeze the Table Header or Column.

    Metric Display Grouping

    Support setting Metric Group Display. After enabling Dimension/Measure Mixed Layout, dimension fields can also be grouped. At the same time, grouping supports setting field descriptions.

    Merge Similar Cells

    You have the option to merge homogeneous cells or leave them separate.

    Untitled.gif

    Display Measure Name in Single Measure Column

    Support setting whether to display a single measure name when dimensions and measures are mixed in the column.

    Sorting Mode

    PC:

    Support setting the Sorting Mode of the cross table.

    image

    • All Methods: Display All Sorting Methods in a Dropdown form. The list shows the sorting methods that have been set, and you can manually switch to other methods.

      Untitled

    • Only Global Sorting: When only Global Sorting is needed in terms of business meaning, to reduce unnecessary operation steps, you can set it to "Only Global Sorting". At this time, report visitors only need to click the up and down arrows image to perform convenient Global Ascending, Global Descending, and No Sorting operations.

      Untitled

    • Only Group Sorting: When only Group Sorting is needed in terms of business meaning, you can set it to "Only Group Sorting". At this time, report visitors only need to click the up and down arrows image to perform convenient Group Ascending, Group Descending, and No Sorting operations.

      Untitled

    • No Sorting: After selecting the no sorting mode, sorting will no longer be performed.

    Mobile:

    Support All Methods, Only Global Sorting, Only Group Sorting, and No Sorting methods, and the sorting logic is the same as that of the PC. Untitled

    Merge Row Dimensions

    Support setting whether to Merge Row Dimensions. After checking, the cross table will merge dimension information into one column and display it by row.

    • PC

      Support setting row dimension name, row dimension column width, and whether to display row dimension fields in the data.

      image.png

    • Mobile

      Support setting row dimension name, row dimension column width, and whether to display row dimension fields in the data.

      image.png

      Note

      When the width of the cross table exceeds half of the mobile screen, it will be displayed in two screens.

    Ordinal Number Column

    After checking, the report column header will display the ordinal number.

    image.png

    (1) Support setting the ordinal number column name.

    (2) Support checking Do Not Display Ordinal Number in Total Row.

    Note

    Only when the cross table checks the column total, can you check Do Not Display Ordinal Number in Total Row.

    Pager

    After clicking the pager icon, the pager editing page is activated.

    • The pager mode defaults to Regular mode. When Data Volume is Large, it is recommended to choose Simple mode. In this mode, the total number of data items will not be calculated.

      image.png

    • The paging type supports selecting First Row Dimension and All Dimensions.

      Note

      When you perform global sorting, the first row paging will automatically become invalid.

    • The supported paging display is 10 items/page, 20 items/page, 30 items/page, 50 items/page, 80 items/page, 100 items/page, 200 items/page, and 500 items/page.

      image.png

  • In Table Header, set the style of the cross table row list header.

    image..png

    Configuration Items

    Configuration Item Content

    Configuration Item Description

    List Header

    Do not display list header

    After checking, the list header content will not be displayed.

    Background Fill

    Set the background fill color of the list header.

    Text

    Set the text style of the list header.

    Set Alignment

    Set the alignment of the list header, supporting align left, align center, and align right.

    Custom Measure Header Name

    When there are measure fields in the row dimension, support configuring custom measure header names.

    image

    Row Header

    Background Fill

    Set the background fill color of the row header.

    Text

    Set the text style of the row header.

    • In Conditional Formatting, set the conditional formatting of the data.

      image

      Chart Configuration Items

      Configuration Item Description

      Select Field

      Select the field that needs conditional formatting configuration. All fields added to the data panel area can be selected.

      After completing the style configuration, if you no longer need to use certain configured conditional formats, you can also click the Batch Clear Format operation in the lower right corner of the field panel, select the conditional formats that need to be cleared in the candidate list, and click OK to complete the format clearing. image

      Quick Style

      Set the mark icon or font display color of the current field from the styles provided by the system. Only measure fields support configuring quick styles. Dimension fields do not support configuring quick styles.

      image

      • Comparison: Support selecting Compare with 0 and Compare with Average.

      112

      • Two-color Fill: Support selecting the color of the data strip.

        112

        Note

        Data strip length calculation method: When the maximum and minimum values are one positive and one negative, the denominator is the maximum absolute value of the positive and negative values. When the maximum and minimum values are both negative or both positive, the denominator is the difference of the absolute values.

      • Color Scale: Support selecting Gradient, Two-stage, and Three-stage.

        112

      • Color Flip

        Take comparison as an example. Less than 0 is red, greater than 0 is green. After flipping, less than 0 is green, greater than 0 is red.

        112

      • Clear: When you no longer need to use a set quick style, click Clear to remove the style. 112

      Custom Style

      In addition to the quick styles provided by the system, you can also configure custom styles as needed. Measure fields and dimension fields can both configure custom styles, but the supported style content is different.

      • Measure Fields: Support configuring Highlight Text/Background, Icon, Color Scale, and Data Strip.

        • Highlight Text/Backgroundimage

          • In Rule Making: Set the condition rules that make the current measure field text display as highlighted. You can control which data field the rule applies to through the Based on Field configuration item. By default, it displays the field configured in Select Field. You can also select other fields here to achieve more complex condition rules based on the values of other fields.

          • In More Settings: Set whether to hide the text of the current field and whether to apply the background color to the entire row.

        • Iconimage

          • In Rule Making: Set the icon marking rules for the current measure field. You can control which data field the rule applies to through the Based on Field configuration item. By default, it displays the field configured in Select Field. You can also select other fields here to achieve more complex condition rules based on the values of other fields.

          • In More Settings: Set whether to hide the text of the current field.

        • Color Scaleimage

          • In Rule Making: Set the display rules for the color scale of the current measure field.

          • In More Settings: Set the shape of the color scale bar and whether to hide the text of the current field.

            Note

            Only when highlight text/background, icon, and data strip are not selected, can you select and configure the color scale.

        • Data Stripimage

          • In Rule Making: Set the display rules for the data strip of the current measure field.

          • In More Settings: Set whether to hide the text of the current field.

      • Dimension Fields: Support configuring Highlight Text/background and Icon.

        • Highlight Text/backgroundimage

          • In Rule Making: Set the condition rules that make the current dimension field text display as highlighted.

          • In More Settings: Set whether to hide the text of the current field.

        • Iconimage

          • In Rule Making: Set the icon marking rules for the current dimension field.

          • In More Settings: Set whether to hide the text of the current field.

      Conditional Styles Apply to Summary Data

      After checking, the set conditional styles will also apply to summary data.

      Synchronize Style To

      Synchronize the current conditional format to other fields.

  • In Total/Subtotal, set Column Total and Row Total.

    Column total supports Fixed at Bottom or Fixed at Top.

    总计..gif

    For frequently asked questions about total configuration, see Total Configuration FAQ.

  • In Filter

    • After selecting Field Content Filter Display, in preview mode, you can click the Selected Field dropdown list on the cross table to filter or sort fields.

      拖动交叉表1

      When a Form Filling event is configured and the fields bound in the form filling are canceled, the form filling cannot be used normally. For details, see Field Relationship Mapping.

      • Support setting default selected dimensions.

        image.png

      • Support setting Maximum Number of Selectable Dimensions.

        image.png

        For example, when the maximum number of selectable dimensions is set to 6, you can select up to 6 dimensions for display.

        image.png

    • After selecting Enable Quick Filter, in preview mode, you can click the Filter dropdown list on the cross table to filter fields.

      image.png

      When you need to filter multiple fields at the same time, you can add multiple conditions in the dialog box that appears to complete the field filtering.

      Scenario example: Analyze the distribution of cities in the South China region where the transportation cost is >300 and the discount point is >0.5. Untitled.gif

      Note

      The filter conditions specified in preview mode are not saved by default. When the page is closed and reopened, you need to re-filter or set the filter conditions.

  • In Auxiliary Display, support setting Scrollbar Display Method and Scrollbar Thickness.

    • When you set the display method to Mouse Hover Display, the scrollbar is displayed only when you place the mouse in the table.

    • When you need the scrollbar to be always on, you can choose Permanent Display.

      滚动条显示

    • When you need to make the scrollbar more obvious, you can adjust the thickness of the scrollbar.

      image

Modify the Styles in a Cross Table

In addition to configuring the styles in the style settings, you can also perform the following configurations on the cross table:

  • Click the target column on the cross table. A dropdown box with configuration options will appear, allowing you to customize the cross table as needed. 界面配置_4.3.2

  • Adjust the number of pages in the cross table. 修改条数_4.3.2

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

Chart Analysis Configuration

Configuration Items

Name

Description

Data Interaction

Drilling

When you configure the drilling function of the chart, you can configure the display style of the drilling level row. For more information, see Drilling.

Filter Interaction

When the data that you want to analyze belongs to different charts, you can configure this parameter to associate the charts, and then analyze the data. For more information, see Filter Interaction.

Hyperlink

When the data that you want to analyze belongs to different dashboards, you can configure this parameter to associate the dashboards, and then analyze the data. Hyperlink includes Parameter Hyperlink and External Link. For more information, see Hyperlink.

Advanced Settings

Events

If you select Events, you can click a field value to add data. Only row dimensions can be added to configure events when column dimensions exist. For more information, see Events.

Display Secondary Indicator

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

Export Data

The export function is available only when the export switch is enabled. For more information, see Export Control.

image

Parameter Items

Parameter Item Description

Export Name

You can specify a custom name for the exported file.

File Format

Support exporting in Image, PDF, and EXCEL formats.

Note

When exporting a cross table, if the file format you export is EXCEL, you can choose the cross table data type as Formatted Export or Raw Data.

  • When the number of cells exceeds 50,000, it is recommended to choose the Raw Data type.

  • Only the first 10,000 data records can be exported if you want to export an EXCEL file.

Organization administrators can configure the export format of data. For more information, see Export Control.

Watermark

This option needs to be configured only when the file format is EXCEL. You can choose whether to display the watermark based on your needs.

Export Channel

Support exporting to Local or OSS service.

Display Secondary Indicator

After configuring measures, you have the option to configure secondary indicators.

If you select Display Secondary Indicator, the comparison indicator selection dropdown box will allow you to select all measure indicators.

  • Comparison Date Selection: You must select a date field in the dataset.

  • Comparison Method: The comparison content will be displayed based on the granularity of the date field, showing up to 4 comparison fields (e.g., if the Comparison Date is selected as daily granularity, the Comparison Content Selection will display Compare (day To Day), Week On Week, Month On Month, Year On Year; if the Comparison Date is selected as monthly granularity, the Comparison Content Selection will display Month On Month, Year On Year, etc.).

  • Display Content: By default, Difference Percentage is selected. You can also check Difference Value or Original Value.

    • Difference Value: The data display format is consistent with the indicator's display format.

    • Difference Percentage: The display format retains two decimal places.

  • Percentage Difference Calculation Pt: By default, it is not checked. After selecting this option, if the user selects the difference value and the selected comparison indicator is a percentage indicator, it will automatically display as pt, for example, 5%-4%=1pt.

  • Display Position: By default, Right Side Of Indicator is selected. It also supports Independent Right Side and Below Indicator display.

    • Right Side Of Indicator: Adds a new column indicator on the right side of the current indicator. The indicator column name is year-on-year, compare (day to day), month-on-month, week-on-week, etc., which is the selected comparison method.

      When the indicator is on the column, the secondary indicator is displayed on the right side of the indicator in sequence. 1

    • Independent Right Side: Exists independently on the right side of the current indicator.

      • If the indicator is on the column, the last column dimension will be split to display compare (day to day), week-on-week. As shown in the following figure, the dimension value All will be split into All, All (compare (day To Day)), All (week-on-week). Two indicators will be displayed under each dimension value. 14

      • If the indicator is on the row, the last row dimension will be split to display compare (day to day), week-on-week. In the case of the same indicator on the column, it changes from the right side to the bottom.

    • Below Indicator: Adds multiple rows of comparison indicators below the current indicator. The alignment is consistent with the indicator's alignment, with text left-aligned and values right-aligned.

      Share a row of cells with the current indicator. Add a row named compare (day to day), week-on-week, month-on-month, year-on-year, etc., below the current indicator's header name, which is the selected comparison method. 1

  • Determine the default value based on the current environment. If it is Chinese, select red for positive and green for negative. If the current environment is English, select green for positive and red for negative. After selecting, the color of the report will not change regardless of the environment it is switched to, and the color of the configuration item will prevail.

  • Synchronize Style To: You can synchronize the configuration of the current indicator to display on N indicators.

FAQ about Totals

Total Calculation Description

  • If the user's original data is GMV (sum), the calculation method of the total and subtotal defaults to Automatic. The calculation logic of the total and subtotal is GMV (sum). If the user switches the total to Custom and selects Average as the aggregation method, the calculation logic of the total is GMV (average).

  • If the user's original data is Id (remove Duplicates), the calculation method of the total and subtotal defaults to Automatic. The calculation logic of the total and subtotal is Id (remove Duplicates). If the user switches the total to Custom and selects Count as the aggregation method, the calculation logic of the total is Id (count).

What Is The Calculation Logic If The User Wants To Calculate The Rate?

  1. Define the rate.

    In this example, the rate is defined as (Metric A/Metric B).

  2. Create a calculated field in the dataset and define the expression as: rate=sum(A)/sum(B). 3

  3. In the data panel of the dashboard, select the Rate (sum) indicator, and select total and subtotal. The calculation logic defaults to automatic Rate (sum). The calculation logic of the total and subtotal is sum(A)/sum(B).

    Note

    If the expression written by the user in the dataset is rate=A/B, directly selecting total and subtotal automatic calculation logic as A/B (sum) will result in an abnormal situation where the rate is greater than 100%. In this case, the calculation result is incorrect.