Building custom reports

Metabase
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
​https://metabase.simple.org​
India’s IHCI program
Production
Bangladesh
​https://metabase.bd.simple.org​
Bangladesh’s NHF hypertension control program
Sandbox
​https://metabase-sandbox.simple.org 
A stable playground for the Simple team to work in
Quickstart
If you're already familiar with Simple's data model and Metabase, you can get started with this quick demo.
Getting Started
To get started, open up the custom report template. Template links are provided below.
India: https://metabase.simple.org/question/343​
Bangladesh: https://metabase.bd.simple.org/question/46 
Sandbox: https://metabase-sandbox.simple.org/question/86  
1. Open the template
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.
2. Filter the results
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.
3. Group the results
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.
Key Reference
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
Tips & Tricks
Registrations
To run a report on registrations, add the following filter to the purple “Filter” section.
Months since registration = 0
Multiple filters
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
Enriching results
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.
Detailed Reference
Overview of the reporting tables
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
How the reporting tables are created
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.
Description of the data
reporting_patient_states
reporting_facility_states
1
|----------------------------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------|
2
| column_name                            | data_type         | description                                                                                                                                       |
3
|----------------------------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------|
4
| patient_id                             | uuid              | ID of the patient                                                                                                                                 |
5
| gender                                 | character varying | Gender of the patient                                                                                                                             |
6
| 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."   |
7
| month_string                           | text              | "String that represents a month, in YYYY-MM format"                                                                                               |
8
| hypertension                           | text              | "Has the patient been diagnosed with hypertension? Values can be yes, no, unknown, or null if the data is unavailable."                           |
9
| assigned_facility_id                   | uuid              | ID of the patient's assigned facility                                                                                                             |
10
| assigned_state_slug                    | character varying | Human readable ID of the patient's assigned facility's state                                                                                      |
11
| registration_facility_id               | uuid              | ID of the patient's registration facility                                                                                                         |
12
| registration_state_slug                | character varying | Human readable ID of the patient's registration facility's state                                                                                  |
13
| 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."                |
14
| 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."            |
15
| 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."              |
16
| 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."          |
17
| 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."     |
18
| 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." |
19
| htn_care_state                         | text              | "Is the patient under_care, lost_to_follow_up, or dead as of this month?"                                                                         |
20
| 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?"     |
21
| 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?"     |
22
| 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?"   |
23
|----------------------------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------|
Copied!
1
             Column              |       Type        | Collation | Nullable | Default
2
---------------------------------+-------------------+-----------+----------+---------
3
 month_date                      | date              |           |          |
4
 month_string                    | text              |           |          |
5
 quarter_string                  | text              |           |          |
6
 facility_id                     | uuid              |           |          |
7
 facility_name                   | character varying |           |          |
8
 facility_type                   | character varying |           |          |
9
 facility_size                   | character varying |           |          |
10
 state_slug                      | character varying |           |          |
11
 cumulative_registrations        | bigint            |           |          |
12
 monthly_registrations           | bigint            |           |          |
13
 under_care                      | bigint            |           |          |
14
 lost_to_follow_up               | bigint            |           |          |
15
 dead                            | bigint            |           |          |
16
 cumulative_assigned_patients    | bigint            |           |          |
17
 controlled_under_care           | bigint            |           |          |
18
 uncontrolled_under_care         | bigint            |           |          |
19
 missed_visit_under_care         | bigint            |           |          |
20
 visited_no_bp_under_care        | bigint            |           |          |
21
 missed_visit_lost_to_follow_up  | bigint            |           |          |
22
 visited_no_bp_lost_to_follow_up | bigint            |           |          |
23
 patients_under_care             | bigint            |           |          |
24
 patients_lost_to_follow_up      | bigint            |           |          |
Copied!
Detailed schema of the reporting tables
You can browse through the detailed schema on github, or on metabase.