Writing your own Report By Template

UpdatedMay 21, 2025

By Peter Hutten-CzapskiPeter Hutten-Czapski

Writing Your Own RBT

Writing your own templates lets you report on anything in OSCAR! You will need to have some idea of the Data Model and some SQL. Hone your SQL with Query by Example and then parameterize it into a RBT so that the end user can adjust the inputs that they need to get the report without drawing on your skills to write the underlying query.

The Report By Template format is an XML file constructed as below

The Report Element

<report title="Search Recent HL7 by MRP" description="Searches Labs after a given date for status, by MRP" active="1">

The <report> tag contains all the elements of the Report by Template. It has title, description and active attributes. It contains at least a <query> element

The Query Element

The <query> tag contains the SQL statements that constitute the report. Parameters are identified by {parameter_name} and are determined by the end user. The Query does not actually have to have parameters, they are optional, but make the report much more useful.

The Parameter Element

The <param> tag has a required id attribute which is the name for the parameter (eg {status_code}) and a required attribute type that can be one of the following 5

The list type provides the user with a drop down of options with only one choice allowed
The check type provides a list of choices where multiple combinations or all can be entered from predetermined choices
The date type provides a calendar picker
The text type provides for user free text
The textlist type provides a text box where multiple choices can be manually entered separated by commas

The list and check type entries provide multiple options that can be defined either dynamically by query or by static choices.

Static choices are defined by a contained <choice> element with a required id attribute which provides the text for the chosen parameter. The id is the value that replaces the place setter {} when the SQL is run and the parameter is resolved. The content of the <choice> tag is the text that the end user sees to pick the desired option.

<param id="status_code" type="check" description="Lab Status">
     <choice id="F">Filed</choice>
</param>

Dynamic choices are defined by a <param-query> tag that contains the SQL that creates the choice. The following will use this method to provide the same choice to the user as above

<param id="status_code" type="check" description="Lab Status">
     <param-query>SELECT 'F' as status_code, 'Filed' as status;</param-query>
</param>

Putting it together

Text and list types provide only one choice for the {parameter} and should be used with <>= or LIKE syntax in the <query>. Textlist and check type can have multiple choices and should be used with the IN syntax eg p.`status` IN (‘{status_code}’) might resolve to p.`status` IN (“F”,”A”)

TIPS:

Use predefined choices using list and check type options to limit end user error
Use concatenation to allow quick links from your report to make them user friendly. The following are common examples
- CONCAT( “<a target=new href=../../demographic/demographiccontrol.jsp?demographic_no=”,
  d.demographic_no, “<a/>” ) will open the Master Demographic
- CONCAT( “<a target=’_blank’ href=../../oscarEncounter/IncomingEncounter.do?appointmentNo=&demographicNo=”,d.demographic_no,”&curProviderNo=&reason=Tel-Progress+Note&encType=&curDate=&appointmentDate=&startTime=&status=>eChart<a/>” ) will open the patients eChart

EXAMPLE:

<report title="Search Recent HL7 by MRP" description="Searches Recent Labs after the given date for status, by MRP" active="1">
<query>
-- this is a standard SQL parameterized query with parameters listed in {} brackets
-- here we form a link to the lab for the end users convenience 
SELECT CONCAT( "<a target=new href=../../lab/CA/ALL/labDisplay.jsp?segmentID=", p.lab_no, ">", p.lab_no, "<a/>" ) AS "Lab",
       p.lab_type,
       p.provider_no,
       p.timestamp,
       h.discipline AS 'Description',
       CONCAT( h.last_name, ", ", h.first_name)  AS 'Patient',
       h.health_no AS 'HIN',
       p.status,
       p.comment
FROM   `providerLabRouting` p,
       `hl7TextInfo` h
WHERE  p.`lab_no` = h.`lab_no`
       AND p.`status` IN ('{status_code}')
       AND p.provider_no LIKE '{provider_no}'
       AND  p.timestamp >= '{date_start}'
ORDER  BY p.`id` DESC;
</query>
<comment> the parameters are defined in the following section </comment>
<comment> the list is one several types that display options to the end user</comment>
<param id="provider_no" type="list" description="Family Doctor">
    <param-query> 
    <comment> the results of this parameter query give the option for provider, this is a commonly reused bit of code </comment> 
        SELECT '%' as provider_no, 'All Providers' as provider 
        UNION 
		SELECT provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider 
	WHERE status='1' 
		AND provider_type='doctor' 
		AND ohip_no>1 
	ORDER BY provider_no;
    </param-query>
</param>
<comment> the date type will provide a calendar pick list </comment> 
<param id="date_start" type="date" description="Labs uploaded after:"> 
</param>
<comment> the check type will provide a master check box for all, and a check for each possible choice </comment>
<param id="status_code" type="check" description="Lab Status">
<comment> in a list or check list the id's provide the data, the text eg Filed is what the user sees </comment>
     <choice id="F">Filed</choice>
     <choice id="A">Acknowledged</choice>
     <choice id="N">New</choice>
     <choice id="X">Unlinked</choice>
</param>
</report>