Advance Query Reports

The following is the database schema to view the entity-relationship diagram for advanced query reports.

QMetry Insight lists tables and their fields on the left panel. The panel contains the consolidated QMetry data in 15 tables which are synced in real-time. For making the query creation easier, the names of the tables resembles QMetry test assets and different modules.

Expanding the table displays all associated fields in it. Hover over the fields to display the field's Type and Size.

Here is a quick overview of tables available as part of the report schema and the kind of data stored on it:

Custom Field and Advanced Report App

With the Advanced Reports App

Custom Fields (of Requirements and Issue modules) that are mapped to Jira or Azure appear in QMetry Insight tables when the Sync fields to Reports feature is enabled on the Integrations tab within the Integration module. Additional tables related to User-Defined Fields (UDFs) will also be displayed.

• External custom fields of Issue Module appear in the issueextudf table.

• External custom fields of Requirement module appear in the requirementextudf table.

Example 65. 

if you map an external custom field Release Reference to the Requirement module and enable sync, it appears as ext_release_reference in the requirementextudf table.

Without Advance Reports App

Custom fields for Requirements and Issues modules that are mapped with Jira and Azure do not appear in QMetry Insight tables. Only synced QMetry Fields data and external tracker System Fields data are available for reports.

If you have not opted for the Advanced Reports App

The Insight table does not display Custom Fields (of Requirements and Issue modules) mapped with Jira and Azure. Only QMetry Fields data and external tracker System Fields data which are synced are available for generating reports.

Query Builder allows you to write SQL statements like SELECT statements. While writing a query the query builder auto-suggest field names when the user inserts the table name and enters a dot (.), the field names of that tables are auto-suggest.

In case of a syntax error in a query, the query builder provides an ability to identify the error through the SQL Syntax Validator.

Handling null execution status for uncovered test cases

By default, the latest execution status for uncovered test cases is null. To display a different value, use the COALESCE function:

COALESCE (<fieldname>,'<statusvalue>')

Example 66. COALESCE function in a query.

SELECT testcases.entityKey,      
COALESCE (testcases.latestExecutionStatus, 'UnCovered')
FROM testcases
WHERE testcases.projectName =
'FitTracker';

The test asset key becomes a clickable link in the report if the column name contains the phrase entity key in any format: entity key, entityKey, entity-key, or entity_key.

Filter reports by project and folder. The folder browse option supports both parent and subfolder selection. This feature is available for Requirements, Test Cases, and Test Suites, and works only when a single project is selected.

To fetch data from a specific folder, use the equals (=) operator:

SELECT requirements.entityKey, requirements.folderPath FROM requirements
WHERE requirements.projectID = @filter.Project
AND requirements.rqFolderID = @filter.RequirementFolder

To retrieve data from a folder and all its subfolders, use the IN operator:

SELECT requirements.entityKey, requirements.folderPath 
FROM requirements
WHERE requirements.projectID = `@filter.Project` 
AND requirements.rqFolderID IN (`@filter.RequirementFolder`)

Single Project Selection:

When you select a single project, the folder filter remains active. You can further refine results by folder structure.

Multi-project Selection

When you select multiple projects, the system disables the folder filter. Folder-based filtering is not available across multiple projects.

Refer to the attached link for more details on System Filters Available for Query.

On the Result panel, select the report type you want to preview the report.

Here is an explanation on Types of Gadget Charts supported along with examples.