:: Version 2.4.0 ::

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:

The Query phrasebook menu

The use the link “Create a new Query” link or the “New…” action 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”.

Previewing the query

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

Preview phrase 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 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.

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

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.

Testing a Query phrasebook entry

Executing a stored Query

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.

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 Excel, please refer to the chapter How to create an Excel report using iTop queries

2_4_0/admin/phrasebook.txt · Last modified: 2018/02/14 10:43 (external edit)
Back to top