Quick BI:Cross Table

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.

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.

     

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

     

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

     

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

  

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.

  

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

    

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

    

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

    

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

      

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

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

      

    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.

    

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

  

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

    

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

  

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

    

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

    For more information, see Data Ranking.

    

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

    

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

    

  • 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 to support the operation of expand/collapse all categories in the chart.

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

  

  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. 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. Note You can configure the height adaptation settings for PC and mobile separately by switching the 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.

  

  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.
  	After selecting Tree Display, the Dimension fields can be displayed in a tree hierarchy. Support custom Row Label Name and Display Level. 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. 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.
  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. 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. 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 to perform convenient Global Ascending, Global Descending, and No Sorting operations. 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 to perform convenient Group Ascending, Group Descending, and No Sorting operations. 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.
  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. Mobile Support setting row dimension name, row dimension column width, and whether to display row dimension fields in the data. 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. (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. 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.

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

  

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

    

    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.
    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. Comparison: Support selecting Compare with 0 and Compare with Average. Two-color Fill: Support selecting the color of the data strip. 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. 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. Clear: When you no longer need to use a set quick style, 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. 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/Background 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. Icon 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 Scale 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 Strip 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/background 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. Icon 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.

  

  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.

    

    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.

      

    • Support setting Maximum Number of Selectable Dimensions.

      

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

      

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

    

    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.

    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.

    

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.

• Adjust the number of pages in the cross table.

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

Chart Analysis Configuration

Export Data

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

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.

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

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

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