Cell drop down tree returns the displayed value

1.Problem description

Add Drop_down tree widget in cell, data dictionary set actual value and display value, select the display value, after the out-of-focus cell will display the actual value.

For example, the following figure shows the department structure in the drop-down tree. The actual value of the option is the department ID, and the displayed value is the department name. It can be seen that the actual value in the drop-down tree box changes after the out-of-focus.

So how do you make it out of focus and still show up as a display value and commit it to the library?

2. Solution

The Cell Attributes>Data Dictionary sets the relationship between the actual value and the display value, so that the final display value after out of focus can be achieved. MAPARRAY() and VALUE() functions are used to convert the actual VALUE into the library.

1. Preparing data

1) Create a general report and add dataset ds1. The SQL statement is SELECT * FROM CorporationDepartment

2) Then add tree dataset tree1 and select ds1 as shown below:

2. Adding a drop-down tree

Add a drop-down tree widget in cell A1, select Automated Build data dictionary construction mode, the actual value is the DepartmentID, display value is the Department_name. Set to multiselect and uncheck Return full hierarchical path. As shown below:

3. Cell configuration setting

Select cell A1, select data dictionary in Cell Attributes > Display, bind ds1 dataset, the actual value is the DepartmentID, the displayed value is the Department_name.

4. Data Entry Attributes Settings

After the above setting, the out-of-focus display is already the displayed value, but the value obtained by submitting the bound cell in the library at this time is still the actual value. Here you can use the formula to process: array(A1,value("ds1","Department_name","DepartmentID",item)), where the array function is used to create a new array, and the Value function is used to list and the one-to-one department name of the department ID in cell A1.    

Note: If you want to concatenate the result and store it in a database as a string, you can change the formula:JOINARRAY(maparray(A1,value('ds1',' Department_name ',' DepartmentID',item)),',')

5. Effect preview

Completed template see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Cell_drop_down_tree_returns_the_displayed_value.cpt

Click to download the template: 

Cell_drop_down_tree_returns_the_displayed_value.cpt