Building custom reports
Details on how to creating your own reports using Metabase, and the reporting tables.
Last updated
Details on how to creating your own reports using Metabase, and the reporting tables.
Last updated
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.
Bangladesh: https://metabase.bd.simple.org/question/46
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 slug
Assigned state slug
Assigned district slug
Assigned block slug
Assigned facility slug
The organization, state, district, block, or facility that the patients are assigned to.
Registration organization slug
Registration state slug
Registration district slug
Registration block slug
Registration 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:
controlled
uncontrolled
visit but no BP
missed visit
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.
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.
You can browse through the detailed schema on github, or on metabase.
