Multi-selection Fuzzy Search in a Drop-down Checkbox

Problem Description

In some scenarios, you may need to select multiple values in a drop-down checkbox and perform a fuzzy search based on each value, as shown in the following figure.

Solution

The fuzzy search can be implemented using SQL statements, mainly with the LIKE operator or LIKE-like functions. The implementation varies among different databases, as described below.

1. Oracle Database implementation

The fuzzy search is implemented primarily using the regexp_like() function. Create a new data query (using the built-in emp table in the Oracle Database). The SQL statement is as follows:

select * from emp where regexp_like(ename,'${ename}')

Note:

The query statement does not contain the % character.

2. MySQL Database implementation

The MySQL implementation is similar to Oracle, except it uses the rlike keyword. The SQL statement is as follows: select * from emp where ename rlike '${ename}'

Note:

The query statement does not contain the % character.

Alternatively, you can use the regexp keyword. The SQL statement is as follows: select * from emp where ename regexp '${ename}'

Note:

The query statement does not contain the % character.

3. SQL Server and other database implementations

SQL Server does not have an equivalent keyword, so the traditional approach of string concatenation must be used. The SQL statement is as follows: select * from emp where ename like '%${ename}%'

Note:

1. The query statement contains the % character.
2. This implementation applies not only to SQL Server but also to other databases.

Template Design

Data Preparation

The FRDemo database (a SQLite database) is used here for demonstration.

Create a new data query ds1. The SQL statement is SELECT * FROM Employe where Full_name like '%${Full_name}%', as shown in the following figure.

Report Design

Create a new general report. Drag the fields from the dataset ds1 into cells A2 to F2. The report body design style is as shown in the following figure.

Parameter Panel Design

1. Edit the parameter panel, click Add All, choose the drop-down checkbox as the parameter widget, and set Widget Value of the label widget to Full_name:, as shown in the following figure. 

2. Set Widget Name of the drop-down checkbox widget to Full_name, matching the name used within ${} in the data query ds1. Configure Data Dictionary. In the pop-up Data Dictionary window, set Type to Custom, and customize the actual values and display values.

Set Returned Value's Type to String, and set Separator to %' or Full_name like '%. The following figure shows the settings.

Note:

The use of separators here depends on the database type. For details, refer to the SQL statements supported by the database and the way dataset parameters are defined. For example, in Oracle and MySQL databases, you can use | as the separator for the returned value's type, while in SQL Server and SQLite databases, you can use %' or Name like '% as the separator.

Effect Preview

1. PC

Save the template and click Pagination Preview, as shown in the following figure.

2. Mobile terminal

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

Notes

Problem description

Save the template, click Pagination Preview, select multiple values for the query, and the following prompt appears.

Solution

The system has SQL injection prevention enabled. After evaluation, you can remove \b(?i)or\b from the list of Disabled Special Keyword, as shown in the following figure.

Download the template: Fuzzy Search in a Drop-down Checkbox.cpt