Quick BI:Cross Tables

Overview

• Scenarios

  • Multi-dimensional analysis: Analyze the relationship between provinces, product types, and profit amounts.

  • Decision support: Support business decisions by comparing metrics such as order amounts and shipping costs.

  • Complex calculations: Perform calculations such as period-over-period comparisons, cumulative values, or percentage rankings.

• Advantages

  • Calculation capabilities: Configure advanced calculations with a single click, such as period-over-period comparison, cumulative calculation, percentage, ranking, TopN, percentile, grand total, and subtotal.

  • Visualization: Configure the table theme, tree display mode, cell freezing, auto wrap, column width, and other display styles.

  • Notes: Add custom text, metric notes, and endnotes. You can also define external link paths to enable interaction between your data and other systems.

  • Events: Add data feedback and reporting events.

  • Conditional formatting: Use various formats, such as text, backgrounds, icons, color scales, and data bars, to make data easier to read.

  • Interactive operations: Filter dimensions or metrics, and filter data within the table.

• Example

Limits

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

• A cross table consists of Rows and Columns:

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

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

  Note

  The number of dimensions and measures for rows and columns is unlimited.

  A cross table lets you export a maximum of 10,000 rows of data. The time required to export data varies with your computer configuration and the amount of data. For more information, see Export control.

Configure Chart Data

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

• Follow these steps to add dimension fields to Rows and measure fields to Columns:

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

     • In the Dimensions list, double-click or drag Area, Product Type, and Product Package to the Rows section.

     • From the Measures list, double-click or drag Order Amount, Shipping Cost, and Profit Amount to the Columns section.

  2. Click Update. The chart automatically updates.

     

• To add dimension fields to Rows, and measure and dimension fields to Columns, perform the following steps:

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

     • From the Dimensions list, double-click or drag Product Type and Product Package to the Rows section.

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

     • In the Measures list, double-click or drag Order Amount, Shipping Cost, and Profit Amount to the Columns section.

  2. Click Update. The chart automatically updates.

     

• Follow these steps to add measure fields to Rows and dimension fields to Columns:

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

     • From the Dimensions list, drag Area, Province, and Product Type to the Columns section.

     • From the Measures list, drag Order Amount, Shipping Cost, and Profit Amount to the Rows section.

  2. Click Update to automatically update the chart.

     

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

  

Configure Data on the Data Tab

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

• Add multiple fields.

  To add multiple fields to the target area, press and hold Shift+Command and select the fields.

  

• Hide fields.

  You can hide metrics in row dimensions and column dimensions. You can quickly hide fields in four ways: hide measure fields on the editing page (①), enable field content filtering in the Filter section of the style panel (②), add a query control (③), or use a filter to filter related field data (④).

  Note:

  • If you hide a row dimension metric, it is not displayed in the chart but is used for grouping and calculations.

  • If you hide a column dimension metric, it is not displayed in the chart but is used for grand total calculations.

• Alignment.

  • For horizontal alignment, you can select align left, align center, align right, or automatic.

    

  • For vertical alignment, you can select align top, align middle, align bottom, or automatic.

    

  • Batch alignment is supported. Click the icon in the field panel. In the field configuration dialog box, select the fields you want to align and the alignment method.

• Sorting

  • For information about No Sort, Ascending, Descending, Ascending within Group, Descending within Group, Custom, Sort By Field, and Advanced Sorting, see Data sorting.

  • Sort By Field: You can sort row dimension metrics by measure or other dimensions.

    

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

      ① Select a measure that is added to the Data tab.

      ② Select a sorting method. From left to right, the sorting methods are Ascending, Descending, Ascending within Group, and Descending within Group.

      

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

      The following figure shows an example of sorting data by Profit Amount.

      

    Note

    Sort By Field is available only for dimension fields.

  • Advanced Sorting: You can use the advanced sorting feature in aggregate tables and detail tables.

    Advanced sorting is available for metrics in row dimensions and column dimensions.

    

