Solution to Complex Computation of Report Data Source

There are many report data source issues involve complex computations. For example, find the products whose sales values are among the top 10 in every state, and present the sales details of these products. Obviously, the most difficult part of this work is to find out the target products and provide it to report through one data source. What is the best approach to solve such kind of problems?

SQL/SP may be the first resort striking your mind because SQL is powerful enough in computing. However, SQL has many drawbacks – all computational logics must be crammed into one statement; unable to be split into several examinable procedures of computation; awkward in debugging and incapable of checking the result at every step intuitively. Plus, SQL lacks the explicit set and therefore no straightforward solution is available to handle the above problem. It is quite cumbersome to perform the necessary intersection operation on a set of grouped data with SQL.

Needless to say, the database administer or the SQL programmer can help you out. But, having these 2 professionals from different sectors prepare a report is obviously an unreasonable resources provisioning. As for the report developers, they focuses on the report formula and layout design. Without the strong SQL background of database administer, they can do nothing but only find it beyond their capability to write the SQL/SP statements with complex logics.

In facts, troubles are far more than this. The raw data like the sales details is most often not saved in database, but in Excel, which is beyond the ability of the database administer. Plus, the cross-database computation is often required to provide the necessary data source for report. In this case, solving the problem does not just involve SQL only.

esProc is a data computational script not only for database, but also for the Txt file or Excel sheet.

 esProc is such a professional developing tool for report data source. Take the computation part in the above case for example. The esProc scripts are:


Because esProc can be referenced through JDBC interface by report, and also supports the external parameters, it is as effective as integrating with SQL/SP.

A mind that is stretched by a new experience can never go back to its old dimensions. esProc will be our first choice to easily solve any complex computations of report data source in the future.


About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Reporting tool and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s