So now that we have our base query we are challenged with converting the most of the rows to report on a single line.  For example we want all the vitals to display on one line but the query returns them as multiple rows.  The ‘print when’ expression worked to a certain degree for some fields but the vitals remained a challenge.

It turns out iReport has a nice feature that allows you to write a ’scriptlet’ - essentially a single java file which is called at different times during the report execution and can return values to text expressions for display on the report.

So were were example to create in the java file a couple of helper methods and then the following code which is called with each row and collects the vital sign values.

String hval = getDoubleValue("HEIGHT", 0);
 if (hval != null) {
 height = hval;
 }
 String wval = getDoubleValue("WEIGHTMEASURED", 0);
 if (wval != null) {
 weight = wval;
 }

And then we create a standalone method which returns the formatted vital signs

 public String getVitals() {

 StringBuffer sb = new StringBuffer(256);

 sb.append("Height: ");

 sb.append(height);

 sb.append("  Weight: ");

 sb.append(weight);

 return sb.toString();

 }

This is very handy but even more so for the symptoms as there were a mix of record items and controls capturing the symptoms - but all of these are gathered together as a single comma delimeted line.  It would have probably have been near impossible to do that with normal report writer features.

We export our project as a jar file which needs to be in the lib (C:\Program Files\PatientOS\0.99\server\appserver\server\default\lib) directory for runtime and added to the iReport design tool using Tools->Options–>Add Jar

So now our report design, adding a image looks like this.

The last thing we need was the medications.  This is a little trickier as we did not include the meds in query and adding a subreport is a pain in any report designer.  So we will use the scriptlet.  We could setup all the properties need to call the application server ejb service to get orders for this visit.  But even simpler is to add a custom controller.  We’ll save that for another post though.

We also alter the SQL in the report to replace the hardcoded visit id with $P{visit_id} and create a parameter called visit_id.  In version 0.99 user_ref_id, visit_id, patient_id are passed to the program.

Finally we need to incorporate the report into the UI.  For both the thin client and the web client we can add a button (in 0.99) which displays a report with the following properties

Action: System Display PDF Report
Report Output: Your Report

For the report we add a new one which uses the custom controller Report Default Jasper SQL Output.

We browse and select the jasper file.

Thats it.  0.99 has some new jar files to support iReport 3.7 so as long as those were deployed correctly everything should work fine.

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…