Text Field Control Realizes Multi-value Query

1.1 Expected effect

How to set the filter condition of the report when the parameter value of the text field is multiple? As shown in the figure below, when the text field parameter has multiple order IDs, the query result is the information of multiple order IDs.

1.2 Implementation Ideas

Use the filter function of the report to set filter conditions, or filter through SQL statements.

2.1 Design report

1) New template, new data set ds1, SQL statement: 

SELECT * FROM ORDERS

2) Drag the field of the dataset ds1 into the report cell to design the table style. As shown below:

2.2 Add Text Area control

In the parameter panel, add [Text Area] control and [Query] button, the name of the [Text Area] control is "AK", as shown in the following figure:

2.3 Add filter

Set the filter condition in cell A2 and select the formula. The content of the formula is: INARRAY($$$, split($AK, "\n"))> 0. As shown below:

The function of the formula is to match whether the current cell content is one of the values entered in the [Text Area] control.

2.4 Effect preview

After saving the template, click "Pagination Preview" and enter the OrderID in the [Text Area] control. The effect on the PC side is shown in the figure below:

1) Refer to section 2.1 Design report.

2) Refer to section 2.2 Add Text Area control.

3) Modify the SQL statement in the ds1 data to filter the data in the dataset.

The SQL statement is: 

SELECT * FROM ORDERS where 1=1  ${if(len(AK)>0," and OrderID in ('" +replace(AK, "\n","','")+ "')","")}

The SQL statement uses "in" to query the data of the OrderID entered in a new line in the Text Area control.

4) After saving the report, the preview effect is shown in section 2.4.

Click to download the template: 

Text field control realizes multi-value_query-filter conditions.cpt

Text field control realizes muti-value_dataset filter.cpt