Reporting with AI: Flattening Obs, Writing SQL Queries

grace · July 10, 2025, 7:39pm

Is anyone using AI to help create your OpenMRS Reports? Has anyone had any success getting AI to understand the OpenMRS data model?

PIH is wondering who they can possibly learn from.

Case Examples:

(Main Priority): Quickly Generate a Report Flat File based on a New Form: 15 fields on a form–> potentially up to 15 obs. Want it to generate it a flat file for each field in that form, and use SQL to do that.
(Later): Create Report Based on Plain English Query about key indicators, such as “How many patients had a high viral load within 3 months of HIV program enrollment?”

The team at @UWash’s @reagan @ibacher did use OpenELIS to set up something similar, and the idea is this same workflow could be used for OpenMRS too.

Others who I believe have been working on AI-generated/supported reports for OpenMRS: @PalladiumKenya @EMR4All @Intellisoft @Madiro

Keen to hear from anyone with thoughts/experience!

grace · July 10, 2025, 7:54pm

And, for those generating SQL with LLMs: How have you made sure the SQL is correct?

PIH is finding often that, in general, the SQL suggested by LLMs needs serious QA because it can be objectively wrong.

ruhanga · July 11, 2025, 6:36am

Thanks for sharing @grace. I completely agree with PIH’s observation. LLMs often require significant contextual guidance to generate accurate SQL queries. It’s not surprising though, like with any development process, getting things right takes iteration. While the queries LLMs produce based on the descriptions given it can sometimes be quite close, they can also occasionally miss the mark and diverge from the expected results in complex situations. Because of this, they aren’t solely reliable on their own and still need careful review and validation.

Given this, I think LLMs are quite good at generating good general(broad, undirected) summaries even without supervision. But again, this largely depends on the volume and quality of data the model is given to work with.

reagan · July 11, 2025, 10:25am

Hi @grace is the team using RAG technique. In our experience the higher the quality of the prompt (including the context of the database structure with relevant addition information) the higher the chances of more accurate responses

tendomart · July 11, 2025, 11:38am

Thanks for inquiring @grace for starting this. after successfully demonstrating the text-to-sql agent at @EMR4All we developed a few months ago, next in the pipeline is Report generation AI, These are the observations from our experience for implementing the text-to-sql agent

Good choice of LLM is paramount, we tried out qwen2.5 , deepseek-r1:1.5b. These performed better while doing inference for online hosted models via smollagents
They perform better while doing inference on models hosted by third parties like OpenAI, Hugging face and other vendors. We experimented with Hugging face smolagents
We have experimented with the Offline text-to-sql agent (i.e inference on ollama powered LLM’s Qwen, deepseek hosted on PC and Raspberry PI ) this is for those who care about data privacy and want to keep their transactions completely on-prem. Results where satisfactory, though greater improvements for accuracy and alot of refining is still a work in progress.

I strongly agree, we want to standardize QA practices for the various LLM’s , frameworks and agents we’re using tools like Deepeval or any other that is at our disposal

We’d be happy to share the work we have so far to OpenMRS, and collaborate with those specifically interested in working on AI generated reports. Next Steps setting up standards, tools and best practices for implementing AI with OpenMRS, documentation, security, human in loop, agents, tools and patterns. e.t.c

Since we’ve also been looking at other practical use cases, for example the ones you highlighted above, we’d be happy to collaborate with PIH /others and come up with those MVP’s to demonstrate

@grace i’d suggest that you organize a brainstorming meeting on how to move this forward. We could especially take advantage of the forth coming Hackathon Calling for mentors and peer reviewers to join the EMR4All Hackathon.

cc @bennyange

mksd · July 12, 2025, 10:55am

We haven’t started work on it but providing an AI prompt to consume Ozone Analytics flattened data leveraging Meditron-3 is on Ozone’s roadmap (as of May 2025):

@jesplana has been experimenting with various tools, including Meditron, on top of the OpenMRS data model and could report back.

grace · August 18, 2025, 5:34am

Thanks so much for this suggestion @tendomart and thank you @mksd for the update re. the Ozone roadmap! That’s very interesting re. Meditron. I’ve been hearing some preliminarily promising things about MedGemma (Google’s) as well.

It’s quite short notice but as promised I found a time that works for a few key stakeholders for our next discussion: this Monday! Details here: 🧠 AI and OpenMRS call this Monday: Share your work & Join our next-steps discussion!

thembo42 · September 4, 2025, 3:23pm

Hi,

Thank you all, these are very interesting developments.

LLMs seem at first glance to be a smart way to approach AI but we need to evaluate the trade offs there-in nad most importantly the usecases at hand.

Talking of text-to-sql/natutal language to sql , just to reference something that stood out to me from google research on the same;

TL;DR

So, I experimented with an MCP server for OpenMRS specifically looking at the OpenMRS data model with Claude (via openmrs-datamodel MCP server), and I encountered some of the problems above but with a significant improvement. You can check it out here ;

This aligns directly with what Google Cloud identified in their text-to-SQL challenges

Need business/domain-specific context.
Need intent disambiguation.
Need SQL-aware generation and validation…

I did too fine-tune an SLM on sample OpenMRS queries and schema and the results were promising.

IMO:

tl;dr Hybrid strategy: Use fine-tuned SLM for SQL reasoning + MCP for real schema context. And for general purpose usecases, we may need RAG as a retrieval layer with vector embeddings..

To ensure a low-risk entry point and one that aligns with the mission of low-resource settings, we need to be SLM-first or a hy-breed with MCP considerations too forward thinking and obviously with all experiments being done with LLMs no going. Something like the MCPs(OpenMRS, OCL/CIEL …) being the execution layer and the SLM(Domain centric) & FHIR-like standards the semantic layer/parser.

Forexample if there is an NL-to-SQL agent somewhat like what i talked about that is well grounded in the OpenMRS schema; simply put: having an SLM on-prem fine-tuned model trained with OpenMRS schema & and a family of tasks that we need to achieve then an LLM should be optional probably to come in for more complex natural language requests, but still wrapped with guardrails.

Raw LLMs don’t know OpenMRS even with a contextual prompt.

Why this is big: MCP lets us stop “stuffing context” into prompts and instead bind models to the OpenMRS ecosystem in a clean, composable way. If we get this right, we can move from toy demos like mine*:* to a production-ready AI layer, powered by community standards.