The order data of customer is in ERP system and the customer information is stored in CRM system. Now we need to create a report to present the annual sales amount of each customer and the growth rate compared with the previous year. And the optional reporting tool only supports single data source, such as, iReport, Crystal, Birt. It looks like below:
It is difficult to achieve by using the traditional programming method, while it becomes easy and simple with esProc.
The train of thought is: design a cellset file in esProc to conduct cross-database operations. Then make reports access cellset file via standard JDBC. Check the pic below.
Note: The same as accessing to internal database like HSQL, the cellset file name is equivalent to the stored procedure.
2. Implementation process
esProc can connect to multiple data sources simultaneously. Check the pic below, there are two data sources: Oracle and MSSQL.
esProc can execute multiple SQL, such as A1 and A7. The name of the data source is used to distinguish different databases as below:
Through a series of operations, data in A1 and A7 was combined into a single dataset. The detailed data is on the right. A11 indicates exporting A9 to JDBC (Multiple result sets can be exported). It is shown as below:
Then, you only need to create a single JDBC data source in reporting tools, and use the JDBC Driver and URL for esProc as below.
Next, create a report, for example, pick the template Coffee.
Then, Enter SQL in wizard. Notice that “p4” is the name of cellset file, and it is used as the name of stored procedure.
Then, we can see the execution results as below:
The following tabulation process is the same as general reports. Here we don’t go into all of the details. Let’s check the final report:
We can see that esProc enables the integration of data from multiple sources. This is because esProc provides all the capabilities of SQL, and supports cross-database computing.
The coming contents are syntax features of codes mentioned above.
3. Cross-database computing
Check the pic below, A5 completed the calculation of database Oracle: the annual sales amount of each customer and the growth rate compared with the previous year. And A7 Get the ID, Name, Country of customers from MSSQL.
And A8 indicates A5 and A7 are joined by ID as below:
It is a Cross-database computing, and this step is equivalent to the left Join in SQL. Of course, SQL only enables join within the same database, while esProc supports cross-database. The join result is on the right side. Click on the blue font to see the internal members, for example as below, the first line.
You may find that unlike SQL, esProc divides “join” and “select” into two steps. e.g. A9. It adds ”select” to the result of “join”.
This step-by-step method is particularly suitable for complex database computing. Another example, the result of A5: the annual sales amount of each customer and the growth rate compared with the previous year. You must write all the complex computations at one time and this requires high technical capabilities if you are just using SQL. It is much easier to do it with several simple expressions as next feature mentioned.
4. Step-by-step computing
Step-by-step computing in esProc is a basic ability. Check the pic below:
A1: The original order data
A2: Reference A1 and group by Client. Click and check the third member as below:
How to summarize data of each group by year as above? It is A3: regroup by year on the grouped data and summarize the sales amount for each group. Notice that “~” represents the current member and the third group of result is on the right as below.
Next, let’s check A4: Add a column to compute the growth rate compared with last year. The description in business language is: (Current year / previous year) -1. Please notice the third member as below:
sAmount: Current member，current year
sAmount[-1]: Previous member, previous year
Next, let’s check A5: Union all group members So far. Data processing for Oracle is completed.
We can see, stepping can divide complex computing objects into simple computing steps. And it is difficult for SQL to do complex database computing since it does not support the step-by-step method directly.
During the data preparation for reports or data processing, we often encounter complex database computing. At this time, you can use esProc to solve problems.
Besides the cross-database and step-by step computing, esProc has many features, such as, grid-style scripts, ordered set, complete set-lizing, objects reference etc. These characteristics make esProc a more capable business computing developer tool.