This guide explains how to create reports using configuration files rather than Java code, leveraging the OpenMRS Initializer module's reportdescriptors domain. This approach allows you to define reports using YAML configuration files and SQL queries, making reports easier to maintain and deploy without code compilation.

1 What is Configuration-Based Reporting?
2 When to Use Configuration-Based Reports
3 Prerequisites
4 YAML Report Descriptor Anatomy
5 Complete Example:
6 Parameters
- 6.1 Parameter Types
- 6.2 Using Parameters in SQL
- 6.3 Optional Parameters
7 Datasets
- 7.1 SQL-Based Datasets
- 7.2 Programmatic DataSetManagers
- 7.3 Multiple Datasets
8 Report Designs
- 8.1 CSV Design
- 8.2 Excel Design
9 Character Encoding and Special Characters
10 Configuration Options
- 10.1 Display Category
- 10.2 Display Order
- 10.3 Privilege-Based Access Control
- 10.4 Component-Based Filtering
- 10.5 Country/Site-Based Filtering
11 SQL Query Patterns
- 11.1 Basic Query Structure
- 11.2 Temporary Table Pattern
- 11.3 Handling NULL Values
- 11.4 Age Calculations
- 11.5 Pivot Data (Observations)
12 Step-by-Step Tutorial: Creating Your First Report
13 Advanced Patterns
- 13.1 Multi-Dataset Report Example
14 Comparison: Configuration-Based vs Programmatic
- 14.1 When to Use Each Approach
15 Best Practices
16 Troubleshooting
17 Example: Real-World Report from PIH
18 Additional Resources
19 Summary

What is Configuration-Based Reporting?

Configuration-based reporting uses YAML files to define report metadata and SQL files (or programmatic DataSetManagers) for data extraction. Reports are automatically loaded by the OpenMRS Initializer module at startup.

Key Benefits

✅ No Code Compilation: Define reports in configuration files, no Java coding required

✅ Version Control Friendly: Text-based YAML and SQL files work well with Git

✅ Easy Deployment: Deploy reports by copying configuration files

✅ Rapid Development: Modify SQL and reload without rebuilding module

✅ Configuration Management: Manage reports alongside other OpenMRS configuration

✅ Multi-Site Support: Use conditional loading based on components, countries, or sites

When to Use Configuration-Based Reports

Use configuration-based reports when:

Reports are primarily SQL-based data extraction
You want to manage reports as configuration (not code)
You need to deploy reports to multiple sites with variations
Your team prefers working with configuration files over Java
Reports are site-specific customizations

Use programmatic reports when:

Complex Java logic is required (beyond SQL)
You're building a reusable module with bundled reports
You need tight integration with module code
Reports require custom data definitions or converters

Prerequisites

Global Properties

You need set the global property reporting.loadReportsFromConfigurationAtStartup to true in your OpenMRS instance. This tells the Reporting module to load reports from the Initializer configuration.

Required Modules

Your OpenMRS instance must have these modules installed:

<!-- OpenMRS Reporting Module -->
<dependency>
    <groupId>org.openmrs.module</groupId>
    <artifactId>reporting</artifactId>
    <version>1.28.0</version>
</dependency>

<!-- OpenMRS Initializer Module -->
<dependency>
    <groupId>org.openmrs.module</groupId>
    <artifactId>initializer</artifactId>
    <version>2.9.0</version>
</dependency>

Configuration Directory Structure

OpenMRS Initializer expects reports in this directory structure:

configuration/
└── reports/
    └── reportdescriptors/
        └── dataexports/          # Or any category name
            ├── myreport.yml      # Report descriptor files
            ├── anotherreport.yml
            └── sql/
                ├── myreport.sql  # SQL query files
                └── anotherreport.sql

Standard Locations:

Server: /openmrs/data/configuration/reports/reportdescriptors/
Development: {appdata}/configuration/reports/reportdescriptors/

YAML Report Descriptor Anatomy

Basic Structure

Every report is defined in a YAML file with these components:

# Unique identifier for the report
key: "myreportkey"

# UUID for the report definition
uuid: "12345678-1234-1234-1234-123456789abc"

# Display name (can be message code for i18n)
name: "My Report Name"

