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

1. Problem description

When you use parameters to query data, if the parameter value in the widget is empty, no data will be displayed after you click Query. As shown below:

We need to achieve the effect that when the parameter value in the widget is empty, click Query, the report will display all the data. That is, when there is no filter, all the data of the report are displayed.

As shown below:

2. Solution

You can use template parameters and dataset parameters to query data, and modify the filter and dataset definition method to realize drop-down box selects all when parameter is none.

1. Create a dataset

Create a new dataset ds1 and enter the SQL statement SELECT * FROM Sales_Volume to query all the data in the table. As shown below:

2. Define a template parameter

1) Define a template parameter "Region" and set its default value to "East China". As shown below:

2) Edit the parameters pane, click "Add All", and set the Data Dictionary of the drop-down box widget as Region field in the Sales_Volume table. As shown below:

3. Design the report

Follow the instructions in the table below to style the report. As shown in the following table:

4. Set the filter

Add Filter to cell A2. Double click A2, select Filter > Common, choose Formula and enter if(len($Region)==0,nofilter,$Region), click Add, click OK, that is, by adding filter to achieve the expected effect. As shown 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

Set conditions when defining dataset parameters to achieve the expected effect, create a new dataset ds1, enter the SQL statement: SELECT * FROM Sales_Volume WHERE 1=1 ${if(len(Region) == 0,"","and Region = '" + Region + "')} .The default setting parameter is East China, as shown in the following figure:

The statements in the formula are commented as follows:

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 ='"+Region+" ' " means get the value of parameter, and '+' is a string concatenation symbol.

${if(len(Region)==0, ""," and Region = ' " + Region + " ' ")}, said:

If Region is empty, the query statement is equivalent to the following: SELECT * FROM Sales_Volume

If Region is not empty, the query statement is equivalent to the following: SELECT * FROM Sales_Volume WHERE 1=1 and Region='${Region}'

2. Design the report

Follow the instructions in the table below to style the report. As shown in the following table:

3. Add the widget

Edit the parameter pane, click "Add All", and set the Data Dictionary of the drop-down box widget as Region in the Sales_Volume table. As shown below:

1. PC

Save the report, then click Pagination Preview to view the effect. As shown below:

2. Mobile

The effect of App and HTML5 is the same, as shown below:

Problem description

After setting according to above, when parameter is empty, we need click query to display all data, how to set preview to display all data without click query?

Solution

Uncheck "Display Nothing Before Query" of the parameter pane, as shown below:

1) Example 1

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

Click to download the template: Drop-down_Box_Selects_All.cpt

2) Example 2:

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

Click to download the template: Drop-down_Box_Selects_All(Dataset).cpt