Reporting for DHIS2

Owned by Nehashri Puttu Lokesh
Last updated: Mar 23, 2017

Setting up a new report for DHIS2

    Below are the steps to setup a new report in Datasense.

  1. Generate and save the templates:- From datasense homepage navigate to Templates tab. Search for the DHIS dataset for which you want to setup the report. This will generate 3 templates.
    • Query Template Suggestion:- This is a json file template for list of queries with a name for each cell for that DHIS dataset. The template will have dummy queries which need to be replaced by actual queries. This should be stored as a json file in "/opt/datasense/lib/dhis_config/aqs_query" folder.

    • Post Template Suggestion:- This is a freemarker file template which will be parsed to get the JSON payload. For each cell in DHIS the value will be put from query-results in above template. This should be stored as a freemarker template file in "/opt/datasense/lib/dhis_config/templates" folder.

    • Config Template Suggestion:- This is a JSON file template which stores the path for above two templates and list of all query-names need to be executed. This should be stored as a json file "/opt/datasense/lib/dhis_config/aqs_config" folder. In this file, you need to replace "[path_to_config]" with "/opt/datasense/lib/dhis_config". It will have names for query file and template files, you will have to change them to above two file names respectively.

  2. Map HRM facility to DHIS Organisation:-Datasense works with HRM facilities and stores data against them. DHIS has a concept of Organisations and it accepts reports against them. In datasensefirst you have to map the HRM facility to a DHIS Organisation. From data-sense homepage navigate to Manage Facilities tab. Here all the facilities will be listed whose data is stored in datasense. Click on the action button for the facility you want to map. It will lead to the search box for DHIS Organisations where you can search and map it to the facility.

  3. Map datasense report to DHIS Dataset:- You need to map a datasense-report to DHIS dataset to tell the system the report content goes to which dataset. To do this from datasense homepage navigate to Manage Reports tab. All the reports which are put as a part of step 1 will be listed there. You need to click on the action button for the report you want to map. This will lead to a search box for DHIS datasets. You can search and map the dataset.


Writing Queries for reports:-

The generated templates will have dummy queries. These need to be replaced with actual queries which will extract data from database for a given cell in DHIS2. Below are the things need to be considered while writing queries.

  • The column value for facility_id in encounter table is not supposed to be compared with a hardcoded value. It will be a parameter named ":paramFacilityId:" having the IDs of facilities which are selected from the UI while scheduling the report. E.g.:- "SELECT COUNT(*) FROM encounter WHERE facility_id=':paramFacilityId:'".
  • While writing queries the starting and last date of reporting period will be compared with parameters ":paramStartDate:" and ":paramEndDate:" respectively. E.g.:- "SELECT COUNT(*) FROM encounter WHERE facility_id=':paramFacilityId:' AND e.encounter_datetime BETWEEN date(':paramStartDate:') AND date(':paramEndDate:')".


 Datasense schema description needed for DHIS2 reports queries

Below are the table-names and relevant columns which will be needed for DHIS2 reporting.

  1. patient:- This tables stores all the information about patients personal data.
    1. patient_hid => HealthId of the patient.
    2. dob => needed when we have to write queries for specific age groups.
    3. gender
    4. present_location_id => present address of patient stored in form of catchment codes. This column can be used when we have to write queries area wise.

  2. encounter:- This table stores information related to encounters.
    1. encounter_id
    2. encounter_datetime => stores the date when this encounter was recorded in CHW-Device/Hospitals.
    3. visit_type.
    4. encounter_type
    5. patient_hid
    6. facility_id => stores the HRM code for the hospital/community-clinic where the encounter happened.

  3. procedures:- This tables stores all the procedures performed in hospitals. While writing queries for procedure we should prefer to use reference-term-code(codings from ICD-10, LOINC etc) if defined for that procedure's concept otherwise concept_uuid for that procedure.
    1. procedure_id
    2. patient_hid
    3. encounter_id
    4. start_date
    5. end_date
    6. procedure_uuid => uuid of TR procedure concept. In the query we need to compare it with the uuid of the procedure concept from TR.
    7. procedure_code => reference-term-code for the procedure concept defined in TR.
    8. diagnosis_uuid => If there was a diagnosis as a part of procedure this will be populated. This also is the concept_uuid of the diagnosis in TR.
    9. diagnosis_code => reference-term-code for the diagnosis concept defined in TR.

  4. diagnosis:- This tables stores all the diagnoses information. While writing queries for diagnosis we should prefer to use reference-term-code(codings from ICD-10, LOINC etc) if defined for that diagnosis's concept otherwise concept_uuid for that diagnosis.
    1. diagnosis_id
    2. patient_hid
    3. encounter_id
    4. diagnosis_datetime
    5. diagnosis_code => reference-term-code for the diagnosis concept defined in TR.
    6. diagnosis_concept_id => uuid of TR diagnosis concept. In the query we need to compare it with the uuid of the diagnosis concept from TR.

  5. immunization:- This table stores all the immunization done. While writing queries for immunization we need to compare drug_id column with uuid of the same drug in TR.
    1. immunziation_id
    2. patient_hid
    3. encounter_id
    4. drug_id => uuid of the TR drug which was given as immunization.
    5. datetime

  6. prescribed_drug:- This table stores all the prescribed drugs.
    1. prescribed_drug_id
    2. patient_hid
    3. encounter_id
    4. prescription_datetime
    5. drug_code => uuid of the TR drug which was given as prescription.
    6. non_coded_name => The name of the drug if it is not from TR. It is not advised to create local drugs and report on them because of consistency issues.

  7. diagnostic_order:- All sort of orderables (Lab order, Radiology Order etc) are stored in this table. While writing queries for diagnostic-orders we should prefer to use reference-term-code(codings from ICD-10, LOINC etc) if defined for that diagnostic-order's concept otherwise concept_uuid for that diagnostic order.
    1. order_id
    2. patient_hid
    3. encounter_id
    4. order_datetime
    5. order_category => Type of order (Lab order, Radiology Order etc)
    6. code => reference-term-code for the order concept defined in TR.
    7. order_concept => uuid of TR order concept. In the query we need to compare it with the uuid of the order concept from TR.

  8. diagnostic_report:- This table stores the results of the orders.
    1. report_id
    2. patient_hid
    3. encounter_id
    4. order_id => ID of the order for which this report is
    5. report_datetime

  9. observation:- This table stores the generic observation which doesn't come any of the above category. Diagnostic Reports will also have results in form of observations.
    1. observation_id
    2. patient_hid
    3. encounter_id
    4. code => reference-term-code for the observation concept defined in TR.
    5. concept_id => uuid of TR observation concept. In the query we need to compare it with the uuid of the observation concept from TR.
    6. parent_id => for a nested observation, all the child observation will have id of their parent observation.
    7. value => value of this observation.
    8. report_id => populated if obs is part of a diagnostic_report.

Refer here to configure and schedule a report