Drop-down tree achieves multi-value query

1. Application scenarios

There are continent, country and capital indicators in the drop-down tree. If you want to query the data of multiple capitals at the same time, as shown in the figure below, how to achieve this?

2. Implementation ideas

We can use the function of REPLACE() and SUBSTITUTE() built in the software.

1) REPLACE()

Replace (text, textto replace, replacetext): replace the original text with other text according to the specified string.

Text: text or cell reference that needs to be replaced by some characters.

Texttoreplace: the specified string or regular expression.

Replacetext: text that needs to replace part of the old text.

2) SUBSTITUTE()

SUBSTITUTE(text,old_ text,new_ text,instance_ Num): use new_text replace old_text in string.

Text: text that needs to be replaced, or cell reference that contains text.

Old_text: part of the text that needs to be replaced.

New_text: used to replace old_ Text.

Instance_num: specify new_text to replace the old_ text。

Note 1: if specified instance_num, only the old_text in the specified position is replaced, otherwise all old_text will be replaced by new_text.

Note 2: if the specified text in the string needs to be replaced, the substitute function is used; if any text in the specified position in the string needs to be replaced, the replace function is used.

1. Data preparation

Create a new normal report and add a dataset. The data query statement is as follows:

DS1: SELECT * FROM map_WorldMa

where Continent is not null 

${if(len(Area)=0,"","and Capital in ('"+SUBSTITUTE(Area,",","','")+"')")}

DS2: select distinct Continent FROM map_WorldMa

where Continent is not null

DS3: select distinct Country FROM map_WorldMa where Continent = '${layer1}'  

and Country is not null

DS4: select distinct Capital FROM map_WorldMa where Country = '${layer2}' 

Note: layer1 indicates the value of level 1 node. If it is called in Level 2, it is SELECT field FROM tablename WHERE Level 2 field ='${layer2}'in turn.

2. Table design

Create a new normal report and drag the field into the corresponding cell, as shown in the following figure:

3. Parameter pane

Open the parameter pane, add the parameter “Area” generated automatically, and select drop-down tree. Name the drop-down tree widget as Area, check multiselect attributes, set data dictionary, and select "Basic Layered Build" as the Build Way.

1) Gradation1: select data query DS2, and set the actual value and display value as "Continent", as shown in the figure below:

2) Gradation2: select data query DS3, set the actual value and display value as "Country", as shown in the figure below:

3) Gradation3: select data query DS4, and set the actual value and display value as "Capital", as shown in the figure below:

4. Effect preview

Save the template and click pagination preview. The effect is as I.1:

2) Mobile terminal

For completed templates, please refer to:

%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Dd_multi_query.cpt

Click to download the template: 

Dd_multi_query.cpt