Version: 1.2 | Release Date: 07/02/2024

The Steps to create EBI(Enduser-driven Business Intelligence)

1. Create Query，we use Query to retrieve data from database directly.
2. Create QuerySet，we use QuerySet to connect multiple Query together and QuerySet can be used to generate an EBI report or Combined QuerySet for further manipulate.
3. Create Combined Set(Option Step)， we use Combined Set to combine multiple QuerySet together, the combine action support: Minus / Union / Union all / Interset.
4. Create EBI, The EBI Builder use QuerySet or Combined Set to create an EBI Report
5. Export EBI

Create Query

You write some script to retrieve data from Database, the scripts combined standard SQL grammar string with custom Input Parameter.

select a.*, now() as testTimeStamp, '12:08:34' as testTime
from employee a
where ${[lookupCond('a.dept', @D_deptFilter)]}
and ${[lookupCond('a.position', @D_posFilter)]}
and ${[criteriaForMySQL('a.sex', @D_sexFilter)]}
and ${[criteriaForMySQL('a.createDate', @D_createDateFilter)]}
and ${[lookupCond('a.id', @D_idFilter)]}
and ${[lookupCond('a.expired', @D_expiredFilter)]}
and (?{[@D_empCode]} = '' or a.code = ?{[@D_empCode]})

The script you wrote would be compiled by EBI engine and generate the executable script as below

select a.*, now() as testTimeStamp, '12:08:34' as testTime
from employee a
where a.dept in (SELECT `id` FROM t_qt_IBSWTCIQGG WHERE id !=0)
and a.position in (SELECT `id` FROM t_qt_HBDTYCMZAC WHERE id !=0)
and a.sex = ?
and a.createDate < ?
and a.id in (SELECT `id` FROM t_qt_GUAEMVFIDM WHERE id !=0)
and 1 = 1
and (? = '' or a.code = ?)
 {'male', 2019-09-11 10:50:49.549, '', ''}

There are three types of custom input parameters, you should define it in the [Input Parameter] Tab and reference it as D_xxxx (xxxx = the parameter code)

1. Lookup
2. Criteria
3. Primitive

The parameters you defined here finally affect the GUI generated by [EBI Builder]

Lookup Parameter

Every Lookup parameter represent One Turbo Filter on the GUI, the grammar of the Lookup Parameter as below:

${[lookupCond('a.dept', @D_deptFilter)]}

The condition above with the parameters will be translated into the real script. End user has the opportunity to add some extra filters in Turbo Filter which will be appended to id != 0.

a.dept in (SELECT `id` FROM t_qt_IBSWTCIQGG WHERE id !=0)

Criteria Parameter

You need to choose the type of Criteria Parameter furthermore within (Text、Integer、Decimal、Logical\Boolean、Date、Datetime) the grammar of the Criteria Parameter as below:

${[criteriaForMySQL('a.createDate', @D_createDateFilter)]}

The real script as below，? represent a placeholder parameter for script，< is decided by what the user-chosen on the GUI.

a.createDate < ?

The Criterial Parameter will be displayed in the [Report Option] section of EBI, the type of Criterial Parameter decide the Operators can be used. Such as Date type, the available Operators listed below:

Primitive Parameter

Include: Text、Integer、Decimal、Logical\Boolean、Date、Datetime

Primitive Parameter will be displayed in the [Report Option] section of EBI， the Operator is fixed as :=

You use the grammar below to reference the parameter and EBI engine will replace it with real value.

?{[@D_empCode]}

Data Handler

This function is used for Java level filtering and processing of Query results. It can perform complex calculations on Query results line by line in Java.

You only need to specify the class name when you use it (the inner class needs to be specified with the $ symbol), and the Class must implement the QueryRowHandler interface.

public abstract class QueryRowHandler{
    
    protected abstract void init();
    
    protected abstract boolean pass(SqlTable rowData);
}

Create QuerySet

Extend multiple Querys with horizontal links

Create Combined QuerySet

Collecting multiple QuerySets

Create EBI

Create EBI via [EBI Builder]. The current Query Source Type is only QuerySet and Combined QuerySet.

The EBI after creation can be added to the Menu as an entry through the UDF Menu.

Developers can also modify it by xml, pay attention to the code of EBI equal to the code of Menu.

<menu code="raindrop_test" messCode="empbi.hr" src="view/module/ebi/ebi" mType="EBI" apDebug="true">
	<inherit name="ebi_module_fc"></inherit>
</menu>

Export EBI

EBI can be imported/exported via [EBI XML Console]