Dataset Parameter

Application Scenario

In industries such as telecommunications and surveillance, a large amount of data is generated daily, resulting in an extremely large amount of data in database tables. However, only a few hundred records of data from the current day is required in reports in most cases.

If you use template parameters, all data must be retrieved first and then filtered, which consumes significant time and greatly affects report query efficiency. However, data filter by dataset parameters is completed during the database query, making query reports designed with dataset parameters more efficient than those with template parameters.

Function Description

When defining a dataset, you can define dataset parameters by adding ${para} clauses to an SQL statement. After the definition, the method of using these parameters is the same as that of the template parameters. For example, when you select New York from the drop-down box and click Query, only sales information of New York is queried in the report, as shown in the following figure.

Note:

Dataset parameters are not bound to any specific dataset. Parameters with the same name across multiple datasets are deemed as the same parameter, with the value of the last assigned value taking precedence.

You can use the parameter macro ${} in a dataset SQL query statement to generate filter conditions dynamically. The execution result of ${} is concatenated with the SQL statement to form the final query statement, which is then passed to the database for execution. Multiple ${} macros can be used in an SQL statement to define multiple parameters.

When you define parameters with ${}, the content enclosed by { } (in which all built-in functions in FineReport, such as the IF and LEN functions, can be used) is the content of the defined parameter.. Within { }, those excluding built-in function names in FineReport, constants, and string concatenation syntax, namely variables, are dataset parameters.

For example, in SELECT * FROM Sales_Volume WHERE Region = '${area}', the dataset parameter defined is area.

Note:

You should try to avoid using {、 } within ${} when defining the dataset parameters, since it may cause parsing failures.

After writing an SQL statement with dataset parameters, you can click the Preview icon on the dataset definition page. If the defined dataset parameters are displayed, the dataset parameters are successfully defined, as shown in the following figure.

If you click OK after entering a value in the Parameter box, the parameter value is transferred into the SQL statement for data query.

The following provides several common application methods. You can adjust these methods according to your actual needs. Note that since dataset parameters are used in SQL query statements, you need to ensure that the SQL syntax of dataset parameters matches that of the corresponding database type.

Simple Application

To define a region parameter for filtering data, you can define a dataset parameter area in the SQL query statement to finally form SELECT * FROM Sales_Volume WHERE Region = '${area}' when creating a dataset.

In the SQL statement, all other content is fixed except for area. If area corresponds to different values, data for different regions will be queried in the dataset.

If you want to perform multi-value query, you can use the IN clause in the SQL statement to define a parameter, for example, SELECT * FROM Sales_Volume WHERE Region in ('${area}').

You need to ensure that the parameter values entered match the database syntax. For example, to query data for New York and California, the SQL statement complying with the SQL syntax is SELECT * FROM Sales_Volume WHERE Region IN ('New York','California'), where the parameter values should be entered as 'New York','California' to be concatenated correctly with the defined ('${area}').

Application with Formulas

You can use all the built-in functions in FineReport when defining dataset parameters. For example, you can use the following SQL statement if you want to define a region parameter. In this case, data of all regions will be queried when the parameter value is empty, and data of the corresponding region will be queried when the parameter value is not empty.

SELECT * FROM Orders WHERE 1=1 ${if(len(area) == 0,"","and [Shipper's region] = '" + area + "'")}

When defining the parameter, you can use LEN function first to obtain the length of the parameter and then use IF function for judgment.

If the length of the parameter area is zero, the content is empty, and the concatenated SQL statement is SELECT * FROM Orders WHERE 1=1.

If the length of the parameter area is not zero, the string and "Shipper's Region" = ', the parameter area, and ' are concatenated into an SQL statement. For example, if the value of the parameter area is Southeast Asia, the execution result of the part which defines the parameter is and "Shipper's region"='Southeast Asia'. The complete SQL statement is SELECT * FROM Orders WHERE 1=1 and "Shipper's region"='Southeast Asia'.

Defining Multiple Parameters

If multiple filter conditions are required for data filter, you can define multiple dataset parameters. The following lists an example.

SELECT * FROM Orders WHERE 1=1 ${if(len(area) == 0,"","and Shipper's region = '" + area + "'")} ${if(len(country) == 0,"","and Shipper's country = '" + country + "'")}