• Aggregation: Supports Sum, Average, Count, Count Distinct, Maximum, Minimum, Population Standard Deviation, Sample Standard Deviation, Population Variance, Sample Variance, Start Time Point, and Last Time Point.

  

  • Start Time Point: You can set the aggregation method of the column field to Start Time Point and select the corresponding date field. The field will then display the aggregated data for the start time point in the cross table.

    

• Advanced calculation supports Date Cumulative, Cumulative Calculation, Percentage, Ranking, TopN, Percentile, and Period-over-period Comparison.

  

  • Date Cumulative calculates the sum of data from the initial date to the current date. For more information, see Date cumulative.

  • Cumulative Calculation performs cumulative calculations on the results returned from the data. For more information, see Cumulative calculation.

  • Percentage: You can configure data percentages as needed to view the proportion of each data item.

    In this example, you can view the proportion of order amounts in each province. The effect is shown in the following figure:

    

  • Data ranking: The grouped metrics are ranked based on their numerical values. This helps you compare data efficiently.

    For more information, see Data ranking.

    

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

    

  • Percentile calculates the percentile ranking of the results returned from the data. This helps you observe the position of a data item in the entire dataset. For more information, see Percentile.

    

  • Period-over-period comparison: You can configure period-over-period comparison for measures. For more information, see Period-over-period comparison.

    Note

    When you create a calculated field in a dataset, if the calculated field needs to be based on the result of an advanced calculation (such as a percentage calculation), you cannot directly reference the advanced calculation field to create a new calculated field. You must first manually obtain the required calculation result using a custom SQL query. Then, create a new field in the dataset to reference this calculation result.

Configure Chart Styles

This section describes how to configure the styles of a cross table. For information about how to configure common chart styles, see Configure the chart title.

You can enter keywords in the search box at the top of the configuration section to quickly find configuration items. You can also click the icon on the right to Expand/Collapse All Categories.

• In Basic table styles, set the style of the cross table.

  

  Configuration item	Description
  Custom table theme	Set the appearance style of the cross table. Theme style: You can select Default, Framed, Simple, or Minimalist. Primary color scheme: When the table theme style is Default, Framed, or Simple, you can select the primary color scheme for the cross table. You can select Follow theme color, Gray, or Custom. Show zebra stripes: Select whether to display zebra stripes in the cross table and specify the color of the stripes.
  Text	Set the text style for the cross table.
  Column width	Set the Column width method. You can select Fit to container or Custom. Note The column width settings for PC and mobile devices are independent. You can set the column width for a PC or a mobile device separately as needed.
  Auto wrap	If you select this option, the table content automatically wraps. Note Cross tables can recognize line feeds in fields. You can also use line feeds to customize the line break effect.
  Dynamic table height adaptation, Maximum height	Table height settings support two policies: "Fixed height" and "Adaptive height". Fixed height: By default, the table height uses the "Fixed height" policy. You can manually set the table container height by dragging it. Adaptive height: Enable dynamic table height adaptation to have the table container height automatically adjust based on the data content. You can also set the maximum height for the table. When the maximum height is set, the table height will not exceed the specified value during dynamic adaptation. The available maximum heights include 192 px (about 5 rows), 352 px (about 10 rows), 672 px (about 20 rows), 1632 px (about 50 rows), and Custom. Note You can click the icon at the top of the page to configure height adaptation settings for PC and mobile devices separately. On mobile devices, dynamic table height adaptation is enabled by default. You can only adjust the maximum table height. This feature may affect the overall layout of the report. Enable it as needed.

