Creating SQL based reports in PatientOS EMR - Part I
iReport
PatientOS is an open source EMR, EHR or really enterprise healthcare information system. It is appropriate to have integration with other great open source products such as iReport. iReport is enterprise report writer which is the GUI front end to JasperReports.
PatientOS supports a number of ways to integrate with iReport including using its data source capabilities but this article will focus on creating SQL based reports.
The current version of iReport is version 3.7 and can be downloaded from sourceforge here.
Database
After downloading the setup and running you can create your first report by using menu File –> New, selecting a template and then choosing JDBC connection.
On the next screen fill in the connection information which for a database called demopos would look similar to this.
The next step is to create a SQL query for the report. In this simple example we are going to create a report for a specific form along with some demographics data.
Our query will be the following.
select p.last_name, p.first_name, r1.display as gender, p.birth_dt, pi.idvalue as mrn, r2.ref_key as record_item_key, r3.ref_key as data_type_key, fr.value_int, fr.value_string, fr.value_date, fr.value_double, fr.value_ref_id, r4.display as value_ref_display, t.term_name as value_term from patients p, visits v, refs r1, patient_identifiers pi, forms f, form_records fr, refs r2, refs r3, refs r4, terms t where v.visit_id = 50000042 and v.patient_id = p.patient_id and p.gender_ref_id = r1.ref_id and p.patient_id = pi.patient_id and pi.source_ref_id > 0 and pi.source_ref_id = 50000051 and v.visit_id = f.visit_id and f.form_type_ref_id = 50000104 and f.form_id = fr.form_id and fr.record_item_ref_id = r2.ref_id and fr.data_type_ref_id = r3.ref_id and fr.value_ref_id= r4.ref_id and fr.value_term_id = t.term_id
For your query you would replace the visit id, change the source ref to match the MRN identifier source and form type to match the form you were pulling from. For now we are hardcoding the visit id but once we have finished testing the report will make that a parameter which will be passed from the application. You can find the form type ref id on the properties of the form. The source ref id you can see from this query - select * from refs where reference_group = ‘IdentifierSource’;
Page Layout
Now that we have the report open we want to move fields around to be displayed. First we will create a page header section by changing the band height of the page section to 40.
Then we drag the patient demographic fields and labels, changing font sizes and other formatting to create a page header.
Instead of having separate first and last name fields we edit the contents of one text field to be
$F{first_name} + ” ” + $F{last_name}
in order to combine the two fields, we also change the font size and bold.
Now can press the Preview button and see the output so far. So we have some header information but now we need to handle the discrete data from the form.
Content Layout
If we wanted to replicate the layout of the original form we would have used the Jasper XML plugin which generates XML that matches the forms controls. However for this report we are going to pick out specific field values and display those. This report is much faster than the XML equivalent - the query runs in around 30 milliseconds.
To be continued…
