how to add the location, provider in the report

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;

  CONCAT(p.given_name, " ", p.family_name) Patient, 
    pvd_p.given_name, " ", pvd_p.family_name
  ) Provider, Location 
  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;