OpenRPT Product Guide

Now we will create the query. Below is the SQL used in our query:

SELECT contacts.FirstName, contacts.LastName, calls.Subject, calls.Notes, calls.Duration
FROM calls, contacts
WHERE
<? if exists("All_ID") ?>
  contacts.ContactID = calls.ContactID
<? elseif exists("Caller_ID") ?> contacts.ContactID = <? value("Caller_ID") ?>
  AND contacts.ContactID = calls.ContactID
<? endif ?>
ORDER BY contacts.LastName;

This query joins the calls table and the contacts table on the contactID. The MetaSQL checks for the existence of the All_ID parameter. If it exists then the WHERE clause displays all calls. If the All_ID parameter does not exist then the WHERE clause displays all calls where the contacts.ContactID equals the value of the parameter Caller_ID.

If we select the Tools drop down menu and click Execute Query we see:

MetaSQL Results Output

Once we have a working query, we may save it to a text file by using the File menu option Save As. In the next section, we will copy and paste this SQL into the report definition's query source.