Query Phrasebook

The Query Phrasebook stores a list of ready-to-run OQL queries so that end-users can easily execute complex queries defined by an administrator.

This is especially useful for building reports: simply integrate the results of such queries into a spreadsheet (for example using Excel's web query feature).

Click on the menu “Query phrasebook” in the “Admin tools” section, to display the list of stored queries:

The use the link “Create a new Query” link or the “New…” action to display the creation form:

The fields “Name” and “Description” are used to identify the queries.

The field “Expression” contains the actual OQL query. A query can contain parameters (identified by the : placeholder followed by the parameter's name). Using parameters allows to define generic queries that can be used in different contexts.

For example the following OQL query lists all the open Changes for a given customer (specified by its name):

SELECT Change WHERE status != 'closed" AND status != 'rejected" AND org_name = :customer

By default, all the fields of the selected objects are displayed as the output of the query. The field “Fields” allows to restrict this output to the given list of fields (by passing a comma separated list of field codes).

Once the query has been created, a preview of the results is shown.

It is possible to create a shortcut on the list or even share the list in an email. In such case, the OQL query will be shared: if the phrasebook entry gets updated later on, that change will not affect the results shown by clicking on the shared link ; only changes in the data will impact the results.

To test your query while writing it, click on the link “Test query” below the edit box. The page “Run Query” then opens in another window of your browser. If the query contains parameters, the page displays a form to enter them.

The execution of stored queries is done using the page webservices/export.php. By default, a link to this page with the suitable options for integrating into Excel is listed in the details of the stored query, but other options can be passed to the page to produce different outputs.

Refer to the documentation of export.php for more information about all the possible options.

For help about integrating the results into Excel, please refer to the chapter How to create an Excel report using iTop queries