All Products
Search
Document Center

Quick BI:Text Query

Last Updated:Jan 20, 2025

The filter bar supports querying data using a text field. This topic explains how to set up a filter bar for text field-based data queries.

Prerequisites

  • A dashboard has been created with charts that include text fields.

  • A filter bar is present on the dashboard. For more information, see Create a Filter Bar.

Configure a Filter

  1. For detailed instructions, see Create a Dashboard and navigate to the dashboard editing page.

  2. On the Dashboards page, locate and click the name of the desired dashboard.

    Ensure the charts on the dashboard include text fields. For more information, see Line Chart.

    image.png

  3. Add a filter bar as illustrated below.

    image.png

  4. Set up the filter bar. image.png

    1. Name the filter Level Query.

    2. Select the Line Chart and the field Order Level to associate.

    3. In the Basic Configuration section, choose the Display Type and set the parameters accordingly.

      1. For Display Type as Drop-down List, refer to Drop-down List.

      2. For Display Type as Text Input Box, refer to Text Input Box.

      3. For Display Type as Tree Drop-down, refer to Tree Drop-down.

  5. Click OK.

Drop-down List

The drop-down list display type enables report creators to define a set of predefined options, offering a clear selection list for report viewers. When the display type is Drop-down List, the source for option values can be Automatic Parsing, Single Dataset, or Manual Input.

image

Automatic Parsing

With automatic parsing, Quick BI automatically detects and aggregates all related field values to populate the filter bar options. image

Note
  • If the chart in the Associated Chart And Field area uses the same dataset, automatic parsing will pull values directly from that dataset.

  • If the chart in the Associated Chart And Field area spans multiple datasets, automatic parsing will compile values from these datasets, resulting in a drop-down list that reflects multiple datasets.

Configuration item

Description

Example

Query Mode

Supports Single Selection and Multiple Selection.

image

  • Query mode: Select Multiple selection.

  • Query time: Select Click to query.

  • Set default filter value: Set the order level to Advanced, province to Guangdong, and product type to Furniture product.

Query Time

Supports Click To Query and Pre-query.

  • Click To Query: Default value. When you click the query button, a conditional query is triggered.

  • Pre-query: Pre-query the content values of the drop-down list before opening the dashboard. This allows the drop-down list values to be displayed according to the configuration of row-level permissions for each account, and the default value for each person will also vary based on the corresponding row-level permissions configuration.

    Pre-query is suitable for scenarios where the target dataset is configured with row-level permissions and the data volume is small. Otherwise, it will affect the overall report loading time.

Set Default Filter Value

If you select this option, the query condition box displays Please select. Click the query condition box to configure default values in the Field pop-up window. image

You can perform the following operations in this window:

  • Enter a keyword in the search box and click the keyword below to add the target field value on the left to the Added list on the right.

  • Click Add All Field Values On The Left to add all field values on the left to the Added list on the right.

  • Select the field values on the left to add the selected field values to the Added list on the right.

  • Click Exclude to exclude the field values that are added to the Added list on the right.

    Note
    • If you select a field value and select Exclude in the Field pane and then change Field style to Tile, the field value appears on the filter bar. This indicates that the field value is excluded from the query result.

    • If you set Field style to Tile and then select a field value and select Exclude in the Field pane, the field value appears with a strikethrough on the filter bar.

  • Click Clear to clear the field values that are added to the Added list on the right.

Note

If the number of field values is greater than 1,000, the first 1,000 field values are displayed by default. You can directly search for the field values that are not displayed.

To summarize, in this example:

  • The Associated Chart and Field area selects fields from various datasets, resulting in a drop-down list that displays a combined set of all related field values.

  • The Query Mode is set to Multiple Selection, allowing the filter bar to support the selection of multiple options simultaneously.

  • The Query Time is set to Click to Query, requiring the report viewer to initiate a conditional query by clicking the query button.

  • The Default Filter Value is preset to Advanced, Guangdong, and Furniture Product, ensuring that upon opening the report, it will automatically display query results for these default parameters.