• In Cells, set the cell display, sorting methods, and other options.

  

  Configuration item	Description
  Display mode	The display mode supports Tile display and Tree display. If you select Tile display, the cross table is displayed in a tiled 2D format.
  	If you select Tree display, dimension fields are displayed in a hierarchical tree structure. You can customize the row label name and display level. Export by expanded level If you select this option, the formatted export to Excel will match the expansion and collapse status of the tree on the current page. For example, if the expanded level is set to 3, the data exported to your local computer will be the data within the 3 levels. Note If the cross table has paging set, only the current page can be displayed during export. You can resolve this by disabling paging for export or increasing the number of displayed rows. If the exported cross table does not have subtotals set, the parent level cannot display data.
  Freeze	Freeze the table header or a column. Note When you freeze a column, make sure the frozen column width is less than 60% of the total table width.
  Metric display grouping	You can set metric display grouping. After enabling Dimension/Measure mixed layout, dimension fields can also be grouped. Grouping also supports setting field descriptions.
  Merge homogeneous cells	You can choose to merge or not merge homogeneous cells.
  Auto wrap	If you select this option, the table content automatically wraps. Note Cross tables can recognize line feeds in fields. You can also use line feeds to customize the line break effect.
  Image field display mode	When there are image fields in the chart, you can set the display mode for image fields. Options include Scale to fit with original aspect ratio, Crop to fit area with original aspect ratio, and Stretch to fit area. Note You can use the Image Field option only if the dataset contains image fields of the image data type ().
  Display measure name for single-measure column	When dimensions and measures are mixed in columns, you can set whether to display the name of a single measure.
  Sorting mode	PC: Set the sorting mode of the cross table. All methods: Displays all sorting methods in a drop-down list. The list shows the configured sorting methods, and you can manually switch to other methods. Global sort only: If your business only requires global sorting, you can set the mode to Global sort only to reduce unnecessary steps. In this case, report viewers only need to click the up and down arrows to perform global ascending, global descending, and no sorting operations. Group sort only: If your business only requires group sorting, you can set the mode to Group sort only. In this case, report viewers only need to click the up and down arrows to perform group ascending, group descending, and no sorting operations. No sorting: If you select this mode, no sorting is performed.
  	Mobile: Supports All methods, Global sort only, Group sort only, and No sorting modes. The sorting logic is the same as on a PC.
  Merge row dimensions	Set whether to merge row dimensions. If selected, the cross table merges dimension information into a single column, displayed by row. You can also set the following information: Row dimension name: Enter the name for the merged row dimension. Row dimension width: Set the width of the row dimension column. You can select Auto to adapt to the system interface, or enter a custom fixed column width. Display row dimension fields in data: If selected, the names of the row dimension fields are displayed. Dimension arrangement: Set the arrangement for the merged row dimension text. You can select horizontal or portrait. Note You can configure row dimension merge information separately for PC and mobile devices by clicking the icon at the top of the page to switch between them.
  Ordinal number column	If selected, the report column header displays ordinal numbers. ① You can set the name of the ordinal number column. ② You can select Do not display ordinal numbers for total rows. Note The Do not display ordinal numbers for total rows option is available only when column totals are enabled for the cross table.
  Paginator	Click the paginator icon to activate the paginator editing page. The paginator mode is set to Normal by default. If you have a large amount of data, select Simple mode. This mode does not calculate the total number of data records. For pagination type, you can select First row dimension or All dimensions. Note When you perform a global sort, first-row pagination is automatically disabled. The supported pagination options are 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. The default initial pagination value is 20 items/page.