# Description (can be message code for i18n)
description: "Detailed description of what this report does"

# Parameters users can provide when running report
parameters:
  - key: "startDate"
    type: "java.util.Date"
    label: "Start Date"
  - key: "endDate"
    type: "java.util.Date"
    label: "End Date"

# Datasets - where the data comes from
datasets:
  - key: "dataset1"
    type: "sql"                # SQL-based dataset
    config: "sql/myreport.sql" # Path to SQL file

# Output format configuration
designs:
  - type: "csv"
    properties:
      "characterEncoding": "UTF-8"
      "dateFormat": "yyyy-MM-dd HH:mm:ss"

# Display and access control configuration
config:
  category: "dataExport"
  order: 100
  privilege: "Get Patients"

Complete Example:

Patient Registration Report

File: configuration/reports/reportdescriptors/dataexports/patient_registrations.yml

key: "patient.registrations.dataexport"
uuid: "a1b2c3d4-e5f6-7890-1234-567890abcdef"
name: "Patient Registrations"
description: "Lists all patients registered within a date range with demographics"

parameters:
  - key: "startDate"
    type: "java.util.Date"
    label: "Start Date"
  - key: "endDate"
    type: "java.util.Date"
    label: "End Date"
  - key: "location"
    type: "org.openmrs.Location"
    label: "Registration Location"

datasets:
  - key: "registrations"
    type: "sql"
    config: "sql/patient_registrations.sql"

designs:
  - type: "csv"
    properties:
      "filenameBase": "patient_registrations.{{ formatDate request.reportDefinition.parameterMappings.startDate \"yyyyMMdd\" }}.{{ formatDate request.reportDefinition.parameterMappings.endDate \"yyyyMMdd\" }}"
      "characterEncoding": "UTF-8"
      "dateFormat": "yyyy-MM-dd HH:mm:ss"

config:
  category: "dataExport"
  order: 10
  privilege: "Get Patients"

SQL File: configuration/reports/reportdescriptors/dataexports/sql/patient_registrations.sql

-- Patient Registrations Report
-- Lists patients registered within a date range

SELECT
    p.patient_id AS 'Patient ID',
    pi.identifier AS 'Patient Identifier',
    pn.given_name AS 'First Name',
    pn.family_name AS 'Last Name',
    per.gender AS 'Gender',
    per.birthdate AS 'Date of Birth',
    TIMESTAMPDIFF(YEAR, per.birthdate, CURDATE()) AS 'Age',
    DATE(p.date_created) AS 'Registration Date',
    loc.name AS 'Registration Location',
    u.username AS 'Registered By'
FROM patient p
INNER JOIN person per ON p.patient_id = per.person_id
INNER JOIN patient_identifier pi ON p.patient_id = pi.patient_id AND pi.preferred = 1
INNER JOIN person_name pn ON p.patient_id = pn.person_id AND pn.preferred = 1
LEFT JOIN location loc ON p.location_id = loc.location_id
LEFT JOIN users u ON p.creator = u.user_id
WHERE DATE(p.date_created) BETWEEN @startDate AND @endDate
  AND (@location IS NULL OR p.location_id = @location)
  AND p.voided = 0
ORDER BY p.date_created DESC;

Parameters

Parameter Types

Common parameter types:

parameters:
  # Date parameter
  - key: "startDate"
    type: "java.util.Date"
    label: "Start Date"

  # Location parameter
  - key: "location"
    type: "org.openmrs.Location"
    label: "Facility"

  # String parameter
  - key: "searchTerm"
    type: "java.lang.String"
    label: "Search Term"

  # Integer parameter
  - key: "maxResults"
    type: "java.lang.Integer"
    label: "Maximum Results"

  # Concept parameter
  - key: "diagnosis"
    type: "org.openmrs.Concept"
    label: "Diagnosis"

Using Parameters in SQL

Parameters are referenced in SQL using @parameterName syntax:

WHERE DATE(e.encounter_datetime) BETWEEN @startDate AND @endDate
  AND e.location_id = @location
  AND o.concept_id = @diagnosis

Optional Parameters

Handle optional parameters with NULL checks:

