Hi, i create a report and what i want to add is the location and provider. In the data definition i suceed to create in encounter data but in the data set definition how do i add them? Please need your help
Thank you
Hi, i create a report and what i want to add is the location and provider. In the data definition i suceed to create in encounter data but in the data set definition how do i add them? Please need your help
Thank you
Hi @ornella, how do you want to add the Encounter Providers and/or Locations? As report parameters or indicators?
like a paramater, my purpose is to make a visit report who show the name of patient followed by the provider and location during the encounter
@ornella I’m not sure I fully understand the requirement, but I’d think you probably want to define a Row-Per-Patient Dataset definition or for simplicity an SQL Dataset definition. You may also look at the docs here: Reporting Module - Documentation - OpenMRS Wiki.
@ruhanga yes i tried i can’t add the encounter data in the row-per-patient dataset definition if someone knows which Dataset definition can be used to add encounter data. And for the sql dataset can you
@ornella, you could create the sql dataset definition with a query similar to the snippet below, perhaps even modify it to suite your needs;
select
CONCAT(p.given_name, " ", p.family_name) Patient,
CONCAT(
pvd_p.given_name, " ", pvd_p.family_name
) Provider,
l.name Location
from
encounter e
INNER JOIN person_name p ON e.patient_id = p.person_id
INNER JOIN location l ON e.location_id = l.location_id
LEFT OUTER JOIN encounter_provider ep ON e.encounter_id = ep.encounter_id
LEFT OUTER JOIN provider pvd ON ep.provider_id = pvd.provider_id
LEFT OUTER JOIN person_name pvd_p ON pvd.person_id = pvd_p.person_id;