Reporting tool is good at chart & form design, style of landscaping, query interface, entry & 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. This article will introduce to ways to solve the complex computations in the report.
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.
|1||with A AS|
|3||OVER (PARTITION BY salesMan ORDER BY month)-1 rising_range|
|7||CASE WHEN rising_range>=1.1 AND|
|8||lag(rising_range) OVER (PARTITION BY salesMan|
|9||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 o END is_three_consecutive_month|
|14||SELECT DISTINCT salesMan FROM B WHERE is_three_consecutive_month=1|
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.