Dynamic table and dynamic conditions

In the data set query, data tables, data columns and conditions can all be dynamic. There are two ways to implement dynamic data columns. Below we will introduce dynamic data tables and dynamic conditions respectively.

1. Application scenarios

The dynamic data table is used to dynamically query the tables in the database.

2. Implementation ideas

• Use the TABLEDATAFIELDS() formula to get the column name of each column of the data set  

• Use the ds1.select() formula to get the contents of the corresponding column. For the use of ds1.select() formula, please refer to the use of ds1.select() and ds1.group() functions    

3. Example

1) Data preparation

In the FineReport designer, click "File>New General Report" to create a new template data set ds1, the SQL statement is select * from ${table} . As shown below:  

2) Template design

• Right-click on cell A1 and select "Cell Element>Insert Formula", the formula is: TABLEDATAFIELDS("ds1") . As shown below: 

• Right-click on cell A2 and select "Cell Element> Insert Formula", the formula is: ds1.select(A1) . As shown below: 

• Select cell A1 , select "Horizontal" in the right property panel "Cell Attributes> Expand> Expansion Direction", select cell A2, and select "Cell Attributes> Expand> Expansion Direction" in the right property panel "Vertical". The following figure shows the setting steps of cell A1, and the setting steps of cell A2 are the same as those of cell A1 . As shown below:   

3) Parameter panel

• Add the parameter "table" in the parameter panel, as shown in the figure below:

• For the wdiget, select "drop-down box widgetl" and set the widget value to "sale". The type setting of the data dictionary is selected as "formula", and the actual value formula is: TABLES("FRDemo") . As shown below:

4) Effect preview

• PC terminal

The effect on the PC side is shown in the figure below: 

• Mobile

The effect of App and HTML5 is shown in the figure below:  

1. Application scenarios

Dynamic conditions are used when filtering data in the table, and when the query conditions are uncertain. For example: sometimes I want to use column A for filtering, and sometimes I want to use column B for filtering.

2. Implementation ideas

When defining the data set, define the condition as two parameters, one is the data table field name, and the other is the field value.

For example, the SQL statement is defined as: SELECT * FROM Sales_Volume where 1=1 ${if(len(name)==0||len(value)==0,"","and "+name+"='"+value+" '")}

3. Example

1) Open the template

Open the template: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\DynamicSQL\dynamic table.cpt 

2) Modify the data set

Modify the data set ds1 to: SELECT * FROM sales where 1=1 ${if(len(name)==0||len(value)==0,"","and "+name+"='"+value+ "'")} , as shown in the figure below: 

Note: 1=1 means that all information is queried by default. 

${if(len(name)==0||len(value)==0,"","and "+name+"='"+value+"'")} means to define two parameters name and value , respectively Represents the field name and value. When there is a value in the parameter box, splice the and condition, that is, and name='value' ; when there is no value in the parameter box, splice an empty string. 

3) Modify parameter interface

The text widget parameter interface table widgets name to name , label widgets the widget value to the Field name: .

Add a label widget, the widget value is: value:, add a text widget Value , as shown in the following figure:

4) Modify the template

Delete rows 1, 2, and 3 in the template, and redesign the table style, as shown in the following figure:

4. Effect preview

1 ) PC terminal

Save the template and click " Pagination Preview ", and enter dynamic query conditions such as: the field name is "Region" and the value is "East China" , you can get the data for the region as East China. The effect is as shown in the figure below:

 Note: The content entered in the field name box must be the field name of the data table, otherwise an error will be reported! The conditions can be searched only if the content is entered in both boxes, otherwise all information can be searched.

2) Mobile

The effect of App and HTML5 is shown in the figure below:  
 

1. Dynamic data table

The completed template can be found in: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\DynamicSQL\Dynamic_data_table.cpt

Click to download the template:Dynamic_data_table.cpt

2. Dynamic conditions

The completed template can be found in: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\DynamicSQL\Dynamic_conditions.cpt

Click to download the template: Dynamic_conditions.cpt