Not all reporting tools support inter-row calculations directly. When they don’t you should write scripts to perform them, which is a big hassle, even bigger when multilayer data grouping is involved. esProc can do the calculations with simple code thanks to its support for order-related calculations. So it’s the best choice to prepare the data needed for creating a report in esProc. An esProc script can be identified as a database stored procedure by the reporting tool for execution, accept parameters from it and return result to it through JDBC. 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:
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:
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.
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:
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.
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:
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:
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:
|Accident and emergency (A&E)||7.03%|
|Ear nose and throat||15.06%|
|Cardinal Community Hospital||44562|
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: