Reporting Tips

Each table-backed form has a foreign key relationship w/ encounter.id. Notice that the encounter table has a patient_id field. encounter.patient_id ->patient.id.

See the zeports stored procedure sp_drugs_labs for examples of how to build report queries. You'll see that it aggregates data from rpr, arvregimen, safemotherhood, etc. T

Here's some sample queries to extract data from zeprs:

ARV Regimen:

SELECT date_visit, regimen_visit_date, fe1.enumeration AS arvRegimen, referred_art_clinic, e.patient_id as patientId, p.district_patient_id
FROM zeprs.arvRegimen a
LEFT JOIN zeprs.encounter e on e.id = a.id
LEFT JOIN admin.field_enumeration fe1 on fe1.id = a.regimen
LEFT JOIN patient p on p.id = e.patient_id
ORDER BY regimen_visit_date ASC;

Results:

Count of how many visits patients w/ NVP regimens have made:

SELECT COUNT(e.id) AS numVisits, fe1.enumeration AS arvRegimen, e.patient_id as patientId, p.district_patient_id
FROM zeprs.arvRegimen a
LEFT JOIN zeprs.encounter e on e.id = a.id
LEFT JOIN admin.field_enumeration fe1 on fe1.id = a.regimen
LEFT JOIN patient p on p.id = e.patient_id
WHERE fe1.enumeration = "NVP"
GROUP BY p.id

Results:

Interpreting Values

Some of the values in a record may have special meaning. See the item(s) listed in Interpreting values for Enumerations for examples. When preparing a new report, it is a good idea to do a join between pageItem.field_id and form_field.to see the input_type for the pageItem - the values recorded may have a different meaning from the literal value. You can view the code for a paticular input widget from he tags directory. Also, PatientRecordUtils.resolveValue will provide guidance.