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:

If you have opted for the Advanced Reports App

When you enable Sync fields to Reports in the Integrations tab, custom fields from Requirements and Issues that are mapped to Jira or Azure appears in QMetry Insight tables. Additional tables for User-Defined Fields (UDFs) are also included.

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 displays “ext_release_reference" in the requirementextudf table.

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.

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

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

For example,

COALESCE (testcases.latestExecutionStatus,'UnCovered')

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

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

The test asset key will become 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.

Users can filter reports based on projects and folders. The system-pre-defined folder browse option allows users 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 you select a single project.

The Test Case Folder Path column supports multiple folders, allowing users to view all associated folder paths for a test case in reports.

Scenario 1: Single Project Selection:

When you 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

When the user selects multiple projects, the folder filtering option becomes disabled, 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 you want to preview the report.

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

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. In our case, we will switch it to the Bar Chart.

You can 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 icon is provided with each type of chart that allows you to customize the appearance of the graph. You can customize the chart colors of your choice 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 they set in Execution Status under Customization. You cannot modify the colors from chart settings as the option remains grayed out for execution status (refer to the attached screenshot).