This topic describes how to create a cross table. Make sure that you have read Dashboard overview and Basic dashboard operations. For creating a dataset, see Create a dataset.

Overview

A cross table displays aggregates and sub-aggregates of columns and groups columns. Aggregates include sums, averages, maximums, and minimums.

A cross table consists of rows and columns. Rows are horizontal and based on dimensions such as provinces and product types. Columns are vertical and based on measures such as order numbers and profit amounts.

Examples



Notes

Numbers of rows and columns are not limited.

Scenarios: packages, shipping costs, order numbers, and profit amounts of multiple products based on provinces

The following example uses the company_sales_record dataset.

  1. Log on to the Quick BI console.
  2. Click Datasets to go to the Datasets page.
  3. Select the company_sales_record dataset and click the Create Dashboard icon in the Actions column.
    Note If you use Quick BI Enterprise Standard, you need to choose Standard or Full Screen as the display mode manually. The following example uses Standard.
  4. Click the Cross Table icon and an empty chart appears on the dashboard.
  5. On the Data tab page, select dimensions and measures.
    In the Dimensions list, locate the province, product_type, and product_box fields and add them to the Rows section. In the Measures list, locate the order_number, shipping_cost, and profit_amtand add them to the Columns section.
    Note Make sure that you have converted the dimension type of the province field from String to Geo.


  6. Click Update and the chart is updated.
  7. On the Style tab page, you can configure the chart title, layout, format, and rules.


  8. Click Save to save the dashboard.

Configure the style

  • In the Basic Settings section, you can configure the title and hyperlinks for page jumping as shown in the following figure.

  • In the Display Settings section, you can configure the table theme, choose whether to show row numbers and whether to merge duplicate cells, configure freezing rules, and set pagination. The updated chart is shown as follows.

  • In the Functionality Settings section, you can configure conditional formatting rules and aggregate rules.
    Conditional formatting
    • Select a field and select the Enable Conditional Formatting check box as shown in the following figure.


    • Click the arrow. From the drop-down list, you can select the field that you want to perform conditional formatting for.


    • Enter the beginnings and ends of intervals in the Value fields. Click the arrows and select text colors, background colors, and icons.


    Select the profit_amt field for conditional formatting. The following formulas are used to determine which format to apply. Value ≥ 1000, Value < 1000 and ≥ 500, and Value < 500. The updated chart is shown as follows.

    • When a value is greater than or equal to 1000, the background color of the cell is set to red and a green up arrow is displayed.
    • When a value is greater than or equal to 500 and less than 1000, the background color of the cell is set to grey and an orange flat line is displayed.
    • When a value is less than 500, the background color of the cell is set to green and a red down arrow is displayed.


    Sort Columns

    By using the sort columns feature, you can group related fields and set group names. If you do not set groups, dragging and dropping fields only modifies the sequence of columns.

    Show Totals

    By using the show totals feature, you can configure the aggregates of column values as shown in the following figure.
    Note You need to select the Merge Same Cells check box in the Display Settings section before selecting the Show Subtotals check box.


  • In the Series Settings section, you can rename fields and set alignments.

Delete a chart

Click the More Actions icon in the upper-right corner of the chart and select Delete from the drop-down list to delete a chart.