The resulting effect is as follows: 112

Single Dataset

When a single dataset is the source for option values, the filter bar's options will be based on the values from a specific field within that dataset.

image

Configuration item

Description

Example

Select Dataset

You can select all datasets in the space where the current dashboard is located. It is recommended to select the dataset that is the data source of the chart. image

image

  • Select dataset: In this example, select the dataset as the data source: company_sales_record.

  • Query value field: Order level.

  • Display name field: Order level.

  • Sort field: Order date.

  • Query mode: Select Multiple selection.

  • Query time: Select Click to query.

  • Set default filter value: Select this option and set the default value to Advanced.

Query Value Field

The field in the source dataset that is used to match the associated field. This field is used for the actual query. The report viewer can select one or more options from the values of this field to query data.

Display Name Field

The field that is displayed as the option value in the filter bar. This field is usually used to improve the user experience of the filter bar and help the report viewer better understand the meaning of the option values.

  • Same as the query value field: When the value of the query value field is clear and easy to understand, you can set the display name field to be the same as the query field. In this case, after selecting the query value field, the system will automatically fill in the display name field, and you do not need to perform additional operations.

  • Different from the query value field: When the value of the query value field is a numeric code or a code that is not easy to understand, you can set an easy-to-understand display name field in the data table to be used as the display option value in the filter bar (the actual query is still based on the query value field). In this case, after selecting the query value field, you need to manually modify the display name field.

Sort Field

Supports sorting based on the selected field. This parameter is optional. If you do not specify this parameter, the data sequence remains the same as that in the dataset by default.

If you need to set sorting based on a specific field, you can select the sort field in the drop-down list (for example, sort by measure or by date order). The sorting method supports Ascending, Descending, and Custom sorting. When you select custom sorting, you can organize the order of field values by dragging in the Custom sorting window. 111

After the settings take effect, they can affect the sorting of the drop-down list in the filter bar, but do not affect the sorting of dimension values in the chart. The sorting of dimension values in the chart is still implemented through the dataset or the data panel in each chart.

Query Mode

Supports Single Selection and Multiple Selection.

Query Time

Supports Click To Query and Pre-query.

  • Click To Query: Default value. When you click the query button, a conditional query is triggered.

  • Pre-query: Pre-query the content values of the drop-down list before opening the dashboard. This allows the drop-down list values to be displayed according to the configuration of row-level permissions for each account, and the default value for each person will also vary based on the corresponding row-level permissions configuration.

    Note

    Pre-query is suitable for scenarios where the target dataset is configured with row-level permissions and the data volume is small. Otherwise, it will affect the overall report loading time.

Set Default Filter Value

If you select this option, the values that are preconfigured are displayed in the drop-down list.

Note

If the number of field values is greater than 1,000, the first 1,000 field values are displayed by default. You can directly search for the field values that are not displayed.

  • When the query time is set to Click to query, you can configure default filter values.

    image

  • When the query time is Pre-query, it supports Manual setting and Dynamic function (the dynamic function is supported only when the query value field and the display name field are the same). Pre-query is suitable for scenarios where data needs to be retrieved in advance, such as displaying different filter options based on each visitor's row-level permissions.

    image

    The dynamic function supports setting Maximum query field value, Minimum query value field, and First query value field.

    Note

    The First query value field takes the first value of the query value field in the Dataset. If the field is sorted in the dataset (ascending, descending, custom sorting, etc.), it takes the first value according to this sorting and is not affected by the Sort field option value source above. image.png

In summary, in this example:

  • The Dataset is the recommended system dataset company_sales_record, which also serves as the data source for the current chart.

  • The Query Value Field corresponds to the associated field, meaning the Order Level is used as the option value for the query.

  • The Display Name Field matches the query value field, indicating both reference the Order Level.

  • The Sort Field is determined by the Order Date, which sorts the control option values accordingly.

  • The Query Mode is set to Multiple Selection, permitting the report viewer to choose several option values in the filter bar.

  • The Query Time is set to Click to Query, necessitating a click on the query button by the report viewer to initiate a conditional query.

  • The Set Default Filter Value is Advanced, meaning the report will automatically show query results for the order type as Advanced when first opened.