WHERE DATE(e.encounter_datetime) BETWEEN @startDate AND @endDate
  AND (@location IS NULL OR e.location_id = @location)
  AND (@diagnosis IS NULL OR o.concept_id = @diagnosis)

Datasets

SQL-Based Datasets

Most common dataset type - executes SQL query against database:

datasets:
  - key: "mydata"
    type: "sql"
    config: "sql/myquery.sql"

The config path is relative to the reportdescriptors directory.

Programmatic DataSetManagers

Reference Java-based dataset managers:

datasets:
  - key: "complexdata"
    type: "registrationDataSetManager"  # Spring bean name

This requires a Java class implementing DataSetManager, registered as a Spring bean.

Multiple Datasets

A single report can have multiple datasets:

datasets:
  - {key: "demographics", type: "sql", config: "sql/demographics.sql"}
  - {key: "vitals", type: "sql", config: "sql/vitals.sql"}
  - {key: "diagnoses", type: "sql", config: "sql/diagnoses.sql"}
  - {key: "medications", type: "sql", config: "sql/medications.sql"}

Each dataset becomes a separate sheet/tab in Excel output or separate section in CSV.

Example:

openmrs-config-pihemr/configuration/reports/reportdescriptors/dataexports /visitNoteData.yml

Report Designs

CSV Design

Basic CSV configuration:

designs:
  - type: "csv"
    properties:
      "characterEncoding": "UTF-8"
      "dateFormat": "yyyy-MM-dd HH:mm:ss"

CSV with Custom Filename

Use template expressions for dynamic filenames:

designs:
  - type: "csv"
    properties:
      "filenameBase": "myreport.{{ formatDate request.reportDefinition.parameterMappings.startDate \"yyyyMMdd\" }}.{{ formatDate request.reportDefinition.parameterMappings.endDate \"yyyyMMdd\" }}"
      "characterEncoding": "UTF-8"

This generates filenames like: myreport.20240101.20240131.csv

Excel Design

designs:
  - type: "excel"
    properties:
      "filenameBase": "myreport"

Character Encoding and Special Characters

Handle special characters with blacklist regex:

designs:
  - type: "csv"
    properties:
      "characterEncoding": "ISO-8859-1"
      "blacklistRegex": "[^\\p{InBasicLatin}\\p{L}]"  # Remove non-Latin characters
      "dateFormat": "dd-MMM-yyyy HH:mm:ss"

Configuration Options

Display Category

Group reports by category:

config:
  category: "dataExport"   # Common categories: dataExport, indicator, monitoring

Display Order

Control sort order in UI (lower numbers appear first):

config:
  order: 10  # Lower numbers appear first

Privilege-Based Access Control

Require specific privileges to run report:

config:
  privilege: "Get Patients"  # OpenMRS privilege required

Or use custom privileges:

config:
  privilege: "Task: clinical.reports"

Component-Based Filtering

Show report only when specific components are enabled:

config:
  components:
    - "registration"       # Your custom component names
    - "clinicalEncounters"
    - "allDataExports"     # Show for all data exports

Country/Site-Based Filtering

Show report only in specific countries or sites:

config:
  countries:
    - "HAITI"
    - "LIBERIA"
    - "OTHER"

Or use custom site identifiers based on your implementation.

SQL Query Patterns

Basic Query Structure

-- Report Name and Description
-- Purpose: What this report does

SELECT
    p.patient_id AS 'Column 1',
    pi.identifier AS 'Column 2',
    pn.given_name AS 'Column 3'
FROM patient p
INNER JOIN patient_identifier pi ON p.patient_id = pi.patient_id
WHERE DATE(p.date_created) BETWEEN @startDate AND @endDate
  AND p.voided = 0
ORDER BY p.date_created DESC;

Temporary Table Pattern

For complex reports, use temporary tables:

-- Create temporary table
DROP TEMPORARY TABLE IF EXISTS temp_report_data;
CREATE TEMPORARY TABLE temp_report_data (
    patient_id INT,
    identifier VARCHAR(50),
    name VARCHAR(100),
    gender CHAR(1),
    birthdate DATE
);

