Upon starting the MetaSQL Editor, we should next connect to the database by clicking the “File” drop down menu option. Next, select “Database” and finally “Connect”. You will see the standard OpenRPT connection screen:

OpenRPT Connection Screen

Before proceeding, set the connection options by clicking on the OPTIONS button. You will see:

Login Options for an ODBC Connection

You will need to set the fields as follows:

Click the SAVE button, and then on the Log In screen click the LOGIN button. Remember that the ODBC connection handles user authentication, so the “Username” and “Password” fields are normally not required when using this connection methodology.

Earlier you learned about passing parameters to a report’s query using MetaSQL. For this report we will need two parameters: All_ID and Caller_ID. Use the drop-down menu option “Tools” and then “Parameter List” to create these two parameters:

MetaSQL Parameters

The values for the parameters are unimportant. As you will see in the query, just the existence of the All_ID parameter will cause the query to display data for all callers in the database.

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.