Drop-down tree implements dataset filtering

When we make a template, which is often used to the parameters, the parameters are divided into two kinds, one kind is data set parameters, a template parameter, therefore, there are two kinds of filter, filter is a kind of dataset parameters, the filter is a template parameter, the front two are described, and also introduced the usage, due to the particularity of the drop-down tree, the return value is a path array values, It is not a string, so if you use a drop-down tree, it will be filtered differently. This article describes how to filter dataset parameters.

1. Data preparation

Create new dataset ds1: SELECT * FROM Client where 1=1 ${if(len(aa)==0,""," and City = '"+treelayer(aa)+"'")}

Create new dataset layer1: SELECT Country FROM Client where Country!=''

Create new dataset layer2: SELECT  Region FROM Client where Region!='' and Country='${layer1}'

Create new dataset layer3:SELECT  City FROM Client where City!='' and Region='${layer2}'

Note: Treelayer function is used in dataset ds1, that is, to obtain the value of the last layer of parameter aa. For detailed usage, please see: Treelayer function

2. Drop-down tree construction

1) Edit the parameter interface, add parameter aa widget, select the drop-down tree widget as the widget type, and set the label widget value to Area:, as shown below:

2) Select the Basic Layered Build for the drop-down tree data dictionary, and the data sources are Layer1, Layer2 and Layer3. Check the specific usage method: Example of hierarchical construction of the drop-down tree

The drop-down tree widget is an optional drop-down tree by default. The Return Path is selected. As shown below:

Note: If the result is not checked in the drop-down tree attribute to return the full hierarchical path, then the return value of the drop-down tree is not the path value, so there is no need to use Treelayer function to modify the return value of the drop-down tree again, just use it directly. At this point, the SQL statement of ds1 data set can be modified as: SELECT * FROM [SClient] where 1 = 1 ${the if (len (aa) = = 0, ""," and the City = '" + aa + "' ")}

3. Report design

As shown below:

4. Effect viewing

5. Completed template

For completed templates see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Drop-down_tree_implements_dataset_filtering.cpt

Click to download the template:

Drop-down_tree_implements_dataset_filtering.cpt

1) Data preparation

Open the radio drop-down tree template above and change the dataset ds1: SELECT * FROM Client  where 1=1 ${if(len(aa)==0,""," and City in ("+"'"+treelayer(aa,true,"\',\'")+"'"+")")}

Note: Treelayer (aa,true,"\',\'") returns the value "," Is a delimiter string of the form "BeiJing, TianJin", but in the SQL statement to implement the filtering must be "BeiJing, TianJin", the latter two than the first and last two single quotes. Instead, concatenate two single quotes before and after treelayer(aa,true,"\',\'").

2) Drop-down tree construction

Select the drop-down tree widget, and in the widget property sheet, select Multiselect and Return Path as a result. As shown below:

Note: Select the option to return the result to the full hierarchical path in the drop-down tree attribute, and the treelayer function is needed when the ds1 dataset is filtered. If this option is selected, treelayer has two functions, one is to obtain the lowest value, the other is to convert the array into a string, but this option is not selected. It simply converts an array to a string.

The other: If the result is not checked in the drop-down tree attribute, the full hierarchical path is returned, and only the leaf node is returned. In this case, the function treelayer is not needed in filtering. Can put (" + "'" + treelayer (aa, true, "\ ', \ '") +"' "+") is modified to (" + "'" + replace (aa, ', '"', '") + "' "+").

3) Effect viewing

4) Completed template

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

Click to download the template: 

Drop-down_tree_implements_dataset_filtering2.cpt