Custom Reports

(The internal H2 database and Custom Report feature have been removed from Synthea for performance reasons. This page is left for historical reference only)

Synthea allows for the creation of custom reports each time a population is generated, using the CustomSqlReport class. This class uses the internal H2 data store to run arbitrary SQL queries against. These queries are defined in a configurable SQL file, and the results are exported in CSV format to ./output/reports/. Currently only SELECT statements are allowed in the SQL.

The Synthea DataStore uses an H2 database under the hood, so any queries must be valid H2-style syntax. See H2 Database Functions for more information on what functions are available.

Configuration Settings

• generate.database_type : REQUIRED: Set to in-memory or file to enable the database against which the queries will be run
• exporter.custom_report : Set to true to enable the custom reports. Defaults to false.
• exporter.custom_report_queries_file : Set this to the location of an SQL file (relative to src/main/resources) containing queries to run at each population generation. Defaults to custom_queries.sql (aka src/main/resources/custom_queries.sql)

Available Database Tables

(see also src/main/java/org/mitre/synthea/datastore/DataStore.java for more information on how these tables are populated)

PERSON

CREATE TABLE IF NOT EXISTS PERSON 
 (id varchar, name varchar, date_of_birth bigint, date_of_death bigint, 
 race varchar, gender varchar, socioeconomic_status varchar)

ATTRIBUTE

CREATE TABLE IF NOT EXISTS ATTRIBUTE 
 (person_id varchar, name varchar, value varchar)

PROVIDER

CREATE TABLE IF NOT EXISTS PROVIDER (id varchar, name varchar)

PROVIDER_ATTRIBUTE

CREATE TABLE IF NOT EXISTS PROVIDER_ATTRIBUTE 
 (provider_id varchar, name varchar, value varchar)

ENCOUNTER

CREATE TABLE IF NOT EXISTS ENCOUNTER 
 (id varchar, person_id varchar, provider_id varchar, name varchar, type varchar, 
 start bigint, stop bigint, code varchar, display varchar, system varchar)

CONDITION

CREATE TABLE IF NOT EXISTS CONDITION 
 (person_id varchar, name varchar, type varchar, start bigint, stop bigint, 
 code varchar, display varchar, system varchar)

MEDICATION

CREATE TABLE IF NOT EXISTS MEDICATION 
 (id varchar, person_id varchar, provider_id varchar, name varchar, type varchar, 
 start bigint, stop bigint, code varchar, display varchar, system varchar)

PROCEDURE

CREATE TABLE IF NOT EXISTS PROCEDURE 
 (person_id varchar, encounter_id varchar, name varchar, type varchar, 
 start bigint, stop bigint, code varchar, display varchar, system varchar)

REPORT

CREATE TABLE IF NOT EXISTS REPORT 
 (id varchar, person_id varchar, encounter_id varchar, name varchar, type varchar, 
 start bigint, code varchar, display varchar, system varchar)

OBSERVATION

CREATE TABLE IF NOT EXISTS OBSERVATION 
 (person_id varchar, encounter_id varchar, report_id varchar, name varchar, 
 type varchar, start bigint, value varchar, unit varchar, 
 code varchar, display varchar, system varchar)

IMMUNIZATION

CREATE TABLE IF NOT EXISTS IMMUNIZATION 
 (person_id varchar, encounter_id varchar, name varchar, type varchar, 
 start bigint, code varchar, display varchar, system varchar)

CAREPLAN

CREATE TABLE IF NOT EXISTS CAREPLAN 
 (id varchar, person_id varchar, provider_id varchar, name varchar, type varchar, 
 start bigint, stop bigint, code varchar, display varchar, system varchar)

IMAGING_STUDY

CREATE TABLE IF NOT EXISTS IMAGING_STUDY 
 (id varchar, uid varchar, person_id varchar, encounter_id varchar, start bigint, 
 modality_code varchar, modality_display varchar, modality_system varchar, 
 bodysite_code varchar, bodysite_display varchar, bodysite_system varchar, 
 sop_class varchar)

CLAIM

CREATE TABLE IF NOT EXISTS CLAIM 
 (id varchar, person_id varchar, encounter_id varchar, medication_id varchar, 
 time bigint, cost decimal)

COVERAGE

CREATE TABLE IF NOT EXISTS COVERAGE (person_id varchar, year int, category varchar)

QUALITY_OF_LIFE

CREATE TABLE IF NOT EXISTS QUALITY_OF_LIFE 
 (person_id varchar, year int, qol double, qaly double, daly double)

UTILIZATION

CREATE TABLE IF NOT EXISTS UTILIZATION 
 (provider_id varchar, year int, encounters int, procedures int, 
 labs int, prescriptions int)

UTILIZATION_DETAIL

CREATE TABLE IF NOT EXISTS UTILIZATION_DETAIL 
 (provider_id varchar, year int, category varchar, value int)

Sample Queries

(Note: these queries have been formatted for readability. Currently the report generator requires that all queries be contained on a single line.)

Sample Query 1: select everything from the "person" table.

select * from person;

Sample Query 2: select the number of living people.

select count(*) from person where person.DATE_OF_DEATH is null;

Sample Query 3: select the people that have an active diagnosis of diabetes, along with the age of diagnosis

SELECT p.name, p.gender, 
  DATEADD('MILLISECOND', p.DATE_OF_BIRTH, DATE '1970-01-01') DOB, 
  DATEADD('MILLISECOND', c.start , DATE '1970-01-01') onset_date, 
  DATEDIFF('YEAR', 
      DATEADD('MILLISECOND', p.DATE_OF_BIRTH, DATE '1970-01-01'), 
      DATEADD('MILLISECOND', c.start, DATE '1970-01-01')) age_at_diagnosis
FROM PERSON p, CONDITION c 
WHERE p.ID = c.PERSON_ID 
AND c.code = '44054006';