Quick BI:Cross table

Overview

• Scenarios

  • Multi-dimensional analysis: Such as analyzing the relationship between provinces, product types, and profit amounts

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

  • Complex calculation requirements: Scenarios where you need to quickly obtain period-over-period comparisons, cumulative values, or percentage rankings

• Advantages

  • Calculation capabilities: You can configure advanced calculations, such as period-over-period comparison, cumulative calculation, percentage, ranking, TopN, percentile, grand total, and subtotal with one click.

  • 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.

  • Events: You can add data feedback and 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.

• Sample Effect

Limits

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

• A cross table consists of 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.

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

Graph Data Configuration

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

• Add dimensions to Rows and measures to Columns by performing the following steps:

  1. On the Data tab, select the required dimensions and measures:

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

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

  2. Click Update. The system automatically updates the chart.

     

• Add dimensions to Rows and dimensions and measures to Columns by performing the following steps:

  1. On the Data tab, select the required dimensions and measures:

     • In the Dimensions list, find Product Type and Product Box, and double-click or drag them to the Rows section.

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

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

  2. Click Update. The system automatically updates the chart.

     

• Add measures to Rows and dimensions to Columns by performing the following steps:

  1. On the Data tab, select the required dimensions and measures:

     • In the Dimensions list, find Area, Province, and Product Type, and drag them to the Columns section.

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

  2. Click Update. The system automatically updates the chart.

     

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

  

Configure Parameters on the Data Tab

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

• Add multiple fields.

  Press Shift/command and select the target fields to add multiple fields to the target area.

  

• Hide fields.

  Dimensions and measures in both rows and columns can be hidden using four methods: hiding measure fields in the editing interface (①), enabling field content filter display in the style panel's Filter section (②), adding query controls (③), or filtering related field data using filters (④).

  Among these:

  • 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.

• Alignment.

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

    

  • Vertical alignment supports top alignment, middle alignment, bottom alignment, and automatic alignment.

    

  • Batch alignment is supported: Click the icon in the field panel, and in the field configuration dialog box, select multiple fields that need alignment and the alignment method.

• Sort

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

  • Sort By Field: You can sort dimensions in the Rows section by measure or other dimension.

    

    • 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 a sort method. From left to right, the sort methods are Ascending, Descending, Ascending within Group, and Descending within Group.

      

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

      As shown in the following figure, you can sort data by Profit Amount.

      

    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.

    

• 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. After the setting is completed, the field will display the data corresponding to the start time point through aggregation calculation 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:

    

  • 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.

    

  • 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, making it easier for users to observe the position of a data item in the entire data set. 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 creating a calculated field in a dataset, if the calculated field needs to be based on the result of an advanced calculation (such as percentage calculation), you currently cannot directly reference the advanced calculation field to create a new calculated field. You need to first manually obtain the required calculation result through custom SQL queries, and then create a new field in the dataset to reference this calculation result.

Configure the Styles of the Cross table

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 area to quickly find configuration items. You can also click on the right to expand/collapse all categories.

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

  

  Configuration item	Description
  Custom Table Theme	Set the appearance style of the cross table. Theme Style: Supports four types: Default, Frame, Simple, and Minimalist. Main Color System: When the table theme style is Default, Frame, or Simple, you can select the main color system for the cross table appearance, supporting Theme Color, Gray, or Custom. Show Zebra Stripes: Choose whether to display zebra stripes in the cross table, and the color of the zebra stripes.
  Text	Set the text style of the cross table.
  Column Width	Set the Column Width method. You can select Adapt to Container or Custom. Note The column width settings for PC and mobile terminals are independent of each other. You can set the column width for PC or mobile terminals separately based on your actual needs.
  Auto Wrap	If you select this option, the table content automatically wraps. Note Cross tables can recognize line feeds in fields. You can also customize line feed effects by using line feeds.
  Table Height Dynamic Adaptation, Maximum Height	The table height setting supports two strategies: "Fixed Height" and "Adaptive Height". Fixed Height: By default, the table height uses the "Fixed Height" strategy. You can manually set the table container height by dragging. Adaptive Height: When you enable Table Height Dynamic Adaptation, the table container height automatically adjusts based on the data content. You can also set the maximum height to which the table can adjust. When the maximum height is set, the table height will not exceed the specified value during dynamic adaptation. The available maximum heights include the following: 192px (about 5 rows), 352px (about 10 rows), 672px (about 20 rows), 1632px (about 50 rows), and Custom.The available maximum heights include the following: 192px (about 5 rows), 352px (about 10 rows), 672px (about 20 rows), 1632px (about 50 rows), and Custom. Note You can switch between PC and mobile terminal height adaptation settings by clicking the icon at the top of the page. The mobile terminal enables the table height dynamic adaptation function by default, and you can only adjust the maximum height value of the table. This feature may affect the overall layout of the report. Please choose whether to enable it based on your actual needs.

