Dynamic Data Table and Dynamic Condition

Version

Function Description

In dataset queries, data tables, data columns, and conditions can all be dynamic. You can realize the effect of dynamic data columns in two ways. The following introduces the dynamic data tables and dynamic conditions separately.

Note:

The data entry function is not supported in the dynamic tables.

Application Scenario

You can use dynamic data tables to query tables in the dataset dynamically, as shown in the following figure.

Implementation Method

For details about the implementation method, see Parameter Query for Any Table in the Database.

Application Scenario

You can use dynamic conditions to filter the data in tables when query conditions are not fixed. For example, sometimes you want to filter the data based on column A, but other times you want to filter the data based on column B, as shown in the following figure.

Implementation Method

When defining the dataset, you can create a dynamic condition by two parameters: one for the data table field name and the other for the field value.

For example, you can create a dynamic condition with the SQL statement SELECT * FROM Sales_Volume where 1=1 ${if(len(name)==0||len(value)==0,"","and "+name+"='"+value+"'")}.

Example

1. Opening the template

Open the template Dynamic Table.cpt in %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter.

2. Modifying the dataset

Modify the SQL statement of dataset ds1 to SELECT * FROM Sales_Volume where 1=1 ${if(len(name)==0||len(value)==0,"","and "+name+"='"+value+"'")}, as shown in the following figure.

Note:

1=1 indicates that all the information will be queried by default.

${if(len(name)==0||len(value)==0,"","and "+name+"='"+value+"'")} means that two parameters, name and value, are defined, which represent the field name and the value, respectively.

If both parameter boxes have values, the and condition, which is and name='value', is concatenated. If either of the parameter boxes is empty, an empty string is concatenated instead.

3. Modifying the parameter panel

Modify the name of the text widget table to name and modify the widget value of the label widget to Field name: on the parameter panel.

Add a label widget, set the widget value to Value:, and add a text widget named value, as shown in the following figure.

4. Modifying the template

Delete the first, second, and third rows in the template, and redesign the table style as shown in the following figure.

Red arrows indicate that filter conditions have been added. When the value in cell A3 is empty, the corresponding row will be hidden to ensure that the report content is not displayed when no data is returned from the parameter query.

Effect Display

1. PC

Save the template and click Pagination Preview. To obtain the data in the region of New York, enter the dynamic condition that Field name is Region and Value is New York. The following figure shows the effect.

Note:

The content entered in the Field name box must be actual field names in the data table. Otherwise, an error will occur. Condition queries can only be performed when both boxes contain content. Otherwise, all the information will be queried.

2. Mobile terminal

The following figure shows the preview effect on the DataAnalyst app and HTML5 terminals.

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.

For details, you can download the template Dynamic Data Dtable.cpt.

2. Dynamic condition

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

For details, you can download the template Dynamic Condition.cpt.