Reporting tool is good at charting & form design, style of landscaping, query interface, input & report,
and export & print. It is one of the tools that are applied most
extensively. However, there are quite often complex computations in the report,
which raises a very high requirement for technical capabilities of report
designers, and is one of the biggest barriers in report design. esProc can
cooperate with Java reporting tools and solve with ease the report complex computations.
Case and Comparison
A company has a High Growth
SalesMan of the Year report, which analyzes, mainly through sales data, the
salesmen whose sales amount exceeds 10% for three consecutive months, and
demonstrates the indices such as their sales amount(Sales Amount), sales amount
link relative ratio(Amount LRR), client count(Client Count), and client count link
relative ratio(Client LRR). The report pattern is shown in following table:
The main data source of the
report is the “monthly sales data”: sales table, which stores the monthly sales
record of the salesmen, with salesman and month being the primary key. The
structure is shown in the following table:
It can be seen that the
calculation of the name-list of the salesmen whose sales amount exceeds 10% for
three consecutive months is the most complex part of this report. As long as
this name-list is calculated out, it is possible to use the reporting tool to
easily present the remaining part. Let’s compare how SQL statement and esProc
respectively calculate this name-list.
SQL Solution
01 WITH A AS
02 (SELECT salesMan,month, amount/lag(amount)
03 OVER(PARTITION BY salesMan ORDER BY
month)-1 rising_range
04 FROM sales),
05 B AS
06 (SELECT salesMan,
07 CASE WHEN rising_range>=1.1 AND
08 lag(rising_range)
OVER(PARTITION BY salesMan
09 ORDER BY month)>=1.1 AND
10 lag(rising_range,2)
OVER(PARTITION BY salesMan
11 ORDER BY month)>=1.1
12 THEN 1 ELSE 0 END is_three_consecutive_month
13 FROM A)
14 SELECT DISTINCT salesMan
FROM B WHERE is_three_consecutive_month=1
1.
1-4 lines: Use SQL-2003 window function to obtain
the ”rising_range” of the monthly sales amout LRR of each salesman, where, ”lag”
seeks the sales amount relative to the preceding month. Here, ”WITH” statement
is used to generate an independnet sub-query.
2.
5-13 lines: Continue to use window function to
seek ”is_three_consecutive_month_gains”, the symbol of consecutive gains of slaesmen in the each record, where, ”rising_ranges”
of the recent three months are biggern
than 1.1 at the same time, and this symbol is 1. Otherwise it equals to 0, and
here the technique ”case when” is used. Finally, ”WITH” statement is still used
to generate independent sub-query B.
3.
Line 14: According to the result in the preceding two steps, seek
the salesmen meeting the reporting condition, namely, the record whose “is_three_consecutive_month_gains equals 1. Here
it is necessary to use “distinct” to filter duplicate salesmen.
esProc Solution
A1: Group the
data according to salesman. Each group is all the data of a salesman, which is sorted
by month in ascending order.
A2: Refer to
the calcualtion result of the preceding step, and select the group that meets
the condition from A1. The condition comes from the last cell of A1 operation
area, namely, Cell B3. Both B2 and B3 belong to A1 operation area. By writing
the condition step by step in many cells, it is possible to reduce the
difficulty.
B3: Conditional
judgment. If the LRR of three consecutive months within the group is bigger
than 1.1, then this group of data meets the condition. Here “amount [-1]” is
for the data of preceding record relative to the data of the current record,
amount/amount [-1] represents a LRR comparsion. The pselect() is used to obtain
the serial number within the group, and whenever meeting the first piece of
data within the group that meets the condition, pselect() immediately returns
the serial number and stops repeated calculations.
A4: Obtain the serial
number of the salesmen in A2, and this result is returned through JDBC to the
reporting tool for use.
Comparison
The method to calculate this
case “stepwise” will be very clear, so it is relatively suitable for stored
procedure. But report developers often cannot add stored procedure in the
database at their discretions, so it is generally still necessary to use SQL
statement to solve the problem. For general SQL-92 statement to solve this type
of problem, it will be very troublesome. By using here the SQL-2003 standard
that is not extensively used, it is possible to reduce the difficulty. Even so,
it is still necessary to face large paragraphs of difficult-to-understand SQL.
For common report developers, it is no doubt a huge challenge.
It is more agile and easy for esProc
to solve this type of calculation. esProc provides an expression formula using
grid style similar to Excel®, which naturally proceeds by steps. Cells can
refer to calculation result one another, which saves the great efforts of
complex nested queries as well as unnecessary and scrambled variables
definition. esProc also provides functions on the calculations of mass data,
such as relative position, serial number reference, and step-by-step
calculation after grouping, which can greatly simplify calculation procedure.
From the above, it is obvious
that esProc is better at solving the complex computation in the report.
Feature: a JDBC Interface
esProc is a product with pure JAVA®
structure and provides JDBC interface for JAVA reporting tools to conveniently
call it. The structure schematic is as follows:
In the system adopting JAVA
reporting tool, it is possible for esProc to conduct complex computation,
multiple-datasource operation, and dirty data source collation. Then, the
reporting tool can obtain the result returned from esProc via JDBC in the form
of an access to the database. Finally, the reporting tool can be used to
present the data.
Feature: Computational Capabilities Over-perform SQL
esProc is a tool specially designed
to calculate mass data, and has SQL statement and stored procedure the
capability to. On the one hand, it conducts query, filter, grouping, and
statistics just as SQL statement does; on the other hand, it can also conduct loop
and branch judgment on analysis process just as stored procedure does.
In fact, SQL statement and stored
procedure, which are also mass data calculation tools, have some obvious
defects: Stepwise mechanism is incomplete, set-lization is incomplete, and
there are lacks of serial number and reference. So in the report where is complex
computation, designing a few lines of SQL statement tends to become very
difficult, and also has very high requirement for technical capabilities of
designers.
About esProc: http://www.raqsoft.com/product-esproc
