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

Version

Problem

When you use a parameter to query data in a report, no data in the report will be displayed after you click the query button if the parameter value is empty in the widget (meaning that no parameter value is entered in the widget), as shown in the following figure.

The required effect is that all data in the report will be displayed after you click the query button when the parameter value is empty in the widget. In this case, all the content in the report is displayed if no filter condition exists, which means no data is filtered when no value is selected.

The following figure shows the effect.

Implementation Method

You can use a template parameter or a dataset parameter to query the data in the report. Meanwhile, you can modify the filter condition or the method of defining the dataset to select all values when the parameter value is empty.

Dataset Creation

To query all the data from the Orders table, create a database query ds1 with the SQL query statement SELECT * FROM ORDERS, as shown in the following figure.

Template Parameter Defining

1. Define a template parameter Region and set the default value of the parameter to Southeast Asia, as shown in the following figure.

2. Enter the editing page of the parameter panel and click Add All. Set Database Table to Orders, and set Actual Value and Display Value to Shipper's region separately in Data Dictionary, as shown in the following figure.

Report Style Design

Design the report style according to the description shown in the following table.

Filter Condition Setting

Add a data filter condition for cell A2. Double-click cell A2, and select Filter in the pop-up data column dialog box. Add a common condition, which is Shipper's Region Equal to the formula if(len($Region)==0,nofilter,$Region), click Add, and click OK. In this case, you can add a filter condition to select all the data when the parameter value is empty, as shown in the following figure.

Note:

nofilter means no filter. The formula if(len(Region)==0,nofilter, $Region) means that if the value of the parameter Region is empty, the data will not be filtered. Otherwise, the data is filtered based on the parameter value.

Dataset Creation

When defining the dataset parameter, you can set a condition for the parameter to realize the effect of selecting all values when the parameter value is empty. Create a dataset ds1 with the following SQL query statement.

SELECT * FROM ORDERS

where 1=1  

if(len(Region) == 0,"","and Region = '" + Region + "'")

In this case, the dataset parameter is Region, and the default value of the parameter is set to Southeast Asia, as shown in the following figure.

The following explains the clauses in the statement.

where 1=1 means that the condition is always true, preventing errors caused by the absence of valid content after the WHERE clause when no parameter condition exists.

len(Region) == 0 means that value of the parameter Region is empty.

Region in the middle of "and Region = '" + Region + "'" means to obtain the parameter value, and '+' is used for string concatenation.

if(len(Region) == 0,"","and Region = '" + Region + "'") means:

If the value of the parameter Region is empty, the query statement is SELECT * FROM ORDERS.

If the value of the parameter Region is not empty, the query statement is SELECT * FROM ORDERS WHERE 1=1 and Region='${Region}'.

Report Style Design

Design the report style according to the description shown in the following table.

Parameter Widget Adding

1. Enter the editing page of the parameter panel and click Add All. Set Database Table to Orders, and set Actual Value and Display Value to Shipper's region separately in Data Dictionary, as shown in the following figure.

2. Modify Widget Value of the label widget to Shipper's Region:, as shown in the following figure.

PC

Save the report and click Pagination Preview. The following figure shows the preview effect.

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

Problem

After you complete the above-mentioned setting, all data will be displayed after you click the query button when the parameter value is empty. So how to display all the data during preview?

Solution

After completing the above-mentioned setting, you just need to deselect Display Nothing Before Query in Component Setting, as shown in the following figure.

1. Example one

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

2. Example two

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