All Products
Search
Document Center

Quick BI:Query Data based on a Text Field

Last Updated:Apr 26, 2024

This topic describes how to configure a filter bar to query data based on a text field.

Prerequisites

  • A dashboard that contains text fields is created.

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

Configure a filter

  1. Go to the dashboard edit page. For more information, see Create a dashboard.

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

    Charts that are created on the dashboard must contain text fields. For more information, see Line charts.

    image.png

  3. Follow the steps shown in the following figure to add a query control.

    image.png

  4. Set the query control. image.png

    1. In the Query conditions section of the Query condition setting dialog box, set the filter name to Level Query.

    2. Select Line Chart and Order Level.

    3. Specify Display type in the Basic configuration section and configure the parameters.

      1. If you set Display Type to Drop-down, see Drop-down.

      2. If you set the Display Type parameter to Text Input, see Text Input.

      3. If you select Tree drop from the Display type drop-down list, see the use case in Tree drop.

  5. Click OK.

Drop-down list (formerly enumerated filter)

If you set the Display Type parameter to Drop-down List, you can select Automatic Parse, Single Dataset, or Manual Input. The following figure shows that Option Source is set to Automatic Parsing.

image.png

Note
  • If the data in the charts selected from the Associated Charts and Fields section belongs to the same dataset, Automatic Parsing indicates that the system automatically obtains data from the dataset.

  • If the data in the charts that are selected from the Related charts and fields section belongs to multiple datasets, Automatic parsing indicates that the system automatically obtains data from multiple datasets. This indicates that the values in the drop-down list of a filter bar are from multiple datasets.

Automatic parsing

Parameter/Option

Operation

Examples

inquiry mode

Valid values: Single choice and Multiple choice.

Multiple choice.

Query Time

Valid values: Click to Query and Pre-query.

  • Click to Query: This is the default value. When you click Inquire on the right side of the filter bar on the preview page, a conditional query is triggered.

  • Pre-query: This mode is supported in Quick BI V3.12.1. If you set Query Time to Pre-query, you can select a value from the drop-down list in the filter bar on the dashboard. The values that are displayed in the drop-down list are determined based on the configuration of row-level permissions. The default value that is displayed for each account varies based on the configuration of row-level permissions.

    If the selected dataset is configured with row-level permissions and the amount of data is small, you can set Query Time to Pre-query. In other scenarios, pre-query may increase the report loading duration.

Click to Query.

Set filter defaults

If you select this option, the field 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.

This option is selected. L2 and L3 are selected as default values.

Example:

After you set inquiry mode to Multiple choice and set Query Time to Click to Query, you can select Set filter defaults and configure the default values.

image.png

In the Field pane, you can perform the following operations:

  • Enter a keyword in the search box and click the field name that appears to add the field value to the Added section on the right.

  • Click Add all field values on the left to add all the field values to the Added section on the right.

  • Select field values on the left side of the Field pane to add them to the Added section on the right.

  • Select Exclude at the bottom of the Added section to exclude the selected field values from the Added section.

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

Single data set

image

Parameter/Option

Operation

Examples

Select a dataset

The dataset in the workspace to which the current dashboard belongs. We recommend that you select the dataset of the associated chart.

company_sales_record

Query Value Field

The field in the source dataset that is used to match the associated field.

order_level

Query name field

The field in the source dataset that is used to filter data. This field appears in the filter bar.

  • If the value of Query name field is the same as the value of Query Value Field, the system automatically fills the value in the Query name field after you select Query Value Field.

  • If the value of Query name field is different from the value of Query Value Field, you must manually change the value of Query name field.

order_level

Sorting Field

The field that is used to sort data. This parameter is optional. If you do not specify this parameter, the data sequence remains the same as that in the dataset by default.

You can select a sorting field from the drop-down list, such as a measure or a date dimension.

After you specify this parameter, only the sequence of field values in the drop-down list of the filter that you configured is changed. The sequence of dimension field values in the charts is not affected because it is determined based on the field sequence in the dataset or the field order in the Category Axis (Dim.).

province

inquiry mode

Valid values: Single choice and Multiple choice.

Multiple choice.

Query Time

Valid values: Click to Query and Pre-query.

  • Click to Query: This is the default value. When you click Inquire on the right side of the filter bar on the preview page, a conditional query is triggered.

  • Pre-query: After you open a dashboard, you can query the values of the drop-down list in advance. You can view the values of the drop-down list for each account based on the row-level permission configuration. The default value varies based on the row-level permission configuration.

    Note

    If the selected dataset is configured with row-level permissions and the amount of data is small, you can set Query Time to Pre-query. In other scenarios, pre-query may increase the report loading duration.

Click to Query.

Set filter defaults

