When Value of Drop-down Checkbox is None, Show All Data

1. Problem description

When using parameters for filtering, filtering will be carried out according to the selected value after selecting a parameter value. However, how to achieve the effect as shown in the following figure, that is, all values in the data table can be queried when no parameter value is selected in the drop-down checkbox. The effect is shown below:

2. Solution

You can select template parameters or dataset parameters from the drop-down checkbox. You can uncheck "Display Nothing Before Query" to realize displaying all data without click Query when parameter is none.

1. Create a dataset

Add a dataset ds1, SQL statement: SELECT * FROM Sales_Volume.

2. Design the report

Add the table header on the first row and drag the fields of dataset ds1 into the cell in turn, as shown below:

3. Add the widgets

1) Add a label widget with the value set to Region:, as shown below:

2) Add a "Drop-down Checkbox" widget named Region, select the Database Table for the Data Dictionary, the database is FRDemo, the database table is Sales_Volume, select Region for both the Actual Value and Displaye Value, and the Return Value Type is Array, as shown in the following figure:

3) Add Query widget, as shown below:

4) Click the blank of the parameter pane, and in the Attributes on the right, uncheck "Display Nothing Before Query", as shown below:

4. Set the filter

Double-click A2, select the Filter, Available Columns as Region, then select Formula and enter: if(len($Region)==0,nofilter,$Region), as shown in the figure below:

Note: nofilter said not filter, the formula "if(len($Region)==0, nofilter, $Region) " means that if the parameter "Region" is empty, said don't filter the data, if not empty, the data will filter by "Region"..

1. Create a dataset

Add a dataset ds1, SQL statement is: SELECT * FROM Sales_Volume where 1=1 ${if(len(Region) == 0, ""," and Region in ('" + Region + "')")}

${if(len(Region) == 0, ""," and Region in ('" + Region + "')")} indicates that if the paramter "Region" is empty, no filtering is performed. If the "Region" is not empty, the data will filter by "Region". As shown below:

Note:

Where 1=1 indicates that the condition is always true, preventing error when there is no parameter condition.

len(Region)==0 indicates that the parameter "Region" is empty.

"Region" between"and Region in ('" + Region + "')")} means get the value of parameter, and '+' is a string concatenation symbol.

2. Design the report

The report design is shown in Section II.2 of this article.

3. Add the widgets

Edit the parameters pane, select Add All, select the Data Dictionary of Drop-down Checkbox widget to select the database table, the database is FRDemo, the database table is Sales_Volume, select Region for both the Actual Value and Display Value, and set the Return Value Type is String. The delimiter is ',', as shown below:

Click the blank of the parameter pane, and in the Attributes on the right, uncheck "Display Nothing Before Query", as shown in the figure below:

1. PC

Click "Pagination Preview". Without select parameter value, click "Query" directly. The effect is shown in Section I.1 of this article.

2. Mobile

Preview effect of App and HTML5, as shown below:

1) Example 1

Please refer to the completed template: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\Checkbox_Selects_All(template).cpt

Click to download the template: Checkbox_Selects_All(template).cpt

2) Example 2

Please refer to the completed template: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\Checkbox_Selects_All(dataset).cpt

Click to download the template: Checkbox_Selects_All(dataset).cpt