Overview
Version
FineBI Version | Functional Change |
---|---|
6.0 | / |
6.0.16 | Added the YYYY-MM-DD HH:MM:SS format in parameters. |
Application Scenario
You can process tables in the database through simple SQL statements and then add the tables to Public Data.
Function Description
You cannot create tables or modify table structures in FineBI through SQL statements. You can only retrieve data of tables from the existing database to FineBI through SQL statements.
The syntax rules of SQL statements need to be compatible with the connected database.
Procedure
Choose Public Data and select a folder on which you have management permissions. For details, see Public Data Management Permission. Click Add Dataset and select SQL Dataset from the drop-down list, as shown in the following figure.
Creating a SQL Dataset Without Parameters
After selecting the needed data connection, enter the SQL statement to retrieve data. Click Preview to view whether the data is correct and click OK to add the table, as shown in the following figure.
The SQL statement is SELECT * FROM Balance_sheet.

Creating a SQL Dataset with Parameters
1. Enter an SQL statement with parameters.
2. Click Refresh.
3. Set Parameter Type and Default Value for parameters in the SQL.
4. Click Preview to view the data retrieval result. All data (later than 2016-12-12 14:15:25) is displayed through filtering.

You can set a dynamic time (like yesterday/last month) when setting the default value for time parameters.
Updating a SQL Dataset
SQL datasets with direct connection data can be used directly after being added. SQL datasets with extraction data can be used after being updated, as shown in the following figure.
Modifying a SQL Dataset
After adding an SQL dataset, you can modify the SQL statement and parameter setting through two entries, as shown in the following figure.
Databases Calling Stored Procedures

1. Stored procedures are only effective for extraction data and cannot be used in databases with direct connection data.
2. Oracle cannot call stored procedures in FineBI.
Introduction to Stored Procedures
Stored procedures are SQL statement sets stored in the databases. FineBI allows you to call stored procedures when you add SQL datasets to obtain tables after SQL statement sets of stored procedures are executed.
Databases that can call stored procedures are ones that can be connected through JDBC and can call stored procedures by executing SQL statements through JDBC.
You can enter the statement for calling stored procedures after the SQL statement to be queried in the self-test tool. If the execution is successful, you can call stored procedures in FineBI.
Calling Example
Different databases have different calling statements. You need to use the corresponding statements for different databases to call stored procedures.
Calling Stored Procedures in MySQL Databases
Enter call Stored procedure name() in the SQL statement box, click Preview to view the calling result, and click OK to save the dataset.
If parameters exist in stored procedures, the calling methods are as follows:
If you use the default parameter value, you can directly enter call Stored procedure name() (for example, call test1()) to call stored procedures.
If you pass parameters without using the default parameter value, you can enter call Stored procedure name ('Parameter value 1', 'Parameter value 2') (for, example, call test('a', '100')).
Calling Stored Procedures in SQL Server Databases
Enter execute Stored procedure name in the SQL statement box, click Preview to view the calling result, and click OK to save the dataset, as shown in the following figure.
If parameters exist in stored procedures, the calling methods are as follows:
If you use the default parameter value, you can directly enter execute Stored procedure name (for example, execute StuCouIN) to call stored procedures.
If you pass parameters without using the default parameter value, you can enter call Stored procedure name 'Parameter value 1', 'Parameter value 2' (for example, execute StuCouIN '7001','2').
Notes
Blank Data Preview Page After Field Adding
Add the Contract Payment Type field and click OK to modify the SQL statement of the SQL dataset, as shown in the following figure.
In this case, the added field is not updated in the data table. You need to click Edit in the right corner of the base table, manually select the added field, and click Save, as shown in the following figure.
Adding the Database View
You can add the database view in FineBI by adding the SQL dataset.
For example, if database views exist in MySQL databases, you can retrieve data to FineBI by creating SQL datasets, as shown in the following figure.
Alternatively, you can choose System Management > Data Connection > Data Connection Management, reconnect databases, and save the operation. That is, you can add views by adding database tables.