Selecting All Values When the Parameter Value is Empty in the Drop-down Checkbox

Version

Problem

When you filter data by parameter, the data will be filtered accordingly based on the selected parameter value. However, how to realize the effect as shown in the following figure? Namely, how to query all values in the data table when no parameter value is selected in the drop-down checkbox country? The following figure shows the effect.

Implementation Method

You can select multiple values in a drop-down checkbox by report parameter or dataset parameter. If you need to realize the effect that all values are selected when the parameter value is empty in the drop-down checkbox, you can deselect Display Nothing Before Query.

Dataset Creation

Create a dataset named ds1 with the SQL statement Select * from Orders.

Report Design

Add headers in the first row and drag the corresponding fields from dataset ds1 into the cells, as shown in the following figure.

Adding Parameter Widgets

1. Add a label widget, set Widget Value to Shipper's Country:, as shown in the following figure.

2. Add a drop-down checkbox widget, set Widget Name to country, click , set Type to Database Table, set Database to FRDemo, set Database Table to Orders, and set Actual Value and Display Value to Shipper's country, respectively, as shown in the following figure.

3. Add a query widget, as shown in the following figure.

4. Click the blank area of the parameter panel, and deselect Query Nothing Before Query on the right Attribute panel, as shown in the following figure.

Filter Condition Setting

Double-click cell A2, select Filter, set Available Column to Shipper's country, set Operator to In, and Formula to if(len($country)==0,nofilter,$country), as shown in the following figure.

Note:

nofilter means no filter. if(len($country)==0,nofilter,$country) means that the data is not filtered if the value of the parameter country is empty. Otherwise, the data is filtered by the parameter value. if(len($province)==0,nofilter,$province) follows the same logic.

Dataset Creation

Create a dataset ds1 and modify the SQL statement to SELECT * FROM Orders where 1=1 ${if(len(country) == 0,"","and [Shipper's country] in ('" + country + "')")}.

In the formula, ${if(len(country) == 0,"","and [Shipper's country] in ('" + country + "')")} means that the data is not filtered if the value of the parameter country is empty. Otherwise, the shipper's country is the selected parameter value. The following figure shows the effect.

Note:

where 1=1 means that the condition is always true, avoiding syntax errors caused by the extra WHERE clause when no subsequent filter conditions are applied. len(country)==0 means the value of the parameter country is empty. In "and [Shipper's country] in ('" + country + "')", country means the parameter value of country is obtained, while the + operator is used for string concatenation.

Report Design

Design the report. For details, see section "Report Design" in section "Example One: Template Parameter."

Parameter Widget Adding

Click  to enter the parameter panel, click Add All, and modify Widget Value of the label widget to Shipper's Country. Select the drop-down checkbox widget, click , set Type to Database Table, set Database to FRDemo, set Database Table to Orders, and set Actual Value and Display Value to Shipper's country, respectively. Set Returned Value's Type to String and set Separator to ',', as shown in the following figure.

Click the blank area of the parameter panel, and deselect Query Nothing Before Query on the right Attribute panel, as shown in the following figure.

PC

Click Pagination Preview. Click Query directly without entering any parameter values. The effect is the same as that shown in section "Problem."

Mobile Terminal

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

1. Example one

For details, you can download the template Selecting All Values When the Parameter Value is Empty in the Drop-down Checkbox-Template Parameter.cpt.

2. Example two

For details, you can download the template Selecting All Values When the Parameter Value is Empty in the Drop-down Checkbox-Dataset Parameter.cpt.