support

Unisource Reporting Tool Integrates Multiple Data Sources

Target

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:

esProc various database integration-1

It is difficult to achieve by using the traditional programming method, while it becomes easy and simple with esProc.

1. Structure

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.

esProc various database integration-2

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 various database integration-3

esProc can execute multiple SQL, such as A1 and A7. The name of the data source is used to distinguish different databases as below:

esProc various database integration-4

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:

esProc various database integration-5

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.

esProc various database integration-6

Next, create a report, for example, pick the template Coffee.

esProc various database integration-7

Then, Enter SQL in wizard. Notice that “p4” is the name of cellset file, and it is used as the name of stored procedure.

esProc various database integration-8

Then, we can see the execution results as below:

esProc various database integration-9

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:

esProc various database integration-10

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.

esProc various database integration-11

And A8 indicates A5 and A7 are joined by ID as below:

esProc various database integration-12

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:

esProc various database integration-13

A1: The original order data
A2: Reference A1 and group by Client. Click and check the third member as below:

esProc various database integration-14

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.

esProc various database integration-15

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:

esProc various database integration-16

Notice:
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.

esProc various database integration-17

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.