Multi-Value Query by Drop-down Tree Widget with Multiple Options

Version

Application Scenario

The drop-down tree contains three indicators, namely Continent, Country, and Capital. If you want to query the data of multiple cities at the same time, how to realize the effect? The following figure shows the effect.

Implementation Method

You can use the built-in REPLACE() and SUBSTITUTE() functions to realize the effect.

1. REPLACE()

REPLACE(Text, Texttoreplace, Replacetext) allows you to use the function to replace the original text with other texts based on a specified string.

• Text: referenced cell containing characters to be replaced, or text to be      partially replaced

• Texttoreplace: specified string or regular expression

• Replacetext: text used to replace part of the original text

2. SUBSTITUTE()

SUBSTITUTE(Text,Old_text,New_text,Instance_num) allows you to replace Old_text with New_text.

• Text: referenced cell containing characters to be replaced, or text to be partially replaced

• Old_text: part of text to be replaced

• New_text: text used to replace the old text

• Instance_num: position at which old texts need to be replaced with new texts

Note:

1. If Instance_num is specified, only the old text at the specified position will be replaced. Otherwise, all old texts will be replaced by the new texts.

2. To replace a specified text in a text string, use the SUBSTITUTE function. To replace any text at a specified position in a text string, use the REPLACE function.

Template Design

Data Preparation

Create a general report and add datasets with the following query statements:

• ds1: SELECT * FROM map_WorldMawhere 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 represents that the value of layer 1 node is obtained. If the value of layer 2 is called, the query statement is SELECT Field FROM Table name WHERE Field in Layer 2 = '${layer2}'.

Template Design

Create a general report and drag the fields into the corresponding cells, as shown in the following figure.

Parameter Page

Open the parameter page, add a label widget and a drop-down tree widget, and name the drop-down tree widget Area. Select Multiselect and set Data Dictionary. (Basic Layered Creation is selected as Creation Type.)

1. Gradation1: Set Type to Data Query, select ds2 as Dataset, set Actual Value and Display Value to Continent, respectively, as shown in the following figure.

2. Gradation2: Set Type to Data Query, select ds3 as Dataset, set Actual Value and Display Value to Country, respectively, as shown in the following figure.

3. Gradation3: Set Type to Data Query, select ds4 as Dataset, set Actual Value and Display Value to Capital, respectively, as shown in the following figure.

Effect Display

1. PC

Save the template, and click Pagination Preview. The following figure shows the effect.

2. Mobile terminal

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

For details, you can download the template Multi-Value Query by Drop-down Tree Widget with Multiple Options.cpt.