JS Execute Query using Widgets for Data Entry-FineReport Help Document

Last update： 2020-12-24

I. Overview

1.1 Requirement

When designing a report, how to execute a query using widgets for data entry?

As shown below, by choosing any region in the drop-down list, corresponding data will be displayed after automatic filtration.

1.2 Solution

Configure a hidden Drop-down Box widget for the parameter pane, assign the value of the current widget to the widget for the parameter pane by adding an After Editing event, and execute the query.

II. Sample

2.1 Create a new report

2.1.1 Create a new dataset

Create a new general report and a new dataset [ds1]. Enter the SQL statement:

SELECT * FROM Sales_Volume WHERE 1=1 ${if(len(region)==0,"","and region='" + region + "'")}

As shown in the figure below:

2.1.2 Design a template

Drag the dataset into the cell and add a Drop-down Box widget for Region in C2, as shown in the figure below:

2.1.3 Configure settings of Data Entry widget

1) Click the Drop-down Box widget added in the cell, click the icon of [Widget Settings], select [area] in the drop-down list of [Widget Name], select [Sales_Volume] in the drop-down list of [Data Dictionary], select [Region] in the drop-down list of [Actual Value], and select [Region] in the drop-down list of [Display Value], as shown in the figure below:

2) Click [Template]＞[Web Attributes]＞[Data Entry Settings], uncheck [Prompt users when leave without submitting], and check [Display Widgets Directly], as shown in the figure below:

2.2 Parameter setting interface

1) Edit the parameter pane, add a Drop-down Box widget, select [region] in the drop-down list of [Widget Name], and uncheck [Visible], as shown in the figure below:

2) Click on any blank space of the parameter pane, and uncheck [Display Nothing Before Query], as shown in the figure below:

Move your mouse over the bottom border of the parameter pane and a double-sided arrow will appear. Set the height of the parameter pane to 0.

Note: If there is a widget for the parameter pane, a downward triangle button will by default appear when you preview the report. Both the upward and downward triangle buttons can be hidden through JS codes.

2.3 Add an After Editing event

Click the widget for data entry, add an After Editing event, and input JavaScript codes, as shown in the figure below:

Input the following JS codes:

//Get the value of the column [Region] selected by the text widget
var area1 = this.getValue();//Get the Drop-down Box Widget for the parameter pane
var area2 = contentPane.parameterEl.getWidgetByName("region");//Assign a value to the Drop-down Box widget for the parameter 
panearea2.setValue(area1);//The parameter pane executes a query_g().parameterCommit();