Drop-down box shows multiple columns

1) Expected effect

When users click the drop-down box widget to select from the drop-down list, it is often a purposeful choice. Therefore, the more complete the record that pops out of the drop-down box, the more convenient it is for users to select data by using the drop-down box widget.

As shown in the picture below, click the drop-down box of student ID to display the student ID, class and student name in the drop-down list.

2) Implementation roadmap

Select the columns to be displayed using the value formula definition, and then combine the columns to be displayed in the display values of the drop-down box.

1. Preparing a template

1) Click "File > New General Report" in FineReport Designer to add dataset ds1, SQL statement is:

SELECT * FROM STSCORE

2) The template is as follows:

2. Adding parameters

Click "Template > Template Parameters", add the parameter "std" and click "OK". As shown below:

3. Setting widgets

Add Drop-down box widget and Query widget in the parameter panel. The component name of the drop-down box widget is std. Set the data dictionary for the drop-down box widget. Set the type toData Query. The dataset is ds1 and the actual value is STUDENTNO.The displayed value is expressed by the formula:

$$$ + " "+ VALUE("ds1",1,2,$$$,1)+" "+ VALUE("ds1",3,2,$$$,1)

Formula description:

Note: For details about the value function, see introduction to the Value Function Into.

$$$: The actual value of the current cell.

VALUE("ds1",1,2,$$$,1) : Returns the elements in the first column of ds1 whose corresponding VALUE in the second column is $$$.

VALUE("ds1",3,2,$$$,1) : Returns the elements in the third column of ds1 whose corresponding VALUE in the second column is $$$.

The final argument: if the value is less than 0, the first value of the array is taken; if the value is greater than the array length, the last value of the array is taken and the results are concatenated into a string.

4. Adding filter criteria

Double-click on cell A2, click Filter,The optional column is STUDENTNO ,which equals to $std, as shown in the following figure:

Save the template and click Pagination Preview to see what you expect in Section Iof this article.

The completed template can be seen in the %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\Drop-down_box_shows_multiple_columns.cpt

Click to download the template: 

Drop-down_box_shows_multiple_columns.cpt