Multi-database Source Integration in the Unisource Reporting Tool

Target:

The customer order data is stored 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 available reporting tools, such as, iReport, Crystal and BIRT, only support single data source. Below is the desired layout:

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

1. Computing structure

Design a cellset file in esProc IDE to conduct cross-database operations. Then make the reporting tool access the cellset file via standard JDBC. See pic below.

Note: Like accessing an in-memory database like HSQL, the reporting tool will indentify the cellset file name as that of the stored procedure.

2. Implementation process

esProc can connect to multiple data sources simultaneously. In the pic below, there are two data sources: Oracle and MSSQL.

esProc can execute multiple SQL statements, such as A1 and A7. Different data source names represent different databases:

Through a series of operations, data in A1 and A7 has been 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:

Now you’ve created a single JDBC data source for the reporting tool. In the pic below, the esProc’s exclusive JDBC Driver and URL are used.

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

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

Then, we can see the execution result as below:

The subsequent tabulation process follows the general steps of report building. Here we won’t go into details. Let’s view the final report:

We can see that esProc enables the integration of data from multiple sources. This is because esProc not only provides all the capabilities of SQL, but also supports cross-database computing.The following contents are syntax features of codes mentioned above.

3. Cross-database computing

In the pic below, A5 completes the calculation on Oracle database: the annual sales amount of each customer and the growth rate compared with the previous year. And A7 gets customer ID, Name, Country from MSSQL.

And A8 joins A5 and A7 by ID:

It is a cross-database computing, which is equivalent to the SQL left Join. Of course, SQL only enables join within the same database, while esProc supports cross-database join. The join result is shown on the right side. Click on the blue font to see the internal members, for example, the first line as shown below.

You may find that unlike SQL, esProc divides “join” and “select” into two steps, e.g. A9. It performs “select” based on 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 explained in the next feature.

4. Step-by-step computing

Step-by-step computing is the basic esProc ability. See the pic below:

A1: The original order data.

A2: Reference A1 and group it by Client. Click on the cell to view the third member:

How to summarize data of each group by year? It is A3: regroup by year on the grouped data and summarize the sales amount for each subgroup. Notice that “~” represents the current member and the result of the third group is shown on the right as below:

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:

Note:

sAmount: Current member，current year

sAmount[-1]: Previous member, previous year

A5: Union members of all groups. Now the processing of data in Oracle is completed.

The step-by-step method can divide a complex computing object into simple computing steps. It is difficult for SQL to do complex database computing because it does not support the step-by-step method directly.

Complex database computations are common during report data preparation or data processing, and you can use esProc to handle them.

Besides the cross-database computing and step-by step computing, esProc has many other features, such as grid-style scripting, ordered set handling, complete set orientation, objects reference etc. These features make esProc a more capable business computing developer tool.