The specific effect is as follows:

112

Manual Input

For manual input as the source of option values, the report creator customizes the options in the filter bar based on selected field values in the chart.

image

Configuration item

Description

Example

Manual Entry

Click Manual entry and enter Query Value and Display Name in the Manual entry window. image

  • Query value: The value of the field selected in the chart. For example, in this example, the selected field is order level, and the corresponding values are Advanced, Intermediate, Low, and Others.

  • Display name: The text displayed in the drop-down list corresponding to the query value.

Note
  • When performing manual entry for the first time and the query value is the same as the display name, you only need to enter the query value and click OK, and the system will automatically enter the display name.

  • When performing manual entry for the first time and the query value is different from the display name, you need to manually enter the query value and display name, and ensure that the number of rows for the query value and display name is consistent and can correspond one-to-one.

  • If it is not your first time to manually enter values, you must manually enter values in the Query value and show name sections. Only entering the query value will receive the following prompt.

    image.png

image

  • Manual entry: In this example, the field values and their corresponding display names are as follows: Advanced - A level; Intermediate - B level; Low - C level; Others - D level.

  • Query mode: Multiple selection

  • Set default filter value: Select this option and set the default value to A level (i.e., Advanced).

Query Mode

Supports Single Selection and Multiple Selection.

Set Default Filter Value

If you select this option, the values that are preconfigured are displayed in the drop-down list.

In summary, in this example:

  • Manual entry refers to the mapping of order level field values to their display values: Advanced to A level, Intermediate to B level, Low to C level, and Others to D level.

  • The Query Mode is set to Multiple Selection, enabling the report viewer to select multiple options simultaneously.

  • The Set Default Filter Value is Advanced, ensuring that the report initially displays query results for the order type as Advanced.

The specific effect is as follows:

112

Text Input Box

The Text Input Box display type enables report viewers to swiftly retrieve the data they need by typing keywords into the query box, enhancing the precision and flexibility of data queries. When set to Text Input Box, the conditional form accommodates Single Condition, Or Condition, and And Condition. An example of the Or Condition is provided below.

image

Configuration item

Description

Example

Conditional Form

Supports Single Condition, Or Condition, and And Condition

  • Single condition: Satisfies a single condition. For example, the product type is a ruler.

  • Or condition: Satisfies any one of two conditions. For example, the product type is a ruler or a pencil.

  • And condition: Satisfies both conditions simultaneously. For example, the order amount is greater than 100 and less than 200.

image

  • Conditional form: Select Or Condition.

  • Set default value: Set the character to include as a ruler, or the character Starting With as Table.

  • Lock filter condition: Select this option.

Set Default Value

The values vary based on the selected conditional form.

For example, to set a default value for the Product type field, the rules and corresponding SQL syntax relationships are as follows:

  • Exact match: product_sub_type=‘Ruler

  • Not match: product_sub_type!=‘Ruler

  • Contains: product_sub_type like ‘%Ruler%’

  • Does not contain: product_sub_type not like '%Ruler%'

  • Starting with: product_sub_type like 'Ruler%'

  • Ending with: product_sub_type like '%Ruler'

  • Is empty: product_sub_type is null

  • Is not empty: product_sub_type is not null

  • Empty text: product_sub_type=''

  • Non-empty text: product_sub_type!=''

Lock Filter Condition

After enabling, the conditional form cannot be switched on the preview page. image

Note

This option appears only when Set Default Value is selected.

To summarize the example:

  • The Conditional form is configured for Or Condition, allowing the display of any data that satisfies at least one of the specified query conditions.

  • The Set default value option is configured to Contains the term Ruler, or the Starting with condition is set to Table. This indicates that upon opening the report, the viewer will by default see query results for product types that either include the term Ruler or begin with the term Table.

  • The Lock filter condition option is enabled, preventing report viewers from changing the conditional form on the report's filter bar.

