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:

esProc_report_irregular_crossrow_1

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:

esProc_report_irregular_crossrow_2

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:

esProc_report_irregular_crossrow_3

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:

esProc_report_irregular_crossrow_4

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:

esProc_report_irregular_crossrow_5

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:

esProc_report_irregular_crossrow_6

The following is the preview of the final report:

esProc_report_irregular_crossrow_7

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.

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 and tagged , , , . 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