Include the Relationship in the report

Hi please i would like to make a report for all registred patient and i need to include the relationship type

@gsluthra, @angshuonline can you also have a look

Please i really need help.

You are looking for an SQL query to list all patients and their relationship names/type?

cc: @abhinab @rahu1ramesh @mohant

@gsluthra yes, please i really need it

@jess I am not sure if I understand your requirement correctly, but from the above conversation I get that you wish to have a report of all the patients and the people they are related to.

You can do this by adding the following configuration to the default-config/clinic-config in the openmrs/app/reports/reports.json file:

"PatientRelationReport": {
    "name": "Patient Relation Report",
    "type": "MRSGeneric",
    "requiredPrivilege": "app:reports",
    "config": {
      "sqlPath": "/etc/bahmni_config/openmrs/apps/reports/sql/patientRelationReport.sql"

And by including the below SQL query in the openmrs/apps/reports/sql/patientRelationReport.sql file:

SELECT CONCAT(pera_name.given_name, " ", pera_name.family_name) AS Patient,
       CONCAT(perb_name.given_name, " ", perb_name.family_name) AS "Patient Bystander",
       rt.a_is_to_b AS Relation
FROM relationship AS r
JOIN patient AS pa ON r.person_a = pa.patient_id
JOIN relationship_type AS rt ON r.relationship = rt.relationship_type_id
JOIN patient AS pb ON r.person_b = pb.patient_id
JOIN person AS pera ON pa.patient_id = pera.person_id
JOIN person AS perb ON pb.patient_id = perb.person_id
JOIN person_name AS pera_name ON pera.person_id = pera_name.person_id
JOIN person_name AS perb_name ON perb.person_id = perb_name.person_id
WHERE CAST(pa.date_created AS DATE) BETWEEN '#startDate#' AND '#endDate#';

If this isn’t what you’re looking for, please do explain what the requirement actually is.