• In Cell, set the cell display and sorting methods.

  

  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 two-dimensional form.
  	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 will export 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 file will be the data within 3 levels. Note If the cross table has pagination set, only the current page can be displayed during export. You can solve this by disabling pagination for export or increasing the number of displayed rows. When the exported cross table does not have subtotals set, the parent level cannot display data.
  Freeze	Set whether to freeze the header or column. Note When setting to freeze columns, please ensure that the frozen column width is less than 60% of the total table width.
  Indicator Display Grouping	You can set indicator grouping display. After enabling dimension/measure mixing, dimension fields can also be grouped. At the same time, grouping 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 customize line feed effects by using line feeds.
  Image Field Display Mode	When there are image fields in the chart, you can set the display mode for image fields. Options include Maintain Original Aspect Ratio, Crop to Fit Area with Original Aspect Ratio, and Stretch to Fit Area. Note Only when the dataset contains image fields and the field type is image type (), can you use Image Fields.
  Display Measure Name for Single Measure Column	When dimensions and measures are mixed in columns, you can set whether to display the single measure name.
  Sort Mode	PC: You can set the sort mode of the cross table. 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. Global Sort Only: When only global sorting is needed for business meaning, to reduce unnecessary operation steps, you can set it to "Global Sort Only". In this case, report viewers only need to click the up and down arrows to perform convenient global ascending, global descending, and no sorting operations. Group Sort Only: When only group sorting is needed for business meaning, you can set it to "Group Sort Only". In this case, report viewers only need to click the up and down arrows to perform convenient group ascending, group descending, and no sorting operations. No Sort: When you select the no sort mode, no sorting will be performed.
  	Mobile: Supports All Methods, Global Sort Only, Group Sort Only, and No Sort modes, and the sorting logic is the same as on PC.
  Merge Row Dimensions	Set whether to merge row dimensions. When selected, the cross table will merge dimension information into one column, displayed by row. At the same time, you can 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: When selected, the row dimension field names will be displayed. Dimension Arrangement: Set the arrangement method for row dimension text after merging, supporting horizontal or vertical. Note You can configure row dimension merge information separately for PC and mobile terminals by clicking at the top of the page to switch between them.
  Sequence Column	When selected, the report column header will display sequence numbers. ① You can set the sequence column name. ② You can select Do not display sequence numbers for total rows. Note The option Do not display sequence numbers for total rows is only available when the cross table has column totals enabled.
  Paginator	Click the paginator icon to page. The paginator mode is set to Normal by default. When the data volume is large, it is recommended to select Simple mode, which does not calculate the total number of data records. Pagination type supports First Row Dimension and All Dimensions. Note When you perform global sorting, first row pagination will automatically be 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 Header, set the style of the row and column headers of the cross table.

  

  Configuration item	Content	Description
  Column Header	Do not display column header	When selected, the column header content will not be 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. Supports left alignment, center alignment, and right alignment.
  	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 Format, set the conditional format of the data.

    

    Configuration item	Description
    Please 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 area. After the style configuration is completed, when you no longer need to use certain configured conditional formats, you can click Batch Clear Format in the lower right corner of the field panel, select the conditional formats you want to clear from the selection list, and click OK to complete the format clearing.
    Quick Style	Set the marker icon or font display color of the current field from the styles provided by the system. Quick styles can only be configured for measure fields, not for dimension fields. Comparison: You can select Compare with 0 or Compare with Average. Dual-color Fill: You can 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 the absolute values. Color Scale: You can select Gradient, Two-level, or Three-level. Color Flip For example, in comparison, less than 0 is red and greater than 0 is green. After flipping, less than 0 becomes green and greater than 0 becomes red. Clear: When you no longer need to use a quick style that has been set, click Clear to remove the style.
    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: Supports configuring Highlight Text/Background, Icon, Color Scale, and Data Bar. Highlight Text/Background In Rule Definition: Set the condition rules for displaying the current measure field text as highlighted. You can use the Based on Field configuration item to control which data field the rule applies to. By default, it displays the field configured in Please 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 displays the field configured in Please 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 Color scale can only be configured when 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: Supports configuring Highlight Text/Background and Icon. Highlight Text/Background In Rule Definition: Set the condition rules for displaying the current dimension field text as highlighted. 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.
    Conditional Style Applies to Summary Data	When selected, the set conditional style will also apply to summary data.
    Sync Style To	You can synchronize the conditional format that you specified to other fields.

