esProc has lots of functions for processing structured and semi-structured data, as well as supports parsing expressions dynamically and using heterogeneous data sources. A reporting tool can pass in parameters, execute an esProc script the same way as it executes a database stored procedure and gets the result set through JDBC.
Below is the structure of integrating an esProc script and a reporting tool:
An example will show you how a reporting tool integrates an esProc script.
The database table SALES3 holds order data. Below is a selection:
You need to calculate the total order amount, the maximum/minimum order amount and the total number of orders each month in a specified year, and transpose the data into 13 columns and 4 rows. That is, the four algorithms constitute the first column named subtotal and each month is a column named 1, 2, 3, 4… .
Step 1: Connect to the database in esProc IDE
You can connect to multiple data sources at one time to perform hybrid computation. In this example MySQL is used to configure JDBC, as shown below:
Step 2: Implement the algorithm in esProc IDE
|
|
A |
|
1 |
$select month(ORDERDATE) as MONTH,sum(AMOUNT) as OSum,max(AMOUNT) as OMAX, min(AMOUNT) as OMIN ,count(ORDERID) as OCount from sales3 where year(ORDERDATE)=? group by MONTH order by MONTH;argYear |
|
2 |
=[“OSum”,”OMAX”,”OMIN”,”OCount”].new(~:subtotal,${to(A1.len()).string()}) |
|
3 |
=A1.run(A2.field(#+1,OSum|OMAX|OMIN|OCount)) |
|
4 |
return A2 |
Perform SQL group and aggregate operations, and then run a loop to transpose every row to column, and fill in values in sequence to each column of A2. A1’s argYear is a report parameter. A4’s result statement outputs A2 to JDBC (by default the statement outputs the last calculation cell A3). Click A2 and you can see the computing result:
Step 3: Add esProc data source for the report
Class name of JDBC driver: com.esproc.jdbc.InternalDriver ( v1.0)
JDBC URL: jdbc:esproc:local://
User name and password are left empty.
The configuration interface for Jasper is as follows:
Step 4: Create data set for the report
After creating a data set for the reporting tool, you can call an esProc script in the same way as calling a stored procedure from the Query Designer. For Jasper, the expression is call transData($P{pYear}), in which the stored procedure name is the name of the esProc script. pYear is the parameter passed into the esProc script by the reporting tool, its values will correspond to esProc parameters according to sequence (rather than by names).
For the reporting tool, there’s no difference between calling an esProc script and calling the ordinary database code. According to routine, after all of this you’ll design report layout and place fields appropriately. Let’s not go into the details here.
See esProc Integration & Application: Integration with JasperReprot for details about configuring and displaying data in a Web application.
The above uses Jasper to explain the normal process of integrating an esProc script by a reporting tool. There are exceptions which will be explained in the following:
Integration by BIRT
BIRT and Jasper are almost the same in carrying out the integration, with the only difference in the use of the parameter. BIRT uses the quotation mark as the parameter placeholder in calling either a stored procedure or a SQL query. So the expression for calling an esProc script by BIRT should be call transData (?). See esProc Integration & Application: Integration by BIRT for details.
In a nutshell, a reporting tool can integrate an esProc script as long as it supports JDBC data source.
Simple script without a file
When an esProc script is simple, it can be written directly in the reporting tool’s Query Designer, without having to create a script file. For example, the following code breaks ANOMALIES field apart in each record to create multiple records.
|
|
A |
|
1 |
=myDB1.query(“select ID,ANOMALIES from data where ID>?”,argID) |
|
2 |
=A1.news(ANOMALIES.array(” “);A1.ID:ID,~:ANOMALIES) |
In Jasper’s Query Designer, you can write the following code: =myDB1.query(“select ID,ANOMALIES from data where ID>?”,$P{pID}) \n =A1.news(ANOMALIES.array(” “);A1.ID:ID,~:ANOMALIES)
The code use the carriage return “\n” to separate lines (and “\t” to separate columns).
The above cellset code can be combined as follows:
|
|
A |
|
1 |
=myDB1.query(“select ID,ANOMALIES from data where ID>?”,argID). news(ANOMALIES.array(” “);A1.ID:ID,~:ANOMALIES) |
So the code in Jasper’s Query Designer can be further simplified as =myDB1.query(“select ID,ANOMALIES from data where ID>?”,$P{pID}).news(ANOMALIES.array(” “);A1.ID:ID,~:ANOMALIES) .
BIRT uses the quotation mark as parameter placeholder. But as the quotation mark is a part of an esProc expression, you need to use the form of “arg1,arg2,arg3” to hold places sequentially. So the query should be =myDB1.query(“select ID,ANOMALIES from data where ID>?”,arg1).news(ANOMALIES.array(” “);A1.ID:ID,~:ANOMALIES) .