Parameter Query for the Infinite Interval

Version

Application Scenario

In some reports, most of the data is in the numeric format (such as production values and amounts). To meet such business requirements, you can define infinite intervals on the query panel with comparison operators such as greater than (>) or less than (<), as shown in the following figure.

Implementation Method

You can define two parameters for the comparison operator and the field whose values are to be queried, respectively. You can define the parameter expression in the database query statement.

Data Preparation

Create a general report and create a data query named ds1 with the SQL statement:

select * from Sales_Volumewhere 1=1${if(len(fh)=0,"",if(len(sales_volume)=0,"","and Sales_Volume"+fh+"'"+sales_volume+"'"))}

Note: 

1. Two parameters, fh and sales_volume, exist in the SQL statement. fh is used to set comparison operators, while sales_volume is used to set the field whose values are to be queried. The two parameters are used to bind the corresponding widgets, respectively.

2. You can realize the effect that all values are selected when the parameter values are empty by the statement.

Table Design

Design the table as shown in the following figure and drag the fields from the dataset into the corresponding cells.

Widget Setting

1. Enter the editing page of the parameter panel, bind the parameters fh and sales_volume to the corresponding widgets, respectively, select Drop-down Box Widget for fh, and select Text Widget for sales_volume, as shown in the following figure.

2. Select the drop-down box widget and customize the data dictionary of the widget on the right attribute panel, as shown in the following figure.

Effect Display

1. PC

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

2. Mobile Terminal

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

For details, you can download the template Parameter Query for the Infinite Interval.cpt.