Solution for the Slow Fuzzy Query in the Drop-down Box

Version

Application Scenario

You want to increase the speed of the fuzzy query in the drop-down box when the data volume is large.

Implementation Method

You can use the text box to filter the data first, and then use the drop-down box for the fuzzy query through the widget bound with a data dictionary. In this way, the fuzzy query speed will be faster. The following figure shows the effect.

Template Creation

Create a general report, as shown in the following figure.

Data Preparation

(1) Create a database query ds1, and enter the SQL statement SELECT * FROM STSCORE where name like '%${if(len(id)=0,"error",id)}%', as shown in the following figure.

Note:

The SQL statement means that if the text box is empty, the error string is used for the fuzzy query, that is, no data will be obtained. If the text box is not empty, the entered value is used for the fuzzy query.

Template Parameter Adding

Add a template parameter name, as shown in the following figure.

Binding Parameters to Widgets

(1) Enter the parameter panel editing page, and add the parameters id and name to the parameter panel, as shown in the following figure.

(2) Bind the parameter id to the text widget, and bind the parameter name to the drop-down box widget. The following example binds the parameter id to the text widget.

(3) Bind a data dictionary to the drop-down box widget, as shown in the following figure.

(4) Set the widget values of Labelid and Labelname to Fuzzy Query Input: and Name:. The following figure shows the effect.

Report Design

(1) Enter text in cells A1 to F1, drag data columns into cells A2 to F2, and design the table style, as shown in the following figure.

(2) Double-click cell A1 to set the filter condition. Set Available Column: to NAME and Operator: to Equal to, and select Parameter and $name from the drop-down lists, as shown in the following figure.

Effect Display

PC

The following figure shows the effect on PCs.

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminals are the same, as shown in the following figure.

Download the completed template by clicking Solution for the Slow Fuzzy Query in the Drop-down Box.cpt.