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 have resemblance with 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:

If you have opted for the Advanced Reports App

Custom Fields (of Requirements and Issue modules) mapped to Jira or Azure, appears 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.

For example, an external custom field "Release Reference" is mapped to the Requirement module in the Integrations tab under Projects, and the sync feature is enabled, it is displayed as “ext_release_reference" in the requirementextudf table.

If you have not opted for the Advanced Reports App

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

Query Builder lets you create SQL queries such as SELECT statements. While writing a query, it automatically suggests field names when you type a table name followed by a dot (.).

If a query contains a syntax error, Query Builder helps you identify it using the SQL Syntax Validator.

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

Where, fieldname=testcases.latestExecutionStatus and status = value to print instead of "null"

For example,

COALESCE (testcases.latestExecutionStatus,'UnCovered')

The following query shows the usage of the COALESCE function in the query.

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

The test asset key becomes clickable on the report if the column name contains the keyword (i.e. Entity Key), in any of the formats like - "entity key" or "entityKey" or "entity-key" or "entity_key" in it.

Filter reports based on projects and folders. The system-pre-defined folder browse option allows you to filter custom reports based on folder structure, supporting both parent and subfolder selection. This feature is available for Requirements, Test Cases, and Test Suites, and functions only when a single project is selected.

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

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

Scenario 1: Single Project Selection:

Select a single project in the project filter. The folder filtering option remains available, allowing further refinement of the report based on the folder structure.

Scenario 2: Multi-project Selection

Select multiple projects. The system disables the folder filtering option, preventing further filtering based on folder structure.

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

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

Report: Generate a bar chart that displays the Count of the test cases by status over priority.

SELECT
count(1) as "Test Case Count",
COALESCE(testcases.priority, 'No Priority') as "Test Case Priority",
COALESCE(testcases.testCaseStatus, 'No Status') as "Test Case Status"
FROM testcases
WHERE testcases.projectID in (`@filter.project`)
GROUP BY testcases.priority, testcases.testCaseStatus

Query Output

By default, it gives you output in tabular format. You can modify the output format by switching to the available chart types from the table.

Adjust the Columns based on the requirement to Label, Data and Cross Tab sections.

• Label always holds a single Test entity field with any value.

• Multiple Test entity field on Data creates a stacked chart; Data always contain fields with numeric values.

• Cross Tab field will help to create a Group Stacked chart.

The Chart Settings allows you to customize the appearance of the graph. You can customize the chart colors based on legends used in the chart along with a feature to enable/disable the legends as required; here’s how you can do it.

Colors of Execution Status used as legends appear as set in Customizations. You can not modify the colors from chart settings as the option remains grayed out for execution status (refer to the attached screenshot).