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).
Creating a Stored Query
Click on the menu “Query phrasebook” in the “Admin tools” section, to display the list of stored queries:
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
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).
Testing a stored query
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.
Previewing the query
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.
Executing a stored Query
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