A Standard Method of Doing Inter-row Calculations in Report Creation

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:

esProc_report_cross_rows_1

Script for calculating link relative ratio:

esProc_report_cross_rows_2

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:

esProc_report_cross_rows_3

Script for comparing sales amount between same months on an annual basis:

esProc_report_cross_rows_4

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:

esProc_report_cross_rows_5

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:

esProc_report_cross_rows_6

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:

esProc_report_cross_rows_7

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_report_cross_rows_8

esProc script:

esProc_report_cross_rows_9

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:

esProc_report_cross_rows_10

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_report_cross_rows_11

esProc script:

esProc_report_cross_rows_12

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:

esProc_report_cross_rows_13

For discontinuous days, you need to align data with a sequence of consecutive dates before calculating the inventory. The script is as follows:

esProc_report_cross_rows_14

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_report_cross_rows_15

esProc script:

esProc_report_cross_rows_16

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:

esProc_report_cross_rows_17

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:

esProc_report_cross_rows_18

The desired layout:

department patient
Accident and emergency (A&E) 7.03%
Anaesthetics 12.37%
Breast screening 12.37%
Discharge lounge 14.61%
Ear nose and throat 15.06%
Haematology 17.43%
Neurology 5.23%
Cardinal Community Hospital 44562

esProc script:

esProc_report_cross_rows_19

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:

esProc_report_cross_rows_20

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Reporting tool. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s