A link-relative
ratio is a percentage got by comparing the data of current period and that of
the previous period. A year-on-year growth rate is the rate of change expressed
between the current period and the same one from the previous year. Different reporting
tools have different ways to calculate the link relative ratios and the year-on-year
growth rates. Some do the calculations in SQL, some perform the calculations
using elusive controls, and others can only achieve the goals by writing
complicated report scripts.

esProc
supports handling inter-row calculations and the calculations involving
relative positions automatically, enabling a standard method of building reports
that present the link relative ratios and year-on-year growth rates. The method
applies to various reporting tools. So esProc is an ideal tool for report data source
preparation. The reporting tool regards an esProc script as the stored
procedure, passes parameter to it and gets the returned result after execution
through JDBC. Learn more from

**The following example will show you how esProc works.***How to Use esProc to Assist Reporting Tools.***Case description:**

Compute
link-relative ratio and year-on-year comparison of growth rate of each month’s
sales amount in a specified period and present the results in a report. Below
is a selection from the

*Orders*table:**esProc code:**

A1=esProc.query("select * from
sales3 where OrderDate>=? and OrderDate<=?",begin,end)

A2=A1.groups(year(OrderDate):y,month(OrderDate):m;sum(Amount):mAmount)

A3=A2.derive(mAmount/mAmount[-1]:lrr)

A4=A3.sort(m)

A5=A4.derive(if(m==m[-1],mAmount/mAmount[-1],null):yoy)

**Code explanation:**

*begin*and

*end*are external parameters. Such as,

*begin="2011-01-01"*,

*end="2014-07-08"*(i.e. the data of the current day which can be obtained through

*now()*function). Below is a selection of the query result:

A2: Group orders by the year and the month, and summarize data to get each month’s sales amount. Some of the computed results are as follows:

A3: Add a new field

*Irr*, i.e, the link-relative ratio on a month-on-month basis. The expression is

*mAmount/mAmount[-1]*, in which

*mAmount*represents sales amount of the current month and

*mAmount[-1]*represents that of the previous month. Note that value of the first month’s (i.e. January 2011) link-relative ratio is null. Computed results are:

A4: Sort A3 by the month and the year to compute the growth rate of year-on-year comparison. Complete code should be:

*=A3.sort(m,y)*. Since A3 has been sorted by the year, so we just need to sort it by the month, that is,

*A3.sort(m)*. This has a higher performance. Some of the computed results are:

A5: Add a new field

*yoy*, i.e., the year-on-year comparison of monthly sales amount. The expression is

*if(m==m[-1],mAmount/mAmount[-1],null)*, which means that the comparison can only be made between the same months. Note that the values of year-on-year comparison of months in the initial year (i.e. the year 2011) are always nulls. Some of the computed results are:

## No comments:

## Post a Comment