Overview
Version
| Report Server Version | Plugin Version |
|---|---|
11.0 | V2.14 |
Note:Application Scenario
FineReport provides a simple and convenient method to connect data among various sources. However, this method requires customers to have basic knowledge of SQL usage. In common cases, customers need to copy and paste tested and valid SQL statements to FineReport, which is cumbersome. In this case, FineReport provides the Advanced Database Query plugin for you to simplify SQL entering and seamlessly integrate the SQL query and operation with editing tools.
Function Description
Advanced Database Query supports syntax prompt, auto-completion (supports field names), and code beautification.
Advanced Database Query supports FineReport formulas in SQL.
Advanced Database Query supports visual SQL Builder. (Only supports common databases such as MySQL, MSSQL, Oracle, etc.).
Advanced Database Query generates parameter filtering statements visually.
Scheme Comparison
| Comparison Item | Current Plan | New Plan | ||
|---|---|---|---|---|
Introduction | This is the method this document describes. Before executing, you need to have the Advanced Database Query plugin installed. | This document does not introduce the method. Process data visually in FineDataLink to simplify SQL statements, such as data association, row-to-column, column-to-row, and adding calculated columns. | ||
Interface Comparison |
| SQL editor interface is more beautiful.
| ||
Others | This plan may result in high report development costs, high maintenance costs, and slow data acquisition if you use complex SQL statements in FineReport to obtain target data. | You can use the FineDataLink visualization method to process data and add business annotations. In this case, FineReport report datasets only need to retain the method of obtaining result indicator data with "select * from table", increasing the speed of FR dataset query and report display. |
Plugin Introduction
Plugin Installation
You can download the com.fr.plugin.sqlbuilderplus.v10-2.20.3.zip plugin.
Procedure
After installing Advanced Database Query in the plugin store, create a dataset of type Database Query. The database query interface will be replaced with the advanced database query by default.
You can deselect the checkbox of Directly Replace FR'sBulti-in Editor with Advanced SQL Editor under Server > Advanced Database to restore the default editor in FineReport.
The Define Data Connection Button
You can click the icon in the following figure to add a data connection.
Viewing the Tables Under the Data Connection
Area ① displays the data source, Area ② displays the refresh button for data connection, and Area ③ displays the data tables under data connection.
Auto-Completion
Click the button in Area ①. Then the auto-completion settings pop up.
Area ③ displays the auto-completion shortcut key.
You can add the highlighted function name in Area ④, and add the highlighted keyword in Area ⑤.
You can customize the auto-complete function name in Area ⑥, and customize the auto-complete keywords in Area ⑦.
You can enable or disable auto-completion in Area ②.
Visualization SQL Editor
Click the icon in Area ① to enter the visualization editor, as shown in the following figure.
Area ② displays the data table. Double-clicking the table will add its fields to Area ⑤.
Select the checkbox in Area ⑤ to use this field for querying.
Items in Area ⑥ can rename, summarize, and sort the selected fields in Area ⑤.
You can click Designer in Area ③ to enter the design interface, which allows you to directly click to select.
You can click Syntax in Area ④ to enter the syntax selecting interface. You can write SQL statements and view the SQL statements after operating in the design interface.
Parameter Injection
Write the parameter statement in Area ①, and then click the icon in Area ② to refresh and display the injected parameter. You can check whether the parameter is added successfully and its default value in Area ③.
Visually insert parameter injection.
By entering Column Name, Parameter Name, selecting Widget type, and ticking Select All If Parameter Is Empty, you can generate parameter injection statements. The generation rules are as follows (The interface will determine whether to generate AND based on whether there is WHERE or AND at the end of the current statement.)
Radio scenario:
Simple parameter filtering Column Name='${Parameter Name}'.
Select all parameters if the parameter is empty ${if(len(Parameter Name) == 0,"","and Column Name = '" + area + "'")}.
Multiselect scenario:
Simple parameter filtering Column Name in ('${replace(Parameter Name,",","','")}').
Select all parameters if the parameter is empty ${if(len(Parameter Name) == 0,"","and Column Name in ('" + replace(Parameter Name,",","','") + "')")}.
The parameter injection toolbar in Area ② is displayed by default and can be shown or hidden by clicking the button in Area ①.
Function Insertion
Click the button in Area ①, enter the Formula Definition interface, and use the formula of FR for calculation. After you click OK, the calculation result of the formula will be returned to you and directly used in the dataset, as shown in the following figure.
Other Button Introduction
Icon ①: SQL beautification
Icon ②: Editing paginated SQL
Icon ③: Viewing executed SQL
Icon ④ SQL preview
Icon ⑤ Maximizing the advanced database query interface
The SQL statement after beautification is shown in the following figure.
Permission Management
Permission management is used to assign the view permissions of users to links and database tables in the remote designer environment so that the report design can be performed smoothly.
The administrator configures permissions.
The installation of other plugins, the remote designer, and the local designer of users all require the Advanced Database Query plugin to be installed.
Connect to the remote designer as the admin.
First, go to the menu under Server > Remote Design Permission Management and configure who can connect to the remote designer.
Then go to the menu under Server > Advanced Database Query and assign the view permissions of users who can connect to the remote server to links and database tables.
Users that have remote designer access will be listed in the left area, and all the data connections and the data tables under each connection will be displayed in the right area.
Choose a user on the left, and select the data connections and data tables that the user can view on the right.
You can also select multiple users and click Batch Setting to authorize them in bulk.
After configuration, users connect to the remote designer with their own identity. When the user designs the report, the user selects the database query as the dataset. As a result, the user can only view the links and tables that were ticked above.
Preview effect
Auto-Completion
Parameter Injection and Function Insertion
Notes
Problems
1. Double-clicking the table name will display the field name, and then you can use the field name for auto-completion.
2. Press Ctrl + Space when auto-completion does not work.
3. If non-administrator users want to perform remote design, the administrator needs to assign permissions to non-super administrators at first.
