Multi-value query for dataset parameters

The FR designer can realize the multi-value query function of the dataset. Users can filter the multi-value query of the dataset according to the actual situation. There are two application scenarios for dataset multi-value queries.

Users perform multi-value query on multiple fields in the dataset to filter and display the data. For example, for multi-regional sales companies, it is often necessary to conduct joint queries for their respective regions, provinces and cities, as well as compare cities at the same level. As shown below:

1. Preparing templates

This paper will achieve the effect of scene 1 based on the Drop-down box parameter linkage.

Template location: %FR_HOME%\webroot\WEB-INF\reportlets\doc\Parameter\cptMulti-value_query_for_dataset_parameters.cpt

Alternatively, click to download the template:

Multi-value_query_for_dataset_parameters..cpt

Open the template in FineReport Designer as shown below:

2. Modifying a template dataset

Modify template dataset ds1 to:

SELECT * FROM Employe where Country= '${country}' and Region = '${region}' and City in ('${city}')

As shown below:

3. Setting the drop-down check Box widget

Enter the parameter panel and delete the drop-down widget named city, as shown below:

Add a drop-down check box widget named city, set the data dictionary to "Data Query", set the dataset to "City ", and set the actual value and display value to "City". Set the return value type to a string and the delimiter to ',' (that is, a comma is placed between single quotation marks (). The delimiter is set to separate multiple parameter values. For example, if the region is North China, Central China, the return value is North China, Central China).

Note: Delimiter must be entered in English half corner state.

4. Effect preview

1) PC

Save the template and click "Pagination Preview" to see the effect shown in Section I.

2) Mobile 

App and HTML5 end effect, as shown below:

Users can perform multi-value queries on a single field in a dataset using a drop-down check box widget.

1. Data preparation

Create a general report, create a dataset ds1, SQL statement is:SELECT * FROM Product where Product_name in ('${name}')

Note: If the parameter is a string, be sure to add single quotation marks. For numerical values, single quotation marks are not required.

2. Report design

Drag the field into the corresponding cell, as shown below:

3. Adding parameter widgets

Edit the parameter panel, click "Add All" above the widget Settings on the right to generate the parameter widget, click the parameter box, and select "Drop-down Check Box widget".

4. Setting the data dictionary of the parameter widget

Select the drop-down check box widget named name, set the data dictionary to the Database Table, select the product table of the built-in database FRDemoEN, the actual value and display value are the Product_name, select the return value type "String", set the delimiter to ", "(i.e., single quotation marks with a comma between them, The separator is set to separate the selected values. For example, if the product name is Apple juice, milk, the return value is apple juice, milk.

Note: Delimiter must be entered in English half corner state.

5. Effect preview

1) PC

2) Mobile 

App and HTML5 end effect, as shown below:

1) For the scenario 1 template, see:

%FR_HOME%\webroot\WEB-INF\reportlets\doc\Parameter\cptMulti-value_query_for_dataset_parameters.cpt

Clickto download the template: 

Multi-value_query_for_dataset_parameters..cpt

2) For scenario 2 templates, see:

%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc\Parameter\MultiValue\Drop - down_check_box_multi-value_query.cpt

Click to download the template: 

Drop - down_check_box_multi-value_query.cpt