Parameter query in infinite interval

1. Application scenario

In some reports, numbers (output value, amount) often account for the main proportion, and many of them have such business needs: the parameter pane can use comparison symbols, such as greater than sign ">" or less than sign "<", to define an infinite interval query, the effect is shown in the following figure:

2. Idea

The comparison symbol and the query value are respectively defined as parameters, and the parameter expressions can be defined in the database query statement.

1. Data preparation

Create a new general report, create a new dataset ds1, the SQL statement is:

select * from Sales_Volume

where 1=1

${if(len(fh)=0,"",if(len(Sales_Volume)=0,"","and Sales_Volume"+fh+"'"+Sales_Volume+"'"))}

Note 1: The SQL statement involves two parameters, fh and Sales_Volume. fh is used to set the comparison symbol, Sales_Volume is used to set the query value, and later are used to bind their respective widgets.

Note 2: This statement also achieves the effect of empty selection of all parameters.

2. Design report

Design the report as shown below, and drag the fields in the dataset into the corresponding cells.

3. Set widgets

1) Enter the parameter pane editing interface, the parameters fh and Sales_Volume are bound to the widgets respectively, fh is set as a drop-down box widget, and Sales_Volume is set as a text field widget, as shown in the following figure:

2) Select the drop-down box widget, the data dictionary of the custom widget in the property panel on the right, as shown in the following figure:

4. Preview effect

Save the template, click pagination preview, the effect is shown in I.1.

Note: Support mobile terminal.

Please refer to the completed template: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\自定义参数大小比较.cpt

Click to download: Custom_parameter_size_comparison.cpt