-- Insert base data
INSERT INTO temp_report_data
SELECT
    p.patient_id,
    pi.identifier,
    CONCAT(pn.given_name, ' ', pn.family_name),
    per.gender,
    per.birthdate
FROM patient p
INNER JOIN person per ON p.patient_id = per.person_id
INNER JOIN patient_identifier pi ON p.patient_id = pi.patient_id AND pi.preferred = 1
INNER JOIN person_name pn ON p.patient_id = pn.person_id AND pn.preferred = 1
WHERE DATE(p.date_created) BETWEEN @startDate AND @endDate
  AND p.voided = 0;

-- Enrich with additional data
UPDATE temp_report_data t
SET name = UPPER(name)
WHERE name IS NOT NULL;

-- Return results
SELECT * FROM temp_report_data
ORDER BY patient_id;

Handling NULL Values

SELECT
    p.patient_id,
    COALESCE(pn.given_name, 'Unknown') AS first_name,
    IFNULL(per.birthdate, '1900-01-01') AS birthdate,
    COALESCE(pa.address1, 'Not Provided') AS address
FROM patient p
LEFT JOIN person_name pn ON p.patient_id = pn.person_id
LEFT JOIN person per ON p.patient_id = per.person_id
LEFT JOIN person_address pa ON p.patient_id = pa.person_id

Age Calculations

SELECT
    p.patient_id,
    per.birthdate,
    TIMESTAMPDIFF(YEAR, per.birthdate, CURDATE()) AS age_years,
    TIMESTAMPDIFF(MONTH, per.birthdate, CURDATE()) AS age_months,
    CASE
        WHEN TIMESTAMPDIFF(YEAR, per.birthdate, CURDATE()) < 5 THEN '<5'
        WHEN TIMESTAMPDIFF(YEAR, per.birthdate, CURDATE()) BETWEEN 5 AND 14 THEN '5-14'
        WHEN TIMESTAMPDIFF(YEAR, per.birthdate, CURDATE()) BETWEEN 15 AND 49 THEN '15-49'
        ELSE '50+'
    END AS age_group
FROM patient p
INNER JOIN person per ON p.patient_id = per.person_id

Pivot Data (Observations)

SELECT
    p.patient_id,
    MAX(CASE WHEN cn.name = 'WEIGHT (KG)' THEN o.value_numeric END) AS weight,
    MAX(CASE WHEN cn.name = 'HEIGHT (CM)' THEN o.value_numeric END) AS height,
    MAX(CASE WHEN cn.name = 'TEMPERATURE (C)' THEN o.value_numeric END) AS temperature
FROM patient p
INNER JOIN encounter e ON p.patient_id = e.patient_id
LEFT JOIN obs o ON e.encounter_id = o.encounter_id
LEFT JOIN concept_name cn ON o.concept_id = cn.concept_id
WHERE e.encounter_datetime BETWEEN @startDate AND @endDate
  AND e.voided = 0
  AND o.voided = 0
GROUP BY p.patient_id;

Step-by-Step Tutorial: Creating Your First Report

Step 1: Create Directory Structure

mkdir -p configuration/reports/reportdescriptors/dataexports/sql

Step 2: Create YAML Descriptor

Create configuration/reports/reportdescriptors/dataexports/my_first_report.yml:

key: "my.first.report"
uuid: "11111111-2222-3333-4444-555555555555"  # Generate unique UUID
name: "My First Report"
description: "A simple patient list report"

parameters:
  - key: "startDate"
    type: "java.util.Date"
    label: "Start Date"
  - key: "endDate"
    type: "java.util.Date"
    label: "End Date"

datasets:
  - key: "patients"
    type: "sql"
    config: "sql/my_first_report.sql"

designs:
  - type: "csv"
    properties:
      "characterEncoding": "UTF-8"
      "dateFormat": "yyyy-MM-dd"

config:
  category: "dataExport"
  order: 100
  privilege: "Get Patients"

Step 3: Create SQL Query

Create configuration/reports/reportdescriptors/dataexports/sql/my_first_report.sql:

-- My First Report
-- Lists patients registered in date range

SELECT
    p.patient_id AS 'Patient ID',
    pi.identifier AS 'Identifier',
    pn.given_name AS 'First Name',
    pn.family_name AS 'Last Name',
    per.gender AS 'Gender',
    per.birthdate AS 'Birth Date'
