Reporting module not working on openmrs 2.0.6

Development
1

Hi, After upgrade openmrs from 1.9.2 to 2.0.6, Reporting module not working. I ran raw query on myql client its working but not working as data set.

Here is the error: https://pastebin.com/VpPuwVwT

Guys if you help me the emergency basis , it will be really helpful. Our live reporting system not working at this moment.

2

Hi, can you please tell us the reporting module version you are using.

It looks like something with an SQL query is wrong org.openmrs.module.reporting.evaluation.EvaluationException: Failed to evaluate Invalid query specified. There are two columns named 'PATIENT_ID

Are you creating a specific query in the UI that you can share with us? Or what would we need to do to replicate this error? Just install the reporting module?

1 Like
3

This query is working when I run this query with mysqlworkbench. Also this query working working on openmrs1.9.2 But not working on openmrs 2.0.6

Reporting module version : 1.15.0

4

what query? please post it as well

5

My all reporting query.

One of them is:

SELECT * FROM(SELECT ID.IDENTIFIER AS PATIENT_ID, PT.PATIENT_ID AS PID, PR.GENDER, YEAR(CURRENT_DATE()) - YEAR(PR.BIRTHDATE) AS AGE, UPPER(CONCAT(NM.GIVEN_NAME, ’ ', NM.FAMILY_NAME)) AS PATIENT_NAME, ET.NAME AS ENCOUNTER_NAME, EN.ENCOUNTER_DATETIME, EN.DATE_CREATED, LO.NAME AS “Screened_Location”, US.USERNAME AS “Screener_ID”

FROM openmrs.patient AS PT INNER JOIN openmrs.person AS PR ON PR.PERSON_ID = PT.PATIENT_ID INNER JOIN openmrs.person_name AS NM ON NM.PERSON_ID = PT.PATIENT_ID INNER JOIN openmrs.patient_identifier AS ID ON ID.PATIENT_ID = PT.PATIENT_ID AND ID.IDENTIFIER_TYPE = 1 INNER JOIN openmrs.encounter AS EN ON EN.PATIENT_ID = PT.PATIENT_ID INNER JOIN openmrs.location AS LO ON LO.LOCATION_ID = EN.LOCATION_ID INNER JOIN openmrs.encounter_type AS ET ON ET.ENCOUNTER_TYPE_ID = EN.ENCOUNTER_TYPE INNER JOIN openmrs.users AS US ON US.USER_ID = EN.CREATOR WHERE PT.voided=0 AND ET.NAME=‘Screening’ AND ID.IDENTIFIER=‘203170100820-6’ AND EN.voided=0) AS Screening

RIGHT JOIN

(SELECT ID.IDENTIFIER AS PATIENT_ID, PT.PATIENT_ID AS PID, PR.GENDER, YEAR(CURRENT_DATE()) - YEAR(PR.BIRTHDATE) AS AGE, UPPER(CONCAT(NM.GIVEN_NAME, ’ ‘, NM.FAMILY_NAME)) AS PATIENT_NAME, ET.NAME AS ENCOUNTER_NAME, DATE_FORMAT(EN.ENCOUNTER_DATETIME,’%d%-%m%-%Y’) AS “CXR_Test_Result_Date”, EN.DATE_CREATED, LO.NAME AS “CXR_Location”, US.USERNAME AS “Lab_Technician”,(SELECT VALUE_TEXT FROM obs OB INNER JOIN openmrs.concept_name AS CN ON CN.CONCEPT_ID = OB.CONCEPT_ID AND CN.LOCALE_PREFERRED = 1 AND CN.voided=0 WHERE CN.NAME = 'Remarks’AND OB.PERSON_ID = PT.PATIENT_ID AND OB.ENCOUNTER_ID = EN.ENCOUNTER_ID AND OB.voided=0) AS Remarks, (SELECT NAME FROM concept_name WHERE concept_id=(SELECT VALUE_CODED FROM obs OB INNER JOIN openmrs.concept_name AS CN ON CN.CONCEPT_ID = OB.CONCEPT_ID AND CN.LOCALE_PREFERRED = 1 AND CN.voided=0 WHERE CN.NAME = 'Radiology Result’AND OB.PERSON_ID = PT.PATIENT_ID AND OB.ENCOUNTER_ID = EN.ENCOUNTER_ID AND OB.voided=0)AND LOCALE_PREFERRED = 1 AND voided=0) AS Radiology_Result, (SELECT VALUE_TEXT FROM obs OB INNER JOIN openmrs.concept_name AS CN ON CN.CONCEPT_ID = OB.CONCEPT_ID AND CN.LOCALE_PREFERRED = 1 AND CN.voided=0 WHERE CN.NAME = 'X-Ray Barcode’AND OB.PERSON_ID = PT.PATIENT_ID AND OB.ENCOUNTER_ID = EN.ENCOUNTER_ID AND OB.voided=0) AS “XRay_Barcode”

FROM openmrs.patient AS PT INNER JOIN openmrs.person AS PR ON PR.PERSON_ID = PT.PATIENT_ID INNER JOIN openmrs.person_name AS NM ON NM.PERSON_ID = PT.PATIENT_ID INNER JOIN openmrs.patient_identifier AS ID ON ID.PATIENT_ID = PT.PATIENT_ID AND ID.IDENTIFIER_TYPE = 1 INNER JOIN openmrs.encounter AS EN ON EN.PATIENT_ID = PT.PATIENT_ID INNER JOIN openmrs.location AS LO ON LO.LOCATION_ID = EN.LOCATION_ID INNER JOIN openmrs.encounter_type AS ET ON ET.ENCOUNTER_TYPE_ID = EN.ENCOUNTER_TYPE INNER JOIN openmrs.users AS US ON US.USER_ID = EN.CREATOR WHERE PT.voided=0 AND ET.NAME=‘CXR Radiology’ AND ID.IDENTIFIER=‘203170100820-6’ AND EN.voided=0) AS CXR_Radiology ON Screening.PATIENT_ID=CXR_Radiology.PATIENT_ID;

6

@shahnewaz can you try to upgrade to SNAPSHOT -1.17.0

7

Now getting this error.

https://pastebin.com/nPZ0gDZE

8

The reporting module does additional validation and has additional constraints beyond just what might be possible in MySQL. Specifically, as the 1st error says, it does not allow you to return multiple columns with the same name because the resulting DataSet must have unique column names.

If you are explicit about what columns you are selecting out of your query (eg. do not use select *), things will likely work just fine.

Mike