Excel Report Query Builder Dialog Box

This dialog box enables you to create an SQL query.

To access

  1. On the ALM sidebar, under Dashboard, select Analysis View.

  2. Expand the analysis tree and select an Excel report.

  3. Under the Configuration tab, click the Query tab, and then click the Query Builder button.
Important information

  • To ensure the integrity of the database, you should run only queries that extract data from the project database. For details, see Protecting the Database.

  • You can include parameters in SQL queries. For details, see Parameters in SQL Queries.
Relevant tasks

How to Generate an Excel Report

See also

User interface elements are described below (unlabeled elements are shown in angle brackets):

UI Element

Description

Add Query. Enables you to create a new query. A new tab is added to the query builder with the default name Query<number>.

When you generate the report, the results of each query are displayed in separate Excel worksheets.

Delete. Deletes the selected query.

Rename Query. Enables you to rename the current query.

Note: The Excel worksheet containing the data extracted by the query will have the same name as the query.

Test Query. Displays the first ten results of the query in the Query Results tab. If your query contains parameters, ALM uses the default values for the parameters.

Run Query. Displays results of the query in the Query Results tab. If your query contains parameters, ALM prompts you for values.

Database Type. Displays the type of database used for your project database.

You must write the SQL query using the syntax used by the project database type.

Find. Searches the query for text that you entered in the find box.

Tip: Click the Find button again to find the next occurrence of the text.
<SQL editor>

Contains the SQL query that defines the data set that is extracted to the Excel report.

Note: Separate project entities with commas.

Syntax exception: The @ and \ characters have a special meaning and are not read literally when running SQL queries on the project database. If you want these characters to be read literally, you should replace @ by \@ and \ by \\. For example, if you want the statement
SELECT * FROM BUG WHERE BUG.BG_SUMMARY = `@parameter@' to be run literally on the ALM database, type SELECT * FROM BUG WHERE BUG.BG_SUMMARY = `\@parameter\@'.Contains the SQL query that defines the data set that is used for the Excel report.
Copy/Paste

Copies selected text from one place in the SQL editor to another.

Available from: Query editor right-click menu
Cut/Paste

Moves selected text from one place in the SQL editor to another.

Available from: Query editor right-click menu

Contains a list of ALM database fields and entities that you can add to the query.

User interface elements are described below:

UI Element

Description

Add Entity to Query. Adds the selected project entity to the SQL query. The entity is inserted at the cursor position.

To add a project entity with the column alias defined by the entity title, click the Add Entity to Query arrow and select Add Entity to Query with Entity Title as Alias.

Available from: Entities pane

Entities View. Enables you to view fields and entities in the Entities pane by their name, their database column name, or both.

Available from: Entities pane

Contains a list of parameters available for use in the queries.

User interface elements are described below:

UI Element

Description

Add Parameter to Query. Adds the selected parameter to the SQL query. The parameter is added at the cursor position.

Note: For a parameter representing a text value, you must manually add single quotes (') around the parameter in the SQL pane. For example, if the SQL pane contains the phrase WHERE BUG.BG_DETECTED_BY = @name@, you must replace the text @name@ with '@name@'.

Available from: Query Parameters tab

New Query Parameter. Opens the New Query Parameter dialog box, enabling you to add a parameter to the parameters grid. For details, see New Query Parameter/Edit Query Parameter Dialog Box.

Available from: Query Parameters tab

Edit Query Parameter. Opens the Edit Query Parameter dialog box, enabling you to edit the selected parameter. For details, see New Query Parameter/Edit Query Parameter Dialog Box.

Available from: Query Parameters tab

Delete Query Parameter. Deletes the selected parameter from the parameters grid.

Caution: If you delete a parameter that is in use in a report query, queries containing the parameter will not be valid.

Available from: Query Parameters tab

Displays preview results of an SQL query.