• In Table header, set the style of the row and column headers of the cross table.

  

  Configuration item	Configuration item content	Description
  Column header	Do not display column header	If selected, the column header content is not displayed.
  	Background fill	Set the background fill color of the column header.
  	Text	Set the text style of the column header.
  	Set alignment	Set the alignment of the column header. You can select align left, align center, or align right.
  	Custom measure header name	When there are measure fields in the row dimensions, you can configure a custom measure header name.
  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 format of the data.

    

    Configuration item	Description
    Select Field	Select the field for which you want to configure conditional formatting. You can select any field that is added to the data panel. After the style configuration is complete, if you no longer need to use certain configured conditional formats, you can click Batch clear formats in the lower-right corner of the field panel. In the selection list, select the conditional formats you want to clear and click OK.
    Quick Styles	Set the marker icon or font display color of the current field from the styles provided by the system. Quick styles can be configured only for measure fields, not for dimension fields. Comparison: You can select Compare with 0 or Compare with average. Two-color fill: Select the color of the data bar. Note Data bar length calculation method: When the maximum and minimum values are one positive and one negative, the denominator is the maximum of the absolute values of the positive and negative values. When the maximum and minimum values are both negative or both positive, the denominator is the difference between their absolute values. Color scale: You can select Gradient, Two-tier, or Three-tier. Flip colors For comparison, if values less than 0 are red and values greater than 0 are green, flipping the colors makes values less than 0 green and values greater than 0 red. Delete: When you no longer need a configured quick style, click Delete to remove it.
    Custom Style	In addition to the quick styles provided by the system, you can also configure custom styles as needed. Both measure fields and dimension fields can be configured with custom styles, but the supported style content is different. Measure fields: You can configure Highlight text/background, Icon, Color scale, and Data bar. Highlight text/background In Rule definition: Set the condition rules for highlighting the text of the current measure field. You can use the Based on field configuration item to control which data field the rule applies to. By default, it applies to the field configured in Select field. You can also select other fields here to implement 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. Icon In Rule definition: Set the icon marking rules for the current measure field. You can use the Based on field configuration item to control which data field the rule applies to. By default, it applies to the field configured in Select field. You can also select other fields here to implement 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 scale In Rule definition: Set the display rules for the color scale of the current measure field. You can set gradient colors for the color scale. In More settings: Set the shape of the color scale bar and whether to hide the text of the current field. Note You can configure a color scale only if Highlight text/background, Icon, and Data bar are not selected. Data bar In Rule definition: Set the display rules for the data bar of the current measure field. In More settings: Set whether to hide the text of the current field and customize the color of the data bar background. Set whether to hide the text of the current field. Dimension fields: You can configure Highlight Text/background and Icon. Highlight Text/background In Rule definition: Set the condition rules for highlighting the text of the current dimension field. In More settings: Set whether to hide the text of the current field. Icon In Rule definition: Set the icon marking rules for the current dimension field. In More settings: Set whether to hide the text of the current field.
    Apply conditional style to summary data	If selected, the configured conditional style also applies to summary data.
    Sync Style To	Synchronize the current conditional format to other fields.

• Under Totals/Subtotals, you can set the Column Summary and Row Summary for the dimensions as needed.

  • Column summary: Calculates summary data by column. Column summary is unavailable if the cross table contains no measures or no row dimensions.

  • Row summary: Calculates summary data by row. Row summary is unavailable if the cross table contains no measures or no column dimensions.

  

  Configuration item		Description
  Column summary	Display position	Set the display position for column grand totals/subtotals. You can place them at the bottom or top of the summary data.
  	Overall summary (column grand total)	Click the switch to enable or disable column grand totals. When enabled, it summarizes all numeric values in a column. You can also configure the following settings. Total alias: Set a display name for the grand total based on your business needs, such as "Total Sales by Region". Calculation method: Select a uniform aggregation method for all fields. If a field is a calculated field, the selected aggregation method does not take effect, and the original aggregation method is used. Style configuration: Set the text style for the grand total data. You can use the system default or customize the font color, background color, and font weight. Pin to top: Set whether to pin the column grand total data to the bottom. Note When column totals are pinned to the bottom or top, you cannot copy the summary data.
  	Categorized summary (column subtotal)	Click the switch to enable or disable column subtotals. When enabled, it calculates subtotals for a column based on the selected category dimension. You can also configure the following settings. Subtotal alias: Set a display name for the subtotal data based on your business needs, such as "Northeast Region Sales Subtotal". Summary dimension: Set the category dimension for the subtotal, such as calculating subtotals for each product type's sales. Subtotal function: Select a uniform aggregation method for all fields. If a field is a calculated field, the selected aggregation method does not take effect, and the original aggregation method is used. Add subtotal: To observe subtotal data from multiple category dimensions simultaneously (for example, observing subtotals for each product type and sales in each region), click Add subtotal to add more subtotal dimensions. Style configuration: Set the text style for the current subtotal data. You can use the system default or customize the font color, background color, and font weight.
  Row summary	Display position	Set the display position for row grand totals/subtotals. You can place them on the left or right side of the summary data.
  	Overall summary (row grand total)	Click the switch to enable or disable row grand totals. When enabled, it summarizes all numeric values in a row. You can also configure the following settings. Total alias: Set a display name for the grand total data based on your business needs, such as "Total Sales for All Products". Calculation method: Select a uniform aggregation method for all fields. If a field is a calculated field, the selected aggregation method does not take effect, and the original aggregation method is used. Style configuration: Set the text style for the current grand total data. You can use the system default or customize the font color, background color, and font weight.
  	Categorized summary (row subtotal)	Click the switch to enable or disable row subtotals. When enabled, it calculates subtotals for a row based on the selected category dimension. You can also configure the following settings. Subtotal alias: Set a display name for the subtotal data based on your business needs, such as "Northeast Region Sales Subtotal". Summary dimension: Set the category dimension for the subtotal, such as calculating subtotals for each product type's sales. Subtotal function: Select a uniform aggregation method for all fields. If a field is a calculated field, the selected aggregation method does not take effect, and the original aggregation method is used. Add subtotal: To observe subtotal data from multiple category dimensions simultaneously (for example, observing subtotals for each product type and sales in each region), click Add subtotal to add more subtotal dimensions. Style configuration: Set the text style for the current subtotal data. You can use the system default or customize the font color, background color, and font weight.

  Note

  For frequently asked questions about total configuration, see FAQ.