• In Total/Subtotal, set Column Summary and Row Summary for the dimensions as needed.

  Column totals can be fixed at the bottom or fixed at the top.

  

  Note

  • When column totals are set to be fixed at the bottom or top, you cannot copy the total column data.

  • For frequently asked questions about total configuration, see FAQ.

• In Filter

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

    

    When you have configured a Form Filling event and canceled the field bound in the form filling 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 number of selectable dimensions.

      

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

      

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

    

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

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

    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.

• In Auxiliary Display, you can set the scrollbar display mode and the thickness of the scrollbar.

  • When you set the display mode to Show on Mouse Hover, the scrollbar is only displayed when you place the mouse on the table.

  • When you need the scrollbar to be always visible, you can select Always Show.

    

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

    

Modify the Styles in a Cross Table

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

• Click the target column in the cross table, and the following configuration options appear in the dropdown box. You can configure the cross table as needed.

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

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

Configure Chart Analysis

Export Data

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

Display Secondary Indicators

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

After selecting Display Secondary Indicator, the comparison indicator selection dropdown box allows you to select all measure indicators.

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

  Note

  • If there is a date field in the chart, the secondary indicator comparison date can only select the date field with the smallest granularity in the chart.

  • If there is no date field in the chart, the secondary indicator comparison date can select any date field in the dataset.

• Comparison Method: Based on the granularity of the date field, the corresponding comparison content will be displaye maximum of 4 comparison fields. For example, if Comparison Date selects day granularity, then Comparison Content Selection displays Compare (Day To Day), Compare (of Last Week), Compare (of Last Month), and Compare (of Last Year). If Comparison Date selects month granularity, then Comparison Content Selection displays Compare (Month To Month) and Compare (of Last Year).

• Comparison Indicator Selection: Select indicators that exist in the dimension for configuration.

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

  • Difference: The data display format matches the indicator display format.

    When you select Difference, you can select Calculate pt for Percentage Indicator Difference: This is not selected by default. When selected, if the indicator uses a percentage display format, the system automatically calculates the difference value in percentage points. For example, 5.2% - 4.1% = 1.1pt.

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

• Same Period Comparison (Only effective when query filter is bound to day granularity): If you configure filter conditions, the comparison with the same period will also include the filter. For example, if you configure a filter condition for 2022-01-02, when calculating the year-over-year comparison, 2021 will also include the 2021-01-02 filter condition. Compare (Day to Day) 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 calculation formula you select will only take effect for data items where the previous period data < 0. You can set it to:

  • (Current Period Data/Previous Period Data-1)*100%: Default value

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

  • Display As -

• Sync Comparison Indicator: You can synchronize the current indicator configuration to multiple indicators.

• Display Position: The default is Right Of Indicator, but Independent Right and Below Indicator are also supported.

  • Right Of Indicator: Adds a new column of indicators to the right of the current indicator. The indicator column name is Compare (of Last Year), Compare (Day to Day), Compare (of Last Month), Compare (of Last Week), etc., which reflects the selected comparison method.

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

  • Independent Right: Exists independently to the right of the current indicator.

    • If the indicator is in the column, the last column dimension will be split to display Compare (Day to Day) and Compare (of Last Week). As shown in the figure below, the dimension value All will be split into All, All (Compare (Day To Day)), All (Compare (of Last Week)), and two indicators will be displayed under each dimension value.

    • If the indicator is in the row, the last row dimension will be split to display Compare (Day to Day) and Compare (of Last Week), similar to when the indicator is in the column, but from the right side to below.

  • 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.

    Shares a row cell with the current indicator, adding a row named Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), Compare (of Last Year), etc., below the current indicator's header name, which corresponds to the selected comparison method.

• Display Style: Select the display color of positive and negative numbers in the table. You can choose to follow the Global Semantic Color Settings (follow 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 Description

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

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

How to Calculate a Rate

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 define the expression as: rate=sum(A)/sum(B).

3. In the data panel of the dashboard, select the Rate (Sum) metric and select totals and subtotals. The calculation logic defaults to automatic Rate (Sum), so the calculation logic for totals and subtotals is sum(A)/sum(B).

   Note

   If the expression written in the dataset is rate=A/B, and you directly check the automatic calculation logic for totals and subtotals as A/B (Sum), abnormal situations where the rate exceeds 100% may occur, and the calculation result will be incorrect.

2. Summary configuration description

For the percentage of 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 to create a calendar in a cross table?

You can set conditional formatting with hidden text effects for the Week Number field, and apply conditional formatting to the Date field to display different values in different colors for similar visual effects.