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 button 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).

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.

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 copied: 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.

If you want to share the result of the Query Phrasebook in a way that any change to the query will be reflected in the link, use the provided url

If you don't provide any fields, then using the provided url in spreadsheet can be affected by any future datamodel changes, which would add new fields or reorder 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 spreadsheet 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 spreadsheet, like Excel, please refer to the chapter How to create a spreadsheet report using iTop queries