Not all reporting tools support inter-row calculations directly. When they don’t, you need to write scripts to implement them, which is really a big hassle, even bigger when multilayer data grouping is involved. esProc can deal with this situation thanks to its support for order-related calculations. An esProc script can be identified as a database stored procedure by the reporting tool for execution. See How to Use esProc to Assist Reporting Tools to learn more.
The following examples show inter-row calculations that appear frequently in report development and their esProc solutions.
Link relative ratio and year-on-year comparison
The database table sOrder stores orders per seller per day. The report needs to present results of comparing the sales amount per month during a specified time period with that of the previous month and with that of the same month from the previous year. Below is the source data:
Script for calculating link relative ratio:
| A | |
| 1 | $select year(OrderDate) y, month(OrderDate) m, sum(Amount) mAmount from sOrder group by y,m where OrerDate>=? and OrderDate<=? ;begin,end |
| 2 | =A1.derive(mAmount/mAmount[-1]:lrr) |
First perform the SQL group and aggregate to get sales amount per month per year, and then calculate link relative ratio using the formula “sales amount of the current month/sales amount of the previous month”. Here’s the result:
Script for comparing sales amount between same months on an annual basis:
| A | |
| 1 | $select year(OrderDate) y, month(OrderDate) m, sum(Amount) mAmount from sOrder group by y,m order by m,y where OrerDate>=? and OrderDate<=? ; begin,end |
| 2 | =A1.derive(if(m==m[-1],mAmount/mAmount[-1],null):yoy) |
Perform a SQL sort on the grouped data by months and years, and then calculate the rate with the formula “sales amount of the current month/sales amount of the same month from the previous year. Here’s the result:
Monthly cumulative sales amount
Calculate the cumulative sales amount per seller per month. The cumulative amount will be cleared at the end of the year.
esProc script:
| A | |
| 1 | $select SellerId,year(OrderDate) y,month(OrderDate) m,sum(Amount) mAmount from sales3 group by SellerId,y,m |
| 2 | =A1.derive(if(SellerId==SellerId[-1]&& y==y[-1],mAmount+accum[-1],mAmount):accum) |
First perform a SQL group and aggregate to calculate the sales amount per seller per month in one year, and then perform the inter-row calculation with the formula “accumulated sales amount of the current month = sales amount of the current month + the accumulated sales amount of the previous month”. Here’s the result:
Balance of every term for one account
data.csv stores information of deposit into and withdrawal from a bank account whose initial balance is 45. Now you need to calculate the balance of every term according to this file. Below is the source data:
esProc script:
| A | |
| 1 | =file(“D:\\data.csv”).import@t(;”,”) |
| 2 | =(t=45,A1.derive(t=t+Inputs-Outputs:Balance)) |
Import the file with comma used as the separator, and calculate the current balance using the formula “previous balance + current deposit – current withdrawal”. Here’s the result:
Daily inventory of multiple products
The database table inout stores warehouse-in and -out records per product per day during continuous days and the initial inventory is zero. You need to display the data in a report and add an extra column to represent the stock per day.
esProc script:
| A | |
| 1 | =myDB1.query(“select *,0 as stock from inout order by date”) |
| 2 | >A1.group(product).run(~.run(in-out+stock[-1]:stock)) |
| 3 | return A1 |
Calculate current day’s stock for each kind of product (through data grouping) with the formula “current day’s warehouse-in – current day’s warehouse-out + warehouse stock of the previous day”. esProc uses [-1] to represent the previous record and produces intuitive expression. Here is the result:
For discontinuous days, you need to align data with a sequence of consecutive dates before calculating the inventory. The script is as follows:
| A | |
| 1 | =myDB1.query(“select * from inout”) |
| 2 | =periods(argBeginDate,argEndDate) |
| 3 | =A1.group(product;product:p,~.align(dateList,date).new(p:product,dateList(#):date, ifn(in,0):in, ifn(out,0):out, stock[-1]+in-out:stock)) |
| 4 | =A3.conj(#3) |
Installment loan
The loan table stores loan information, including total loan amount, payment terms by month and annual interest rate. You need to build a grouped report in which details of each term of payment for every loan record – payment, interest, principal, principal balance – will be presented. Below is the source data:
esProc script:
| A | |
| 1 | =myDB1.query(“select * from loan”) |
| 2 | =A1.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment) |
| 3 | =A2.((t=LoanAmt,Term.new(A2.LoanID:LoanID, A2.LoanAmt:LoanAmt, A2.mPayment:payment, A2.Term:Term,A2.Rate:Rate, t*A2.mRate:interest, payment-interest:principal, t=t-principal:principlebalance))) |
| 4 | =A3.conj() |
First calculate the monthly interest rate and each term’s payment for every loan using a formula; then create a two-dimensional detail table for each loan according to the available information and calculate the current term’s interest, principal and principal balance; finally concatenate these detail tables. Below is the detail table for loan 1:
Calculating inter-row ratios irregularly
The database table majorSum stores the number of patients in a hospital’s every key department and the total patient number in the hospital. You need to present the ratio of the patient number in each department to that in the whole hospital, and keep the total patient number of the hospital at the end. Below is the source data:
The desired layout:
esProc script:
| A | |
| 1 | =myDB1.query(“select department,patient from majorSum order by department”) |
| 2 | =A1.maxp(patient) |
| 3 | =A1\A2 |
| 4 | =A3.run(string(patient/A2.patient,”#.##%”):patient) |
| 5 | =A4|A2 |
First find the record of total patient number of the hospital (which has the largest patient number) using maxp function; use “\” to perform a difference operation to get the records of patient numbers in departments and calculate the required ratio; and finally use “|” to concatenate the newly generated records. Here’s the result:
