The visualized esProc Function Assist Editor helps users edit function expressions. You can configure the name, options and parameters of the function directly within the Editor. Below is the Function Assist Editor’s interface:

Select a cell: The selected cell will be used as the function prefix.

Select a function: Select the desired function. Here functions available will be automatically listed according to return value type of the cell selected in “Select a cell”.

Property settings: In this area you can configure the selected function’s parameters and options. The Property auto-adjusts according to the currently-selected function’s name; you can edit the parameter expression when the Value cell displays an edit box, and select an item when it contains a drop-down list. You can also define the desired property value under property expression Exp.

Click the URL in the lower part of the interface to jump to the current function webpage.

Here explains how to use Function Assist Editor through an example:

Example: Retrieve CITIES table and STATES table from esProc’s built-in data source, perform grouping & aggregation on CITIES table, join the summary table with STATES table through foreign key fie3ld STATEID, and write data of the join result table to Excel file STSTE.xls encrypted with a password.

1. Create a splx file

2. Edit cell A1 to connect to demo data source

Select A1, click Edit -> Function Assist Editor or right-click to get Function Assist Editor, enter its interface, and configure the function as follows:

Select a function: connect(db)

Data source name: demo

When using connect(db) function, just select the desired data source from the drop-down list under Data source name’s Value if it is one of the configured ones in Tool -> Join:

Here “demo” is selected. Click “OK” and A1 generates the following expression:

	A
1	=connect("demo")

Configure the connection properties under property value expression Exp if the to-be-connected data source isn’t one of the configured data sources in Tool -> Join. For example, configure MySQL connection:

Click “OK” and get the following cell expression:

=connect("com.mysql.jdbc.Driver","jdbc:mysql://127.0.0.1:3306/mysql?user=root&password=123")

3. Edit cell A2 to retrieve CITIES table from demo data source, and return result as a table sequence

Select A2, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a cell: A1(DB); the selected cell will be used as the function prefix.

Select a function: query(sql)

SQL query statement: select CID,NAME,POPULATION,STATEID from CITIES where CID<?

Parameter: 200

Click “OK and cell A2 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)

4. Edit cell A3 to group CITIES table by STATEID, and sum POPULATION and count CID in each group

Select A3, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a cell: A2(Sequence)

Select a function: group(x:F,…;y:G,…)

Grouping exp: STATEID

Aggregate exp: sum(POPULATION), renamed Total_POPULATION; count(CID), renamed Count_city

Click “OK and cell A3 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)
3	=A2.group(STATEID:SID;sum(POPULATION):Total_POPULATION,count(CID):Count_city)

5. Edit cell A4 to retrieve STATES table from demo, and returns result as a cursor

Select A4, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a cell: A1(DB)

Select a function: cursor(sql)

SQL query statement: select STATEID,NAME,AREA from STATES

Options: Check “Auto-close connection (x)”

Click “OK and cell A4 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)
3	=A2.group(STATEID:SID;sum(POPULATION):Total_POPULATION,count(CID):Count_city)
4	=A1.cursor@x("select STATEID,NAME,AREA from STATES")

6. Edit cell A5 to sort STATES table by STATEID

Select A5, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a cell: A4(Cursor)

Select a function: sortx()

ORDER BY: STATEID

Click “OK and cell A5 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)
3	=A2.group(STATEID:SID;sum(POPULATION):Total_POPULATION,count(CID):Count_city)
4	=A1.cursor@x("select STATEID,NAME,AREA from STATES")
5	=A4.sortx(STATEID)

7. Edit cell A6 to join A5’s cursor with A3’s table sequence through foreign key field STATEID

Select A6, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a cell: A5(Cursor)

Select a function: join()

Foreign key field: STATEID

To-be-joined table sequence: A3(Sequence)

Primary key field: SID

Select exp: Total_POPULATION; Count_city renamed Count_CITY

Options: Check “Inner join (i)”

Click “OK and cell A6 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)
3	=A2.group(STATEID:SID;sum(POPULATION):Total_POPULATION,count(CID):Count_city)
4	=A1.cursor@x("select STATEID,NAME,AREA from STATES")
5	=A4.sortx(STATEID)
6	=A5.join@i(STATEID,A3:SID,Total_POPULATION,Count_city:Count_CITY)

Now data to be written to the file is all prepared and we’ll write them to the file.

8. Edit cell A7 to create data file STSTE.xls

Select A7, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a function: file()

File name: D:/STSTE.xls

Charset: UTF-8

Click “OK and cell A7 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)
3	=A2.group(STATEID:SID;sum(POPULATION):Total_POPULATION,count(CID):Count_city)
4	=A1.cursor@x("select STATEID,NAME,AREA from STATES")
5	=A4.sortx(STATEID)
6	=A5.join@i(STATEID,A3:SID,Total_POPULATION,Count_city:Count_CITY)
7	=file("D:/STSTE.xls":"UTF-8")

9. Edit cel A8 to export data of A6’s cursor to a file

Select A8, and right-click to enter Function Assist Editor interface, and do the following configurations:

Select a cell: A7(File)

Select a function: xlsexport()

Table sequence or cursor to be exported: A6(Cursor)

Fields to be exported: Export all fields by default

Sheet name: STATE_JOIN

XlS file write password: 123456

Options: Check “Import the first row as field names (t)”

Click “OK and cell A8 generates the following content:

	A
1	=connect("demo")
2	=A1.query("select CID,NAME,POPULATION,STATEID from CITIES where CID<?",200)
3	=A2.group(STATEID:SID;sum(POPULATION):Total_POPULATION,count(CID):Count_city)
4	=A1.cursor@x("select STATEID,NAME,AREA from STATES")
5	=A4.sortx(STATEID)
6	=A5.join@i(STATEID,A3:SID,Total_POPULATION,Count_city:Count_CITY)
7	=file("D:/STSTE.xls":"UTF-8")
8	>A7.xlsexport@t(A6;"STATE_JOIN";"123456")

10. Execute script

Execute the script and an Excel file named STSTE.xls is generated under D drive. Double-click the file and enter the password to view it. Below is the content:

Note:

1. For any selected cell, the function cannot be reselected after it has been selected, edited, and confirmed unless its content is cleared; but you can reconfigure its parameters and options.

2. If a manually edited cell expression uses a function that is not supported by the function assist feature for the time being, its result cannot be synchronized into the Function Assist Editor.

3. The function assist feature does not cover all esProc functions. It only supports certain functions related with retrieval, computation and export. More functions will be included in the future.