I. Overview
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.
II. Example-Filter Condition
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:
3. Example-Dataset Filtering
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.
4. Template Download
Click to download the template:
Text field control realizes multi-value_query-filter conditions.cpt
Text field control realizes muti-value_dataset filter.cpt