FROM patient p
INNER JOIN person per ON p.patient_id = per.person_id
INNER JOIN patient_identifier pi ON p.patient_id = pi.patient_id AND pi.preferred = 1
INNER JOIN person_name pn ON p.patient_id = pn.person_id AND pn.preferred = 1
WHERE DATE(p.date_created) BETWEEN @startDate AND @endDate
  AND p.voided = 0
ORDER BY p.date_created DESC;

Step 4: Deploy and Test

Copy files to OpenMRS configuration directory
Restart OpenMRS (or reload Initializer configuration if supported)
Navigate to Reports → Manage Reports
Find your report in the list
Run report with date range parameters
Download CSV output

Step 5: Verify and Iterate

Check CSV output for correctness
Modify SQL as needed
Reload configuration (restart or reload)
Test again

Advanced Patterns

Multi-Dataset Report Example

YAML:

key: "comprehensive.patient.data"
uuid: "aaaabbbb-cccc-dddd-eeee-ffffffffffff"
name: "Comprehensive Patient Data"
description: "Patient demographics, encounters, diagnoses, and medications"

parameters:
  - {key: "startDate", type: "java.util.Date", label: "Start Date"}
  - {key: "endDate", type: "java.util.Date", label: "End Date"}

datasets:
  - {key: "demographics", type: "sql", config: "sql/patient_demographics.sql"}
  - {key: "encounters", type: "sql", config: "sql/patient_encounters.sql"}
  - {key: "diagnoses", type: "sql", config: "sql/patient_diagnoses.sql"}
  - {key: "medications", type: "sql", config: "sql/patient_medications.sql"}

designs:
  - type: "csv"
    properties:
      "filenameBase": "comprehensive_data"
      "characterEncoding": "UTF-8"

config:
  category: "dataExport"
  order: 50
  privilege: "Get Patients"

Each dataset query should use the same parameters and preferably return a common key (like patient_id) for joining in analysis tools.

Conditional Display Example

config:
  category: "dataExport"
  order: 20
  components:
    - "hiv"              # Only show if HIV component enabled
    - "allDataExports"
  countries:
    - "KENYA"            # Only show in Kenya sites
    - "UGANDA"
  privilege: "Task: hiv.reports"  # Require custom privilege

Comparison: Configuration-Based vs Programmatic

Aspect	Configuration-Based	Programmatic

Aspect	Configuration-Based	Programmatic
Files	YAML + SQL	Java classes
Deployment	Copy config files	Build and deploy module
Modification	Edit files, reload	Code, compile, redeploy
Version Control	Text files in Git	Java code in Git
Complexity	Simple to moderate	Simple to very complex
SQL Support	Excellent	Excellent
Custom Logic	Limited (SQL only)	Unlimited (Java)
Learning Curve	Lower (YAML, SQL)	Higher (Java, APIs)
Reusability	Config per installation	Module distributed to many sites
Best For	Site-specific reports	Module-bundled reports

When to Use Each Approach

Use Configuration-Based for:

Data extraction reports
Site-specific customizations
Rapid development and deployment
SQL-based reporting
Multi-site deployments with variations

Use Programmatic for:

Complex data transformations
Reusable module components
Custom cohort definitions
Integration with module logic
Shared across many implementations

Best Practices

Organization

Use clear naming: patient_registrations.yml, not report1.yml
Group by category: Use subdirectories for different report types
Match SQL filenames: myreport.yml → sql/myreport.sql
Comment SQL queries: Explain purpose and logic

SQL Optimization

Filter early: Use WHERE clause to limit data
Index usage: Filter on indexed columns (patient_id, encounter_id, etc.)
**Avoid SELECT ***: Specify only needed columns
Test with production data: Verify performance with realistic data volumes
Use EXPLAIN: Check query execution plan for optimization opportunities

Parameter Handling

Always validate: Handle NULL parameters in SQL
Provide labels: Use clear, user-friendly labels
Use standard names: startDate, endDate for consistency
Document ranges: Explain what date range means (registration? encounter?)

Security