If you select this option, the field 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.

  • If you set Query Time to Click Search, you can manually set the default filter value.

    image.png

  • If you set the query time parameter to Pre-query, you can select Manual Settings or Dynamic Functions. Dynamic functions are supported only when the query value and display name fields are the same. Pre-query is suitable for scenarios where you need to fetch data in advance, such as displaying different filtering items based on the row-level permissions of each visitor.

    image.png

    For dynamic functions, you can set Maximum Value of Query Field, Minimum Value of Query Value Field, and First Item of Query Value Field.

    Note

    The first item of the query value field is the first value of the query value field in the dataset. If the field is sorted in the dataset, such as ascending or descending order or custom sorting, the first item of the query value field is used. The first item of the query value field is not affected by the sorting field of the value source. image.png

This option is selected. L2 and L3 are selected as default values.

Enter manually

Parameter/Option

Operation

Examples

manual input

If you set Option value source to Enter manually, you can click manual input and enter values in the Query value and show name sections.

No default value

inquiry mode

Valid values: Single choice and Multiple choice.

Multiple choice

Set default value

Displays the value that you set in this section in the Order Level drop-down list.

This option is selected. Medium and High are selected as default values.

  • If it is the first time that you manually enter values and the values that you want to enter in the Query value and show name sections are the same, you need to only manually enter a value in the Query value section. After you click OK, the system automatically fills the same value in the show name section.

  • If it is the first time that you manually enter values and the values that you want to enter in the Query value and show name sections are different, you must manually enter the values in the two sections.

  • If it is not your first time to manually enter values, you must manually enter a new query value and display name. If you enter only the query value, the following error message appears.

    image.png

The following figure shows a chart with the preceding configurations.

image

Text input box (formerly conditional filter)

If you set Display Type to Text Input Box, you can select Single Condition, Or Condition, or And Condition. The following figure shows that Conditional Form is set to OR.

image

The following table describes the mappings between rules and the SQL statements used when you configure default values for the product_sub_type field.

Rule

SQL syntax

Exact match

product_sub_type='ruler'

No

product_sub_type!='ruler'

contains

product_sub_type like '%ruler%'

Not included

product_sub_type not like '%ruler%'

Starts with

product_sub_type like 'ruler%'

End With

product_sub_type like '%ruler'

Is empty

product_sub_type is null

not null

product_sub_type is not null

Empty text

product_sub_type=''

Non-empty Text

product_sub_type!=''

Parameter/Option

Operation

Examples

Conditional form

Valid values: Single condition, Or condition, and And condition.

Or condition.

Set default value

The values vary based on the selected conditional form.

In the following example, ruler is specified for the contain condition and table is specified for the Starts with condition.

Set default value

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

Note

This parameter appears only when you select Set default value.

This option is selected.

Tree drop

If you select Tree drop from the Display type drop-down list, fields from both the current dataset and another dataset can be selected. Display form can be set to Tree display or Tiled display.

  1. Select the dataset company_sales_record and click Start configuration.

  2. In the Tree structure design dialog box, specify Query Value Field and Display name field for each level and click OK.

    • If the value of Query Value Field is the same as the value of Display name field, the system automatically fills the value in Display name field after you specify Query Value Field.

    • By default, the values of Query Value Field and Display name field are the same. If the values are different, you must manually change the values.

    • The tree drop display type supports a maximum of four levels of queries.

  3. Configure Display form, inquiry mode, and Query Time.

    You can set Display form to Tree display or Tiled display.

    • If you set Display Form to Tree, Query Mode can be set to Single Choice or Multiple Choice. Example of a chart with Tree display and Single choice

      imageMulti-select tree display

      image

    • If you set Display form to Tiled display, inquiry mode can be set to Single choice or Multiple choice, and Query Time can be set to Click to Query or Pre-query.

      If data records exceed 1,000, we recommend that you set Display Form to Tile.

      image

  4. Select and configure Set Default Value.

  5. (Optional)

    Select and configure Quick association.

    If you select Quick association, the current line chart and field are automatically selected for the sub-level of the tree structure.

    Click Associate Chart in the Tree Structure Design section or click the sub-level name of the tree structure in the Query Condition section to associate it with charts and fields in another dataset.

    image

    For example, when you configure the associated chart and field settings for the second-level provinces in the following figure, the system automatically fills in the associated fields of the line chart and pie chart of the same dataset. The associated fields of the chart kanban of the non-dataset need to be manually entered.

    image

  6. Click OK.

    After the configuration is complete, you can view the configured levels in the Query conditions pane of the Query condition setting dialog box.

    • Click the 隐藏 icon to hide or show the query levels.

    • Click the 隐藏 icon to hide the filter in the filter bar.

  7. Click Inquire in the dashboard display section of the filter bar. All the charts that are associated with the filter bar are automatically updated.

Search from Database

When you filter a query, if more than 1000 items are filtered, the first 1000 items are displayed by default. If you do not find the value you want, you can search from the database.

image

Note

Search from Database is available only when Display type is set to Drop-down list (formerly enumerated filter) and Option value source is set to Automatic parsing or Single data set.