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

Only administrators have access to the “Query Phrasebook” menu.

Creating a Stored Query

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

Query phrasebook menuQuery phrasebook list

The use the Create a new Query button to display the creation form:

Form to create a new Query

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

In the output, the order of the fields does not depend on the order in which the fields have been listed in “Fields”.

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.

Button for testing the OQLRun Query to test an OQL

Previewing the query

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

The preview cannot be shown if there is any parameter in the query.

Email results

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

Preview phrase results

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.

URL to run a stored query

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

latest/admin/phrasebook.txt · Last modified: 2024/06/20 17:28 (external edit)
Back to top
Contact us