Check privileges: Always set appropriate privilege requirements
Filter voided: Always include WHERE x.voided = 0
Protect PHI: Be mindful of sensitive data in reports
Use parameters: Never build SQL with string concatenation

Testing

Test edge cases: Empty date ranges, NULL parameters
Verify counts: Check totals against known data
Check encoding: Test special characters in names/addresses
Performance test: Run with production-size datasets
Multi-site test: If using components/countries, test filtering

Troubleshooting

Report Doesn't Appear

Check:

Files in correct directory: configuration/reports/reportdescriptors/
YAML syntax valid (use online YAML validator)
OpenMRS restarted after adding files
Initializer module loaded successfully (check logs)
Privileges - user has required privilege

SQL Errors

Debug:

Test SQL in MySQL directly
Set parameters: SET @startDate = '2024-01-01';
Check parameter names match (@startDate in SQL, startDate in YAML)
Review OpenMRS logs for SQL error details

Empty Results

Verify:

Date range contains data
Parameters correctly passed
WHERE clauses not too restrictive
Voided records filtered appropriately
JOINs not excluding all records

Encoding Issues

Fix:

designs:
  - type: "csv"
    properties:
      "characterEncoding": "UTF-8"  # Try different encodings
      "blacklistRegex": "[^\\p{InBasicLatin}\\p{L}]"  # Remove problematic characters

Example: Real-World Report from PIH

Here's a simplified example based on PIH's implementation:

File: appointments.yml

key: "appointments.dataexport"
uuid: "7fd11020-e5d1-11e3-ac10-0800200c9a66"
name: "Appointments Data Export"
description: "Lists all appointments with patient demographics and appointment details"

parameters:
  - key: "startDate"
    type: "java.util.Date"
    label: "Start Date"
  - key: "endDate"
    type: "java.util.Date"
    label: "End Date"

datasets:
  - key: "appointments"
    type: "sql"
    config: "sql/appointments.sql"

designs:
  - type: "csv"
    properties:
      "characterEncoding": "ISO-8859-1"
      "dateFormat": "dd-MMM-yyyy HH:mm:ss"

config:
  category: "dataExport"
  order: 150
  components:
    - "appointmentScheduling"
  privilege: "Task: archive.reports"

File: sql/appointments.sql (simplified)

-- Appointments Data Export

SELECT
    p.patient_id AS 'Patient ID',
    pi.identifier AS 'Identifier',
    CONCAT(pn.given_name, ' ', pn.family_name) AS 'Patient Name',
    per.gender AS 'Gender',
    per.birthdate AS 'Birth Date',
    app.start_date_time AS 'Appointment Date',
    app.status AS 'Status',
    app_type.name AS 'Appointment Type',
    loc.name AS 'Location'
FROM patient_appointment app
INNER JOIN patient p ON app.patient_id = p.patient_id
INNER JOIN person per ON p.patient_id = per.person_id
INNER JOIN patient_identifier pi ON p.patient_id = pi.patient_id AND pi.preferred = 1
INNER JOIN person_name pn ON p.patient_id = pn.person_id AND pn.preferred = 1
LEFT JOIN appointment_type app_type ON app.appointment_type_id = app_type.appointment_type_id
LEFT JOIN location loc ON app.location_id = loc.location_id
WHERE DATE(app.start_date_time) BETWEEN @startDate AND @endDate
  AND app.voided = 0
ORDER BY app.start_date_time DESC;

Additional Resources

OpenMRS Initializer Documentation
OpenMRS Reporting Module
OpenMRS Data Model
Programmatic Reporting Guide - For comparison

Summary

Configuration-based reporting with OpenMRS Initializer provides a powerful, flexible way to create reports without Java coding. By using YAML for metadata and SQL for data extraction, you can rapidly develop, deploy, and maintain reports across multiple OpenMRS installations.

Key Takeaways:

YAML files define report metadata, parameters, and configuration
SQL files contain data extraction logic
Reports automatically load via OpenMRS Initializer
Suitable for SQL-based data extraction reports
Complements programmatic reporting for complex scenarios
Ideal for site-specific customizations and multi-site deployments

Configuration-Based Reporting with OpenMRS Initializer