In the above SQL statement, ${} is used twice to define two dataset parameters, namely, area and country. For details about how to define these two parameters, see section "Application with Formulas."

Nested Application

When defining dataset parameters, you can define multiple parameters with nested logical conditions. The following lists an example.

SELECT  * FROM Sales_Volume WHERE 1=1 ${if(len(area)!=0," and Region='"+area+"'",if(len(product)== 0,""," and Product='"+product+"'"))}.

The IF function is a built-in formula in FineReport, and the variables area and product are dataset parameters.

If len(area)!=0 is true (namely, the value of the parameter area is not empty), data will be queried with SELECT * FROM Sales_Volume WHERE 1=1 and Region='area'. If the value of the parameter area is empty, len(product) needs to be judged. If the value of the parameter product is empty (namely, no query condition is concatenated), all data will be queried. Otherwise, data will be queried with SELECT * FROM Sales_Volume WHERE 1=1 and Product='product'.

Defining a Dataset Parameter

Create a dataset ds1 and enter the SQL query statement SELECT * FROM Sales_Volume WHERE Region='${area}' in the database query dialog box. Through the database query statement, the dataset parameter area is defined. Click the Refresh icon in the lower right corner. In this case, the created dataset parameter area is displayed on the dataset parameter management panel below. Set the default value of the parameter to New York. Click OK to finish data preparation and dataset parameter definition simultaneously, as shown in the following figure.

Designing Table Styles

1. Design the table style, as shown in the following figure. Specifically, select Header (through which headers are centered automatically) under Style > Style Setting > Theme for the table, as shown in the following figure.

2. Drag the fields from the dataset into the corresponding cells, center the texts, and add sky-blue outside and inside borders to the whole table, as shown in the following figure.

3. Select cell C2, and choose Cell Element > Basic on the right attribute panel. Select Summary and Sum sequentially from the drop-down boxes in Data Setting, as shown in the following figure.

Adding a Widget for the Parameter

1. Click the edit icon on the parameter panel to enter the setting page of the parameter panel, as shown in the following figure.

2. Click area or Add All from the Component Setting panel (that displays parameters without widgets added) in the upper right corner to add the default widget of the parameter to the parameter panel, as shown in the following figure.

Note:

Parameters are bound to widgets based on the parameter names. That is to say, when the widget names match the parameter names, the parameters and widgets are automatically bound. In this case, assigning values to the widgets on the web terminal is equivalent to assigning values to the parameters.

3. After you add the default widget of the parameter area to the parameter panel, check the effect, as shown in the following figure.

Note:

If you do not want to click Add All or Parameter to add default widgets quickly, you can drag a widget directly from the widget bar to the parameter panel. You can bind the widget to the parameter by modifying the widget name to match the parameter name.

4. Click the edit icon of the custom widget, and select Drop-down Box Widget, as shown in the following figure. In this case, you can select a parameter value from the drop-down box.

5. To display options of parameter values in the drop-down box during report preview, set the data dictionary of the drop-down box to Region field in the Sales_Volume table.

Select the drop-down box widget on the parameter panel, click Attribute on the right attribute panel, and click the edit icon next to Data Dictionary to enter the data dictionary setting dialog box. In Data Dictionary, set Type to Database Table, set Database to FRDemo, set Database Table to Sales_Volume, set both Actual Value and Display Value to Column Name, and select Region sequentially from the drop-down boxes, as shown in the following figure.

Note:

The parameters from the report dataset (ds1 here) cannot be used in the data dictionary of the widget. Otherwise, a circular reference will occur, leading to template errors.

6. Select the label widget and set Widget Value to Region:, as shown in the following figure.

7. Drag the Query button to the left to create a compact layout, making the widget panel more aesthetic. Click the edit button of the parameter panel again to complete the setting of the parameter widget, as shown in the following figure.

Effect Display

PC

Save the report and click Pagination Preview to view the report.

Select New York from the drop-down box, and click Query to view only the sales information of New York in the report.

Select California from the drop-down box, and click Query to view only the sales information of California in the report.

Mobile Terminal

For details about how to view reports on the mobile terminals, see Quick Single Template Preview Through DataAnalyst.

Select New York from the drop-down box, and click Query to view only the sales information of New York in the report.

Select California from the drop-down box, and click Query to view only the sales information of California in the report.

For details, you can download the template Dataset Parameter.cpt.