Reports

From MirrorMed

Contents

[edit]

Intro

The MirrorMed reporting engine is very powerful, and pretty easy to use. There are three steps to creating any given report.

[edit]

Create the Report SQL

The best way to develop a sql query is to use something like phpmyadmin. You simply need to create a series of SQL calls that gives you the data that you need. To start with you want to start with a static data item, like a specific encounter that you have created. 

SELECT *
FROM person, encounter, person_number AS pn, number, person_address AS pa, address
WHERE pa.address_id = address.address_id
AND pa.person_id = encounter.treating_person_id
AND pn.number_id = number.number_id
AND pn.person_id = encounter.treating_person_id
AND encounter.treating_person_id = person.person_id
AND encounter.encounter_id = 600038

This gathers information regarding a particular provider from the encounter id (600038) you can run this easily from the phpmyadmin sql screen or directly from the mysql command line client. Try it out...

Now you are ready to start creating a report. Go to the add reports menu... Admin->Reports->Add Report. Add a title, and a description of your choice, like "My Report" and "A lovely report" Then add the sql and label it like so.. 

---[provider_info]---
SELECT *
FROM person, encounter, person_number AS pn, number, person_address AS pa, address
WHERE pa.address_id = address.address_id
AND pa.person_id = encounter.treating_person_id
AND pn.number_id = number.number_id
AND pn.person_id = encounter.treating_person_id
AND encounter.treating_person_id = person.person_id
AND encounter.encounter_id = 60003

Now you have a report... it might be useful to save it at this point. Then go to Admin->Reports->List Report to take a look at the report. Since the encounter id is hard coded into the report it will always work.. The default view is a simple tabular display of the data. Ok now lets get back to editing... add the following sections to the code... 

---[provider_info]---
SELECT *
FROM person, encounter, person_number AS pn, number, person_address AS pa, address
WHERE pa.address_id = address.address_id
AND pa.person_id = encounter.treating_person_id
AND pn.number_id = number.number_id
AND pn.person_id = encounter.treating_person_id
AND encounter.treating_person_id = person.person_id
AND encounter.encounter_id = 600038
---[patient_info]---
SELECT *
FROM person, encounter, person_number AS pn, number, person_address AS pa, address
WHERE pa.address_id = address.address_id
AND pa.person_id = encounter.patient_id
AND pn.number_id = number.number_id
AND pn.person_id = encounter.patient_id
AND encounter.patient_id = person.person_id
AND encounter.encounter_id =600038
---[form_text]---
SELECT *
FROM storage_text AS s, form_data AS f
WHERE (
f.form_data_id = s.foreign_key
)
AND (f.external_id = 600038
)
---[form_string]---
SELECT *
FROM storage_string AS s, form_data AS f
WHERE (
f.form_data_id = s.foreign_key
)
AND (
f.external_id = 600038 
)

Note that everything is tied statically to encounter 600038. If you do not have an encounter so numbered you should replace that number with an encounter that actually exists on your system. Also note that if you do not have all of the patient and provider data included the report might not work. Your milage may vary.


Note that I am referencing an encounter that has a simple Vitals form associated with it. Which is why the there is data in the form_text section. My Vitals EHR Form has the variables 

diastolicbp
height
pulse
systolicbp
temp 
weight

So this report would only work once this form has been filled out.

TODO Show how to use GET variables to drive the reports per datapoint, ie per encounter.


[edit]

Create the Report Template

Now you may have noted that after creating the report you can upload a template. This is what makes a report a report as opposed to just a bunch of sql calls... This is smarty so you can mix HTML for layout and use smarty to access to data that you sql queries have provided you. 

 {assign var=provider_info_array value=$provider_info_ds->toArray()}
 {assign var=provider_info value=$provider_info_array[0]}
 {assign var=patient_info_array value=$patient_info_ds->toArray()}
 {assign var=patient_info value=$patient_info_array[0]}
 {assign var=form_string_array value=$form_string_ds->toArray()}
 {assign var=diastolicbp value=$form_string_array[0]}
 {assign var=height value=$form_string_array[1]}
 {assign var=pulse value=$form_string_array[2]}
 {assign var=systolicbp value=$form_string_array[3]}
 {assign var=temp value=$form_string_array[4]}
 {assign var=weight value=$form_string_array[5]}
 <table border=0 cellspacing=0 cellpadding=2 width=100%  style="font-size:100%;vertical-align:top;">
 
 <tr>
 	<td>Physician Name: {$provider_info.last_name}, {$provider_info.first_name} </td>
 	<td>Phone: {$provider_info.number} Email: {$provider_info.email}</td>
 </tr>
 <tr>
 	<td> </td>
 	<td> </td>
 </tr>
 <tr>
 	<td>Date of Service: {$patient_info.date_of_treatment}</td>
 	<td>Temp: {$temp.value}  Resp: </td>
 </tr>
 <tr>
 	<td> </td>
 	<td>BP: {$systolicbp.value} / {$diastolicbp.value}  </td>
 </tr>
 <tr>
 	<td>Patient Name: {$patient_info.last_name}, {$patient_info.first_name} </td>
 	<td>Wt: {$weight.value}   BMI: {$weight.value/$height.value} </td>
 </tr>
 <tr>
 	<td>Date of Birth: {$patient_info.date_of_birth}</td>
 	<td>Ht: {$height.value} Goal: </td>
 </tr>
 <tr>
 	<td>Address: {$patient_info.line1} {$patient_info.city},  {$patient_info.postal_code}</td>
 	<td> </td>
 </tr>
 <tr>
 	<td>Phone: {$patient_info.number}</td>
 	<td> </td>
 </tr>
 <tr>
 	<td>E-Mail: {$patient_info.email} </td>
 	<td> </td>
 </tr>
 </table>

Take note of the statements like {assign var=provider_info_array value=$provider_info_ds->toArray()} This is what brings the results of the sql querys into an array scope that you can easily dereference. Note that there is one "toArray" call for variables that are named based on the sql sections that you created above. Once you have the arrays of data you can reference specific data points inside.

[edit]

Connect the Report

Now you simply need to connect the report by going to Admin->Report->Connect Report. You probably want to place the report on the main screen for the patient or something. You can place the report on any menu in the system. Or you can place a report on the patient itself. Hopefully this interface will simplify in the future.

To start with click the chooser for Dashboard Reports under Patient. It should be obvious how to add a menu item and associate a report with it that will be displayed on the Patient Dashboard.

Retrieved from "http://docs.mirrormed.org/index.php/Reports"