Fuzzy Query of Drop-down Box/Drop-down Checkbox/Drop-down Tree

1) Problem description

In some scenarios, we want to use the drop-down check box to select multiple values, and then blur the query by each value, as shown in the following figure:

2) Solutions

Processing through SQL statements, mainly using like keywords or like functions, different database processing methods are not the same, the following are introduced respectively.

1) Oracle database solution

The main solution is to use the regexp_like() function. Create a new query (using the emp table built into the Oracle database) with the following SQL statement:

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

Note: There is no % sign in the query.

2) MySQL database solution

MySQL is implemented in the same way as Oracle, except that it uses the rlike keyword, and SQL statement is as follows:

select * from emp where ename rlike '${ename}'

Note: There is no % sign in the query.

Or use the regexp keyword, and the SQL statement is as follows:

select * from emp where ename regexp '${ename}'

Note: There is no % sign in the query.

3) SQL Server database and other database solutions

SQL Server does not have a similar keyword to solve the problem, you can only use the old way to concatenate string, SQL statement as follows:

select * from emp where ename like '%${ename}%'

Note 1: The query statement contains % numbers.

Note 2: This solution applies not only to SQL Server, but also to other databases.

1.Template design

1) Data preparation

This section uses the FRDemo database (SQLite database).

Create data query ds1, SQL statement as follows:

SELECT * FROM Employe where Full_name like '%${Full_name}%',As shown below:

2) Report design

Create a regular report, drag the ds1 field of dataset into cells A2~F2, and design the report body as shown below:

3) Design parameter pane

•  Edit the parameter pane, select Add All, and select the drop-down check box widget for the parameter widget. The value of the label widget is Full_name:, as shown below:

  

•  Drop down check box widget the widget name is the same as the name in ${} in data query ds1. Edit the data dictionary, select custom type settings, and customize the actual value, display value.The return value is a string of type and the separator is %' or Full_name like '%. As shown below:

• 

Note 1: Oracle, MySQL database drop-down box "|" return value types used as the separator.

Note 2: The return value type of the SQL Server and SQLite database drop-down check boxes uses "%' or Full_name like '%" as the separator.

2.Effect preview

1) PC

Save the template and click Pagination Preview, as shown below:

2) Mobile 

App and HTML5 end effect, as shown below:

3.Precautions

Problem description

Save template, select Pagination Preview, select multiple value query, if the following prompt appears: 

Solution

SQL anti-injection is enabled in the system, and the injection of or keyword can be cancelled after considering security, as shown in the following figure:

For the completed template, see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Drop-down_check_box_multi-value_fuzzy_query.cpt

Click to download the template: 

Drop-down_check_box_multi-value_fuzzy_query.cpt