Multi-Value Query from a Drop-down Checkbox by Dataset Parameters

Version

Function Description

FineReport allows you to perform a multi-value query on a dataset based on the actual scenario. The multi-value query on a dataset can be applied to two scenarios.

You can perform the multi-value query on multiple fields in the dataset to filter and display data. For example, a multi-region sales company often needs to perform combined queries across regions, countries, and cities, as well as comparisons between cities at the same level.

Template Preparation

This document introduces how to achieve the effect of scenario one using the following template.

You can click to download the template:Drop-down Box Parameter Linkage.cpt.

Open the template in FineReport, as shown in the following figure.

Modifying the Template Dataset

Change the SQL statement in dataset ds1 as SELECT * FROM Orders where "Shipper's region"='${area}' and "Shipper's country"='${country}' and "Shipper's city"='${city}', as shown in the following figure.

Setting the Drop-Down Checkbox Widget

Enter the parameter panel, and delete the drop-down box widget named city, as shown in the following figure.

Add a drop-down checkbox widget, and name it city. On the Data Dictionary setting page, set Type to Data Query, Dataset to City Option, and both Actual Value and Display Value to Shipper's city. Set Returned Value's Type to String, and set Separator to ',' (a comma enclosed in single quotation marks). The separator is used to separate multiple selected parameter values. For example, if Lampang and Chumphon are selected from the drop-down list of City, the returned value will be Lampang','Chumphon.

Note:

The separator must be entered in the English half-width mode.

Effect Display

PC

Save the template, and click Pagination Preview. The preview effect is the same as that in section "Scenario One: Multi-Value Query on Multiple Fields."

Mobile Terminal

The following figure shows the preview effect on the DataAnalyst app and on the HTML5 terminal.

You can perform the multi-value query on a single field in the dataset through the drop-down checkbox widget.

Data Preparation

Create a general report. Create a dataset named ds1, and enter the SQL statement: SELECT * FROM SProduct where Product_name in ('${name}').

Note:

If the parameter is of string type, ensure that single quotation marks are added. If the parameter is of numeric type, single quotation marks are not needed.

Report Design

Drag the fields into the corresponding cells, as shown in the following figure.

Adding the Parameter Widget

Enter the parameter panel, and click Add All in the upper right corner of the designer.

Set Widget Value of the label widget to Product Name:, click the parameter box of the parameter widget, and select the drop-down checkbox widget.

Setting the Data Dictionary for the Parameter Widget

Click the drop-down checkbox widget named name. On the Data Dictionary setting page, set Type to Database Table, Database to FRDemo, Database Table to SProduct, and both Actual Value and Display Value to Product_name. Set Returned Value's Type to String, and set Separator to ',' (a comma enclosed in single quotation marks). The separator is used to separate multiple selected parameter values. For example, if Apple Juice and Milk are selected from the drop-down list of Product Name, the returned value will be Apple Juice','Milk.

Note:

The separator must be entered in the English half-width mode.

Effect Display

PC

Mobile Terminal

The following figure shows the preview effect on the DataAnalyst app and on the HTML5 terminal.

1. Template for scenario one:

Click to download the template: Multi-Value Query by Dataset Parameters.cpt.

2. Template for scenario two:

Click to download the template: Multi-Value Query from a Drop-down Checkbox.cpt.