Quick BI:Perform Operations on Tables in an Ad Hoc Analysis

Operations supported after you right-click measure fields

The following table describes the operations that you can perform after you right-click one or more measure fields. The operations that you can perform vary based on the number of measure fields that you select.

• Sorting

  You can sort a single measure field in an ad hoc query.

  • In-group sorting is supported, such as the ascending order of profit amount in the following figure.

  • Supports global sorting.

  After you configure sorting, if you click Save or Save Release to open the ad hoc analysis page again, the original sorting content is still retained.

• Retain Only

  If you click Retain Only for a measure field, only data of the selected measure field are retained in the table. The data of other measure fields are deleted.

  If the table contains a large number of measures and you only want to view the order quantity and order amount, you can select to retain only these two measures.

• Suppress

  For more information about how to suppress the selected measures, see Suppress.

• Calculate

  • Ratio of numerical value and ranking of numerical values

    If you right-click a measure and select it, you can perform numeric proportion and ranking operations.

  • Percentage and basic operations

    When you right-click and select two measures, you can perform Percentage, Variance Percentage, and Quadruple operations. The calculation formulas are displayed in the selected order.

  • Total, average, maximum, and minimum

    Right-click and select more than or equal to three dimensions or measures to calculate the sum, average, maximum, and minimum values.

  • Custom calculation

    You can also use custom calculations to write field expressions.

    Important

    The measure fields that you reference in a custom calculation must be the measure fields that you drag to the table.

    

• Display Format

  After a table is generated, if you want to change the display format of a measure field, you can right-click the measure field and click Display Format.

  Ad hoc analysis provides several common display formats. You can select one from the drop-down list.

  The ad hoc query feature also allows you to specify a display format. You can configure the Format Type, Decimal, Magnitude, and Prefix parameters to specify the display format.

  For example, if you want to set the unit price to two decimal places, in the order of tens of thousands, and use the prefix of ¥, you can directly set the corresponding format in the custom display format configuration box.

  After the customization takes effect:

• Remove Measure

  You can right-click a measure field and select Remove Measure to delete the measure field that you select from the table.

Operations supported after you right-click dimension values

You can perform the following operations after you right-click one or more dimension values. The operations that you can perform vary based on the number of dimension values that you select.

• Hidden

  You can hide dimension values in ad hoc queries if you do not want to view the related data. You can also cancel the hide operation to show the dimension values that you hid.

  If you hide a dimension value, a blue line appears on the position in which the hidden value is located. If you move the pointer over the blue line, the message "Data in xx rows or columns is hidden." appears. You can click the blue line to show the hidden values.

  For example, in the following figure, the three dimension values in the product subcategory with the advanced level are hidden. Click the hidden blue line again to unhide them.

• Retain Only

  After only some dimension values are retained, other dimension values are deleted, and the total is calculated as the total of the retained content.

• Exclude

  If you select Exclude from the shortcut menu, Subtotal (Included) and Subtotal (Excluded) are automatically displayed in the table. The subtotal (included) is the sum of the values left after the exclusion, and the subtotal (excluded) is the sum of the excluded values.

  For example, after the air transport dimension value is excluded, the subtotal (included) and subtotal (excluded) are displayed in the table, which are the sum of the remaining calories and trains, and the value of the excluded air transport, respectively. If you do not want to display Subtotal (Included) and Subtotal (Excluded), you can click the Custom Subtotal icon, find the dimension value that you excluded, and then click Subtotal (Included) and Subtotal (Excluded).

• Suppress

  For more information about how to suppress the selected row or column of dimension values, see Suppress.

• Calculate

  Dimension values can also be calculated, and the calculation result is inserted into the row below the selected dimension values.

  • Percentage and basic operations

    When you right-click and select two dimension values, you can perform Percentage, Variance Percentage, and Quadruple operations. The calculation formula is displayed in the selected order.

  • Total, average, maximum, and minimum

    Right-click and select more than or equal to three dimension values to quickly calculate the sum, average, maximum, and minimum values.

  • Custom calculation

    You can also use custom calculations to write field expressions.

    Important

    The dimension values that you reference in a custom calculation must be the dimension values that are dragged to the table in the same dimension field.

    

• Remove Dimension Value

  You can delete a dimension field or dimension values in the dimension field from the table to remove the dimension field or dimension values.

Header Operations

You can click a column in the table header to perform global sorting.