4.10.1 Report by Template Overview

Report by Template is a way to write scripts that can analyze the database and print or export the results.

This can be used for periodic reporting or special inquiries.

You can a tutorial and many examples at:  http://oscarcanada.org/oscar-users/emr-resource/report-templates

This section is for more advanced users who are writing queries for others to use.

Note: Need to find the admin role that allows access to the Admin, Report by Template section.

Input parameters

The advantage of RBT is that you can add user entered parameters to filter the results of the query.

The following input types are allowed: text, date, list, check

<report title="RBT Title" description="Sample RBT parameters" active="1">
<query>
  SELECT * FROM demographic LIMIT 1;
</query>
<param id="medname" type="text" description="Enter medication"> </param>
<param id="start_date" type="date" description="From date"> </param>
<param id="provider" type="list" description="Provider">
   <param-query>
      ( select distinct provider_no, concat(last_name,',',first_name,'(',provider_no,')') as name 
         from provider where provider_type = 'doctor' and status = 1 )
      UNION
      ( select ' ALL', 'All providers' )
      order by provider_no ;
   </param-query>
</param> 
<param id="appt_types" type="check" description="Appointment Type">
  <param-query> select name, name from appointmentType;</param-query>
</param> 
</report>

Keeping line feeds

When you select text fields, all the line feeds get removed and all the text is printed across the screen.

This code will retain the linefeeds in the text field so that they appear as entered.

UNHEX( REPLACE( HEX(notes), '0A', HEX('<br>') ))