.

# How to Solve Complex Computations in the Report

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.

SQL Solution

## SQL Solution

 1 with A AS 2 (SELECT salesMan,month,amount/lag(amount) 3 OVER (PARTITION BY salesMan ORDER BY month)-1 rising_range 4 FROM sales), 5 B AS 6 (select salesMan, 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 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.

## A script Solution

 A B 1 =sales.group(salesMan).(~.sort(month)) 2 ==A1.select(??) =0 3 =~.pselect(B2=if(amount/amount[-1]>=1.1,B2+1,0):3)>0 4 =A2.(salesMan)

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.

Views: 639

Comment