Drop-down tree widget basic layered build example

1) Application scenarios

The drop-down tree widget has a multi-layer tree structure of data, which can be used to show the relationship between different levels, as shown in the following figure:

2) Implementation roadmap

The drop-down tree widget can select the corresponding data set in the data definition interface by using a normal hierarchical build. This is essentially dynamic SQL in FR, and each SQL needs to be defined as a report data set. In a normal hierarchical build, a hierarchy is defined as follows: 

Level 1: Use a Data Dictionary to define the actual and displayed values of the drop-down box.

Level 2: Filter based on the data at Level 1, define a dataset, and then return the columns defined by the dataset through the data query. SELECT * FROM table_name WHERE table_name = '${layer1}'

Note: Layer1 means to take the value of the node of level 1,if called from level 3, SELECT * FROM table_name WHERE table_name  = '${layer2}' .Layer1 and Layer2 are system parameters, which are fixed and cannot be changed.

1. Preparing data

Create a new dataset ds1, query all countrys, SQL statement is:SELECT Country FROM Client where Country!='', as shown below:

Create a new dataset ds2 and query the owner region according to the value of layer1. The SQL statement is:SELECT  Region FROM Client where Region!='' and Country='${layer1}',as shown below:

Create a new dataset ds3, according to the first two layers of layer1, layer2 query master city, SQL statement is: SELECT  City FROM Client where City!='' and Country = '${layer1}'and Region='${layer2}', as shown below:

2. Report design

1) Drag the drop-down number check box widget into the parameter panel, as shown below:

2) Select the Attributes>Advanced>Data dictionary, the construction method of Basic layered build, select the dataset corresponding to each layer.

At the first layer, select Data Query, select ds1 as the data set, select the actual value and display value as the Country, as shown in the figure below:

At the second level, select Data Query, select ds2 as the dataset, select the actual value and display value as the Region, as shown in the figure below:

At the third layer, select Data Query, select ds3 as the dataset, and select the actual value and display value as City, as shown in the figure below:

3. Effect viewing

1) PC

Click Pagination Preview and the effect will look like the picture below:

2) Mobile

The effect of App and HTML5 is as follows:

The template has been completed, please refer to: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Drop-down_tree_widget_basic_layered_build_example.cpt

Click to download the template: 

Drop-down_tree_widget_basic_layered_build_example.cpt