• In Filter

  • Enabling Field Content Filter Display lets you click the Selected Fields drop-down list on the cross table in preview mode to filter or sort fields.

    

    If you have configured a Form filling event and canceled the field bound in the form, the form filling will not work properly. For more information, see Field relationship mapping.

    • You can set default selected dimensions.

      

    • You can set the Maximum selectable dimensions.

      

      For example, if the maximum number of selectable dimensions is set to 6, you can select at most 6 dimensions for display.

      

  • After you enable Quick Filter, you can filter fields in a cross table by clicking the Filter drop-down list in preview mode.

    

    To filter multiple fields at the same time, you can add multiple conditions in the dialog box that appears.

    Scenario example: Analyze the city distribution in the South China region where the transportation cost is ＞300 and the discount point is ＞0.5.

    Note

    Filter conditions specified in preview mode are not saved by default. If you close and reopen the page, you need to re-apply or set the filter conditions.

• In Auxiliary display, you can set the Scrollbar display mode and the Scrollbar thickness.

  • If you set the display mode to Show on mouseover, the scrollbar is displayed only when you move the mouse over the table.

  • If you need the scrollbar to be always visible, select Always show.

    

  • To make the scrollbar more noticeable, you can adjust the thickness.

    

Adjust Styles on the Chart

In addition to using the style settings, you can also perform the following configurations directly on the chart:

• Click the target column in the cross table. The following configuration options appear in the drop-down list. You can configure the cross table as needed.

• Adjust the number of items per page on the cross table.

• Drag to adjust the column width on the cross table.

Configure Chart Analytics

Export Data

The export feature is available only when the export switch is turned on. For more information, see Export control.

Enable Secondary Metric Display

After you configure measure metrics, you can enable and configure secondary metrics.

After you select Enable Secondary Metric Display, the comparison metric drop-down list lets you select any measure metric.

• Comparison Date Selection: Select a date field.

  Note

  • If there is a date field in the chart, the comparison date for the secondary metric must be the date field with the finest granularity in the chart.

  • If there is no date field in the chart, the comparison date for the secondary metric can be any date field in the dataset.

• Comparison Method: The available comparison options depend on the granularity of the date field. You can select a maximum of four comparison fields. For example, if the Comparison Date is set to day granularity, the Comparison Content Selection displays Compare (day To Day), Week On Week, Month On Month, and Year On Year. If the Comparison Date is set to month granularity, the Comparison Content Selection displays Month On Month and Year On Year.

