esProc Assists Report Development – Irregular Cross Row Calculation

During data reporting there are many unconventional statistical tasks that are difficult to be handled solely by reporting tools, like Jasper and BIRT, or SQL. One case is to specify a particular record in the original data against which we cross-calculate all other records and derive new field values like percentages and sums. esProc, with its powerful computing engine for processing structured data, can assist the handling of the case. It can also be integrated conveniently by the reporting tool. We’ll look at how esProc handles the case through an example.

Database table majorSum stores the total number of patients of a certain hospital and the number of patients of each key department. We want to report proportions of patient number of each key department to the total patient number of the hospital in alphabetical order by the departments and, at the same time, keep the total number of patients in the end. The original data are as follows:

The expected report is as follows:

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 prepares necessary data as follows: 

A1=myDB1.query("select department,patient from majorSum order by department")

Execute the SQL statement to retrieve data from table majorSum of data source myDB1. The result is as follows: 

A2=A1.maxp(patient)

Get the record that stores the total number of patients in the hospital. maxp function gets the record that has the maximum field value – Cardinal Community Hospital in this example.

A3=A1\A2

Remove A2, the record of total number, from A1’s table. Operator “\” represents getting the difference between sets.

A4=A3.run(string(patient/A2.patient,"#.##%"):patient)

Compute proportions of the patient number of each department to the total number. run function computes by looping through A3’s records. string function converts a number into a string. The result is as follows: 

A5=A4|A2

Concatenate the record of the total number of patients and other records containing figures shown by percentage. Operator “|” is equal to union function. esProc supports generic two-dimensional table, so strings and numbers can be stored in the same field. A5’s result is what the report needs: 

A6:result A5

Return A5’s table to the reporting tool. esProc provides JDBC to integrate with the reporting tool, which will identify it as a database. See related documents for the integration solution.

The above step-by-step computation is used for observing the intermediate results conveniently. But for a seasoned programmer, these steps can be condensed into two lines of code:

A1=myDB1.query("select department,patient from majorSum order by department")

A2:result(total=A1.maxp(patient),(A1\total).run(string(patient/total.patient,"#.##%"):patient)|total)

Create a simple list report with, for instance, BIRT. The template is as follows: 

The following is the preview of the final report:

The reporting tool calls the esProc script in the same way as that in which it calls the stored procedure. Save the esProc script as, say irregulProportion.dfx, to be called by call irregulProportion() in BIRT Stored Procedure Report Designer. Besides, esProc supports report parameters too.