The resulting effect is as shown:

112

Tree Drop-down

The tree drop-down display type enables report viewers to filter data within a parent-child hierarchy by selecting from a drop-down list, such as choosing a business area along with its subordinate provinces and cities. image

Configuration item

Description

Example

Select dataset

You can select all datasets in the space where the current dashboard is located. It is recommended to select the dataset that is the data source of the chart. image

image

  • Select dataset: In this example, select the current chart source dataset company_sales_record.

  • Tree structure design: Configure the area field for level one and the province field for level two.

  • Display form: Select Tile display.

  • Query mode: Select Multiple selection.

  • Query time: Select Click to query.

  • Set default value: Set the default value to Northeast > Liaoning.

  • Quick association: Select quick association.

Tree structure design

Click Start configuration and configure the Query field and Display field for each level in the Tree structure design window. image

  • Query field: This field is used for the actual query. The report viewer can select one or more options from the values of this field to query data.

  • Display name field: When the query field is a business ID or code that is difficult to understand, you can set a display name field to make the options of the query field easier to understand.

Note
  • When you select the Query Value Field, the system will automatically fill in the Display Name Field.

  • The Display Name Field is the same as the Query Value Field by default. If they are different, you need to manually modify them.

  • The tree drop-down supports a maximum of four levels of queries.

Display form

Supports selecting Tree display and Tile display.

Note

When the data exceeds 1,000 entries, it is recommended to select Tile Display as the display form.

  • Tree single selection display image

  • Tree multiple selection display image

  • Tile single selection display image

  • Tile multiple selection display image

Query mode

Supports selecting Single selection and Multiple selection.

Query time

Supports Click to query and Pre-query.

  • Click to query: A conditional query is triggered only when you click the query button.

  • Pre-query: Pre-query the content values of the drop-down list before opening the dashboard. This allows the drop-down list values to be displayed according to the configuration of row-level permissions for each account, and the default value for each person will also vary based on the corresponding row-level permissions configuration.

    Note

    Pre-query is suitable for scenarios where the target dataset is configured with row-level permissions and the data volume is small. Otherwise, it will affect the overall report loading time.

Set default value

Select this option and set the relevant values. The preconfigured values will be displayed in the filter bar when the report page is loaded later.

Note

The default value here is the display name field in the tree structure design. image

Quick association

After selecting Quick Association, the sub-level fields in the tree structure will be automatically selected into the Associated chart and field of the same dataset chart, thereby improving the efficiency and accuracy of filter bar configuration.

For example, when setting the associated chart and field for the second-level Province in the following figure, the system has automatically filled in the province field into the associated fields of the Line Chart and Bar Chart of the same dataset chart, but not the associated fields of the Pie Chart of a different dataset chart, which needs to be manually entered.

image

Note

Only the charts selected in the Associated chart and field of the main level support automatic synchronization of sub-level fields. image

You can click Associated Chart in the tree structure or click the name of the sub-level in the query items on the left to associate other dataset charts and fields with the sub-level of the tree structure. image

In this example, the configuration is as follows:

  • The Select dataset refers to the company_sales_record, which is the source dataset for the current chart.

  • The Tree structure design sets the area field as level one and the province field as level two. Because the query field values are clear and intuitive, the query field and the display name field are kept the same.

  • The Display form is configured to Tile display.

  • The Query mode allows for Multiple selection, enabling the report viewer to select several options in the filter bar.

  • The Query time is set to Click to query, requiring the report viewer to click the query button to initiate a conditional query.

  • The Set default value is preset to Northeast > Liaoning, displaying the query results for this region and province by default when the report is opened.

  • The Quick association feature is enabled.

The resulting interface appears as follows:

112

Search from Database

When conducting a filtered query with more than 1,000 filter items, only the first 1,000 option values are displayed by default. You can search directly for option values that are not displayed. If the desired option value is not found, consider using the Database Search feature.

image

Note

The Database Search is only supported when the display type is a Drop-down list and the source of option values is set to Automatic parsing or a Single dataset.