Reporting for DHIS2
Setting up a new report for DHIS2
Below are the steps to setup a new report in Datasense.
- 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.
- 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.
- 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.
- 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:')".
Refer here to configure and schedule a report