Reporting: Any SQL cohort returns the result as '1 Patients'

I’m working with SQL Cohort Queries in the Reporting Module. I notice that for any SQL in the SQL Cohort Query, the result is 1 Patients (for preview as well as run).

There isn’t any stacktrace being generated for preview or run.

I’m running…

  • OpenMRS 2.2, but noticed the problem with too.
  • Reporting 0.9.7
  • Serialization Xstream 0.2.9
  • Jetty/6.1.10 (intellij)

Am I using it wrong or is it an error? I dont see any issue similar to this in JIRA. Any comments @mseaton or @mogoodrich ?

Generally SQL queries in the Reporting module work.

Can you show the example you’re trying? (You need to have a patient_id column come back from the query, if I remember right.)

I am trying to retrieve the count of patients. These screenshots are from

When I do a all patients cohort the result is 30 patients.

This is the SQL cohort query that I am saving and running

This is the result for that SQL cohort query and this seems to be the only result for any query.

@maurya - the issue is that you are not querying for a cohort, you are querying for a number. If you wish to use Cohort Definitions to return a Cohort of patients (from which you can get the members themselves, or the count of members), then you need your query to return the patient_ids that match your needs.

So, the equivalent of the “AllPatientsQuery” as a SQL Cohort Definition would be configured as:

select patient_id from patient where voided = 0;

As @darius said in his earlier response, you need to be returning patient_id from your query in order to use SqlCohortDefinitions correctly.

If all you want is an indicator value based on Sql, you can use a SqlIndicator for this and bypass Cohorts entirely. This is where configuring the SQL as “select count(*) from patient where voided = 0” will work as you intend.

The difference being, that in the Cohort Definition context, doing “select count(*) from patient” will return the number 30, and this will be interpreted as a Cohort of size 1 containing only the patient whose patient_id = 30.

Hope this clarifies.


1 Like

Thank you for the clear explanation @mseaton, it was really helpful.