Advanced Reporting Capabilities for Complex Requirements

This guide covers advanced reporting capabilities for complex requirements that go beyond basic report building.

When standard report components don't meet your specific needs, the OpenMRS Reporting Module offers powerful advanced features including SQL data sets for ultimate query flexibility, multi-data set reports that combine different types of information, and sophisticated location filtering strategies for geographic reporting. These features require deeper technical knowledge but enable you to create highly customized reports that address complex organizational requirements and data analysis needs.

Table of content

1 Using SQL Data Sets
2 Working with Multiple Data Sets
3 Location and Geographic Filtering

Using SQL Data Sets

When the built-in data set types don't meet your needs, SQL data sets provide ultimate flexibility.

Creating SQL Data Sets

Navigate: Administration → Data Set Definitions
Add: "SQL Data Set"
Configure:
- Name: Descriptive name
- SQL Query: Your SELECT statement
- Parameters: Define any parameters used

SQL Best Practices

Parameter Usage:

-- Use :parameterName for parameters
SELECT p.patient_id, p.given_name, p.family_name
FROM patient p
WHERE p.date_created >= :startDate
AND p.date_created <= :endDate
AND p.location_id = :location

Performance Tips:

Always use appropriate indexes
Limit result sets when possible
Use EXPLAIN to check query performance
Consider date range limitations

Common Patterns:

-- Date range with time considerations
WHERE obs_datetime >= :startDate 
AND obs_datetime < DATE_ADD(:endDate, INTERVAL 1 DAY)

-- Avoiding voided records
AND obs.voided = 0
AND encounter.voided = 0
AND patient.voided = 0

-- Location filtering
AND encounter.location_id = :location

Working with Multiple Data Sets

Combining Different Data Set Types

Example Report Structure:

HIV Program Report
├── Summary Statistics (Period Indicator Data Set)
│   ├── Total Active Patients
│   ├── New Enrollments
│   └── Retention Rate
└── Patient Details (Simple Patient Data Set)
    ├── Patient Demographics
    ├── Enrollment Dates
    └── Current Status

Data Set Keys and Organization

Key Naming Conventions:

summary - For indicator summaries
details - For patient-level data
by_location - For location breakdowns
trends - For time-series data

Parameter Coordination:

Use same parameter names across data sets
Map parameters consistently
Document parameter relationships

Location and Geographic Filtering

Location Hierarchy

Understanding OpenMRS Locations:

Country → Region → District → Facility → Ward
Reports can filter at any level
Child locations inherit parent filters

Location Parameter Options:

Single Location: Specific facility only
Location List: Multiple specific locations
Location Hierarchy: All children of selected location

Implementation Strategies

Facility-Level Reports:

Use single location parameter
Filter all cohorts by location
Consider encounter location vs patient location

Regional Reports:

Use location hierarchy
May need custom SQL for complex geographic queries
Consider performance implications

Multi-Site Comparisons:

Use location dimensions
Create separate indicators for each location
Use location list parameters