• Comparison metric selection: Select existing metrics on the dimension for configuration.

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

  • Difference: The data is displayed in the same format as the metric.

    When you select Difference, you can select Calculate pt for percentage metric difference. This is not selected by default. If you select this option and the metric uses a percentage format, the system calculates the difference in percentage points (pt). For example, 5.2% - 4.1% = 1.1 pt.

  • Difference Percentage: The value is displayed with two decimal places.

• Same-period comparison (effective only when query filter is bound to day granularity): If you configure filter conditions, the same-period comparison also includes the filter. For example, if you configure a filter condition for 2022-01-02, when calculating the year-on-year comparison, 2021 will also include the 2021-01-02 filter condition. Day-to-day comparison automatically supports same-period comparison. Dynamic period-over-period comparison does not support same-period comparison.

• When previous period data is negative: Select the formula for calculating the difference percentage. The formula you select takes effect only for data items where the previous period's data is less than 0. You can set it to:

  • (Current Period Data / Previous Period Data - 1) × 100% is the default formula.

  • 1 - (Current Period Data / Previous Period Data) × 100%

  • Display as:

• Sync comparison metric: You can synchronize the current metric's configuration to other metrics.

• Display Position: The default value is Right Of Metric. You can also select Independent Right or Below Metric.

  • Right Of Metric: Adds a new metric column to the right of the current metric. The name of the column reflects the selected comparison method, such as Year-on-Year, Day-over-Day, Month-on-Month, or Week-on-Week.

    When metrics are in columns, secondary metrics are displayed sequentially to the right of the metrics.

  • Independent Right: An independent metric displayed to the right of the current metric.

    • If a metric is in a column, the last dimension in the column is split to display day-over-day and week-over-week comparisons. As shown in the following figure, the dimension value All is split into All, All (Compare (day To Day)), and All (Week On Week). Two metrics are then displayed under each dimension value.

    • If the metric is in a row, the last row dimension is split to display Compare (day to day) and Week on week, similar to when the metric is in a column, but displayed below instead of to the right.

  • Below Metric: Adds one or more rows of comparison metrics below the current metric. The alignment of the new rows matches that of the current metric: text is left-aligned and values are right-aligned.

    It shares a row cell with the current metric, adding a row named Compare (day to day), Week on week, Month on month, Year on year, etc., below the current metric's header name, corresponding to the selected comparison method.

• Display style: Select the display color for positive and negative numbers in the table. You can choose to follow the Global semantic color settings (which follows the settings in Page settings > Global style > Semantic color), Positive numbers in red, negative numbers in green, or Positive numbers in green, negative numbers in red.

FAQ

1. FAQ about totals

Total Calculation Details

• If the original data is GMV (Sum) and the calculation method for totals and subtotals is set to Automatic, then the totals and subtotals are calculated as GMV (Sum). If you switch the total to Custom and select Average as the aggregation method, the total is calculated as GMV (Average).

• If the original field is Id (Count Distinct), and the calculation method for totals and subtotals is set to Automatic, the totals and subtotals are also calculated as Id (Count Distinct). If you switch the total calculation to Custom and select Count as the aggregation method, the total is calculated as Id (Count).

How are rates calculated?

1. Define the rate.

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

2. Create a calculated field in the dataset and enter the expression: Rate = Sum(A) / Sum(B).

3. In the data panel of the dashboard, select the Rate (Sum) metric and enable totals and subtotals. The calculation logic for Rate (Sum) defaults to Automatic, so totals and subtotals are calculated using the formula Sum(A) / Sum(B).

   Note

   If the dataset contains the expression Rate = A / B, selecting A / B (Sum) to automatically calculate totals and subtotals will produce an incorrect result, and the rate may exceed 100%.

2. Summary configuration description

For the percentage of a parent column summary to take effect, the following two conditions must be met:

• Measure fields are placed in Rows, and dimension fields are placed in Columns.

• There are two or more dimension fields.

3. How do I create a calendar in a cross table?

You can create a calendar-like visual effect by setting a conditional format with a hidden text effect for the Week number field and applying another conditional format to the Date field to display different values in different colors.