Building custom reports
Details on how to creating your own reports using Metabase, and the reporting tables.
Metabase is a tool that allows us to explore the data in Simple, and build reports without engineering support. If you are new to Metabase, you can start with one of our predefined templates, and explore further from there.
Alternatively, you can just click on "Ask a new Question", and choose "Custom Question". You can refer to Metabase's own documentation for a better understanding of how to use the query building interface.
Once you have generated a report, you can save it, share it with others, or even download the data to further analyse as spreadsheets or in other contexts.
Metabase is available for the following Simple environments.
Environment | URL | Purpose |
Production India | India’s IHCI program | |
Production Bangladesh | Bangladesh’s NHF hypertension control program | |
Production Ethiopia | Ethiopia's MOH hypertension control program | |
Production Sri Lanka | Sri Lanka's MOH hypertension control program | |
Sandbox | A stable playground for the Simple team to work in |
If you're already familiar with Simple's data model and Metabase, you can get started with this quick demo.
To get started, open up the custom report template. Template links are provided below.
To start writing your custom report, click on the “Show Editor” button shown below.
This will open the report editor. It will look something like this. The key features are labeled in the image below.
As is, this template generates a monthly report of cumulative hypertensive patients assigned per district in a single state over the last 24 months.
Add filters to the purple “Filter” section to limit the report to what you care about. For example, if you want a monthly cumulative report of controlled patients in this state, add the following filter:
Htn Treatment Outcome in Last 3 Months = controlled
See the Reference at the end of this document for a full list of options.
You can modify the green “Summarize” section on the right to add more facets to segment the results. For example, you can break down the results by facility by adding the following grouping:
Assigned Facility Slug
See the Reference below for a full list of grouping options.
Read on further to understand the data in each of the reporting tables you can access in the questions on Metabase.
The following are the key properties that can be used to filter and group the report’s results in the purple “Filter” section.
Field name | Field description |
Month date | The reporting month |
Assigned organization slugAssigned state slugAssigned district slugAssigned block slugAssigned facility slug | The organization, state, district, block, or facility that the patients are assigned to. |
Registration organization slugRegistration state slugRegistration district slugRegistration block slugRegistration facility slug | The organization, state, district, block, or facility that the patients were registered in. |
Hypertension | Is the patient diagnosed with hypertension? (yes/no) |
Diabetes | Is the patient diagnosed with diabetes? (yes/no) |
Htn Care State | Is the patient under care, lost to follow-up, or dead |
Treatment Outcome In Last 3 Months | Patient’s controlled status based on the last 3 months. Possible values are:
|
Months since registration | Months since the patient’s registration, as of the reporting month |
Months since visit | Months since the patient’s last visit (with or without a BP), as of the reporting month |
Months since BP | Months since the patient’s last BP measure, as of the reporting month |
To run a report on registrations, add the following filter to the purple “Filter” section.
Months since registration = 0
If you want to run a report that includes multiple filters such as controlled patients, uncontrolled patients, missed visit patients, in a single report, you can use a grouping instead of a filter. Group by the appropriate property in the green “Summarize” section.
For example, to generate a report of controlled, uncontrolled, visit-but-no-bp, and missed-visit patients, add the following grouping:
Treatment Outcome In Last 3 Months
Sometimes, you want to run a report where results are grouped by facility, but you also want block and district information next to each facility. In order to do so, simply add block and district as additional groupings in the green “Summarize” section, and they will be shown in the results.
The results will have block and district information next to each facility.
The following table is a summary of each of the reporting tables, and what they contain. Please refer to the sections below for details of the columns in each of these tables.
Table Name | Description | Contents |
reporting_patient_blood_pressures | Summary of a patient’s latest blood pressure | One row per patient, per month, from the month of the patient's registration. |
reporting_patient_visits | Summary of a patient's latest visit where they had a BP/Sugar recording, an appointment, or a prescription drug refilled | One row per patient, per month, from the month of the patient's registration |
reporting_patient_states | Summary of a patient's health information and computed hypertension indicators. | One row per patient, per month, from the month of the patient's registration. |
reporting_facility_states | Summary of a facility’s registrations, treatment outcomes, and patients under care | One row per facility, per month, for all months since 2018 |
reporting_quarterly_facility_states | A facility’s quarterly treatment outcomes | One row per facility, per quarter, for all months since 2018 |
The reporting tables are building blocks that are built on top of raw data. The following diagram illustrates which raw tables they are based on, and how we build larger even building blocks from these tables.
Derivation of the reporting tables from raw data
- Raw data tables: Data as recorded in the app goes into the transactional, or raw tables as seen in the first section of the diagram. These rows have all the detailed information collected as and when the nurse syncs the data via the Simple app. These tables have billions of rows, and are not well suited for querying reports.
- Reporting tables: These are summarised tables created from the raw data tables for ease of creating reports. The summaries can be per-patient-per-month, or per-facility-per-month. Please refer to the above diagram for the description of each table.
- Dimension tables: We can use these tables along with other reporting tables to filter and disaggregate data across time and geographical regions.
reporting_patient_states
reporting_facility_states
|----------------------------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------|
| column_name | data_type | description |
|----------------------------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------|
| patient_id | uuid | ID of the patient |
| gender | character varying | Gender of the patient |
| current_age | double precision | "Patient's age as of today, based on 'age', 'age_updated_at' and 'date_of_birth'. This will have the same value for a patient across all rows." |
| month_string | text | "String that represents a month, in YYYY-MM format" |
| hypertension | text | "Has the patient been diagnosed with hypertension? Values can be yes, no, unknown, or null if the data is unavailable." |
| assigned_facility_id | uuid | ID of the patient's assigned facility |
| assigned_state_slug | character varying | Human readable ID of the patient's assigned facility's state |
| registration_facility_id | uuid | ID of the patient's registration facility |
| registration_state_slug | character varying | Human readable ID of the patient's registration facility's state |
| months_since_registration | double precision | "Number of months since registration. If a patient was registered on 31st Jan, it would be 1 month since registration on 1st Feb." |
| quarters_since_registration | double precision | "Number of quarters since registration. If a patient was registered on 31st Dec, it would be 1 quarter since registration on 1st Jan." |
| months_since_visit | double precision | "Number of months since the patient's last visit. If a patient visited on 31st Jan, it would be 1 month since the visit on 1st Feb." |
| quarters_since_visit | double precision | "Number of quarters since the patient's last visit. If a patient visited on 31st Jan, it would be 1 quarter since the visit on 1st Jan." |
| months_since_bp | double precision | "Number of months since the patient's last BP recording. If a patient had a BP reading on 31st Jan, it would be 1 month since BP on 1st Feb." |
| quarters_since_bp | double precision | "Number of quarters since the patient's last BP recording. If a patient had a BP reading on 31st Jan, it would be 1 quarter since BP on 1st Jan." |
| htn_care_state | text | "Is the patient under_care, lost_to_follow_up, or dead as of this month?" |
| htn_treatment_outcome_in_last_3_months | text | "For the visiting period of the last 3 months, is this patient's treatment outcome controlled, uncontrolled, missed_visit, or visited_no_bp?" |
| htn_treatment_outcome_in_last_2_months | text | "For the visiting period of the last 2 months, is this patient's treatment outcome controlled, uncontrolled, missed_visit, or visited_no_bp?" |
| htn_treatment_outcome_in_quarter | text | "For the visiting period of the current quarter, is this patient's treatment outcome controlled, uncontrolled, missed_visit, or visited_no_bp?" |
|----------------------------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------|
Column | Type | Collation | Nullable | Default
---------------------------------+-------------------+-----------+----------+---------
month_date | date | | |
month_string | text | | |
quarter_string | text | | |
facility_id | uuid | | |
facility_name | character varying | | |
facility_type | character varying | | |
facility_size | character varying | | |
state_slug | character varying | | |
cumulative_registrations | bigint | | |
monthly_registrations | bigint | | |
under_care | bigint | | |
lost_to_follow_up | bigint | | |
dead | bigint | | |
cumulative_assigned_patients | bigint | | |
controlled_under_care | bigint | | |
uncontrolled_under_care | bigint | | |
missed_visit_under_care | bigint | | |
visited_no_bp_under_care | bigint | | |
missed_visit_lost_to_follow_up | bigint | | |
visited_no_bp_lost_to_follow_up | bigint | | |
patients_under_care | bigint | | |
patients_lost_to_follow_up | bigint | | |
Last modified 9mo ago