Advance Query Reports

Note

QMetry Insight module is visible only if you have View and Modify rights for QMetry Insight.

Advanced Query Reports is designed for technical users with SQL knowledge. Use it to write SQL queries, basic or complex, generate custom reports. The Query view gives you access to consolidated QMetry data and control over generating custom reports.

The screen is divided into two main areas. The left panel lists all available tables and their fields. The right side contains the Query Builder, Filter panel, and Result panel.

Understanding the Screen Layout

The Advance Query Reports screen has four sections:

A. View Report Schema DB Tables: Browse available tables and fields.

B. Query Builder: Write and run SQL queries

C. Filter panel: Apply project and folder filters to your query.

D. Result panel: Preview results and select chart types.

QPro_Reports_AQR_Layout.png

Report Database Schema

The left panel lists all tables and their fields. QMetry Insight consolidates data across 15 tables, synced in real-time. Table names correspond to QMetry test assets and modules for easy reference.

View the full Entity Relationship Diagram: Report Database Schema/ER Diagram

Note

In Requirements and Issues tables, field names with the Jira prefix are renamed to use the Ext prefix.

Each field displays an icon indicating its data type:

varchar.png

Type - varchar (text (short string))

bigint.png

Type - bigint or int (numeric identifier)

text1.png

Type - text (long text)

datetime.png

Expand a table to view all its fields. Hover over any field to see its type and size.

Table reference

Table Name

Details

issues

Internal and external issues.

issuecycle

Cycles associated with issues.

issueextudf

Custom fields of external tracker mapped with the Issue module. The table is available only if you have opted for the Advanced Reports App.

requirementissue

Issues linked with requirements.

requirementextudf

Custom fields of external tracker mapped with the Requirement module. The table is available only if you have opted for the Advanced Reports App.

requirementreleasecycle

Release and cycle associated with a requirement version.

requirements

QMetry and Jira requirements.

requirementtestcase

Testcases linked to requirements.

testcaseissue

Issues linked with test cases without execution.

testcasereleasecycle

Release and cycle associated with a test case version.

testcases

Test case details.

testcasetestsuite

Test cases linked with test suites.

testexecutionissue

Issues found during executions.

testexecutions

Testcase execution details.

teststepexecution

Execution details of a Test case - steps.

teststeps

All the details of test case - steps.

testsuitereleasecycle

Release and cycle associated with test suites.

testsuites

All the test suites details.

users

User details.

All ids like createdBy, owner, executed by, etc will be mapped with `users` table to get the username, userAlias, fisrtname, lastname etc.

Testcasefolder

Displays all folders associated with the test case, including Folder Name, Folder Path, Test Case Folder ID, Test Case ID, and the archived status of each folder (if applicable).

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 32. 

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.



QPro_AQR_External_Custom_Fields.png

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.

Test Run UDFs

Test Run UDFs are user-defined fields configured at the test execution run level. These fields are available in the testexecutions table.

Each Test Run UDF appears in the testexecutions table as a column prefixed with qmetry_. For example, a UDF named severity_level appears as qmetry_severity_level.

You can reference Test Run UDF columns in SELECT, WHERE, GROUP BY, and ORDER BY clauses. The following example queries Execution Status and an Test Run UDF across a project:

Querying Test Run UDFs

SELECT
testexecutions.projectName AS "Project Name",
testexecutions.releaseName AS "Release Name",
testexecutions.cycleName AS "Cycle Name",
testexecutions.tcExecutionStatusName AS "Execution Status",
testexecutions.qmetry_severity_level AS "Severity Level"
FROM testexecutions
WHERE testexecutions.projectID IN (`@filter.project`)
ORDER BY testexecutions.releaseName, testexecutions.cycleName

Filtering on Test Run UDFs

You can filter report results using Execution UDF columns. The following example filters executions by a specific UDF value:

SELECT
testexecutions.projectName AS "Project Name",
testexecutions.tcExecutionStatusName AS "Execution Status",
testexecutions.qmetry_severity_level AS "Severity Level"
FROM testexecutions
WHERE testexecutions.projectID IN (`@filter.project`)
AND testexecutions.qmetry_severity_level = 'Critical'

Grouping and sorting on Test Run UDFs

You can apply GROUP BY and ORDER BY to Test Run UDF columns. The following example counts executions grouped by an Execution UDF and Execution Status:

SELECT
testexecutions.qmetry_severity_level AS "Severity Level",
testexecutions.tcExecutionStatusName AS "Execution Status",
COUNT(1) AS "Execution Count"
FROM testexecutions
WHERE testexecutions.projectID IN (`@filter.project`)
GROUP BY
testexecutions.qmetry_severity_level,
testexecutions.tcExecutionStatusName

Control Custom Fields Synced to Advanced Query Reports

System Admins can control which Jira or Azure custom fields are synchronized with QMetry Insight Advance Query Reports. This prevents unnecessary data synchronization and gives admins precise control over which external fields are available for reporting.

Limiting synced fields improves reporting performance, data reliability, and reporting control.

Configure Custom Fields for Reporting

To configure which custom fields are available in Advance Query Reports, do the following:

Note

The custom fields must be enabled in the project configuration for reporting.

Sync_Fields_to_Report_New_Screen.png

To configure custom fields for reporting, perform these steps:

  1. Go to Integration.

  2. Select the Jira or Azure integration.

  3. Locate Sync fields to Reports.

  4. Select the Configure icon.

  5. Select the custom fields for reporting.

  6. Click Save.

Only the fields selected become available in Advanced Query Reports. You can add more fields later by updating the configuration.

Query Builder

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

If your query contains a syntax error, Query Builder identifies it using the SQL Syntax Validator.

QPro_Reports_AQR_QueryBuilder.png

Note

  • The system does not accept SELECT * clause. You must specify column names in the select statement.

  • The system does not accept UPDATE and DELETE queries.

  • You can apply Group By to custom fields and system fields of type text, including lookup, multi-lookup, fields. Custom fields include QMetry custom fields and external tracker fields synced with QMetry Insights.

  • Gadgets (other than tables) support up to 500 records. During execution, the query returns the first 500 records. All data is available in exported reports.

  • SQL queries support WITH clause, empowering users to build custom reports using Common Table Expressions (CTEs).

QPro_Reports_AQR_SelectQuery.png

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>')
QPro_AQR_Query for Coalesce.png
Example 33. 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.

QPro_Reports_AQR_EntityKey.png


Adding Filters

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.

QPro_Reports_AQR_SingleProject.png

Multi-project Selection

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

QPro_Reports_AQR_MultipleProject.png

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

Gadget Charts

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

Saving Gadgets

After you run a query, select Add Gadget to save the report and add it to a dashboard. The system saves the gadget to QMetry Custom Gadget.

QPro_Reports_AQR_AddGadget.png

The system adds the gadget to QMetry Custom Gadget section.

Customizing Chart Colors

Use Chart Settings to customize the appearance of any chart. You can change legend colors and show or hide legends and axis labels.

Example 34. Stacked Bar Chart by test case status and priority

  1. Write the query

    The following query counts test cases by status and priority, producing output suitable for a stacked bar chart:

    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

  2. Configure the chart layout

    QPro_Reports_AQR.png

    The query output appears in Table format by default. To switch to a chart, select a chart type from the Result panel. Then assign columns to the chart sections:

    • Label: A single test entity field with any value.

    • Data: Fields with numeric values. Adding multiple fields creates a stacked chart.

    • Cross Tab: Creates a Group Stacked chart.

  3. Customize Chart Colors

    To customize the chart's appearance, do the following:

    QPro_ARQ_Modify_Chart_Colors.png

    1. Select the cog icon on the graph. The Chart Settings panel opens.

    2. To change a legend color, select its color swatch and choose a new color.

    3. To show or hide a legend, toggle its Visible setting.

    4. To show or hide axis labels, toggle the axis label Visible setting.

    5. To revert all color changes to system defaults, select Reset All under the Colors section.

    Note

    Execution Status legend colors are controlled by Customization > Execution Status. The color option in Chart Settings is disabled for these legends.



Editing Gadgets

The gadgets created in Advance Query Reports can be edited from the QMetry Custom Gadget.

To edit a gadget, do the following:

QPro_AQR_Edit_Gadgets.png

  1. Go to QMetry Custom Gadget and select the My Gadget tab.

  2. Select the Edit icon for the gadget to update. The system opens the Advance Query Reports SQL query builder for that gadget.

Exporting Gadget Data

You can export individual gadget data or chart images in multiple formats.

Export limits

  • Excel exports support up to 1,000,000 records per export.

  • Individual gadget CSV exports use the same limit.

Export Formats

  • Download chart images in PNG, JPEG, SVG Vector, or PDF.

  • Export All Data: Schedules an Export Report task. View the scheduled job in the Scheduled Task section and download the output as a CSV.

  • Export column Data in XLS: Downloads the Chart Data Table as an XLS file.

You can also export all custom dashboard gadgets through an API call.

QPro_AQR_Exporting_Gadget_Data.png

Best Practices

  1. Only users with knowledge of SQL queries shall have rights to write custom queries and can access QMetry data, as there is direct access to all QMetry data in Report Schema DB.

  2. The custom SQL queries must always include a project filter specified as : @FILTER.PROJECT. This prevents the recipients of the shared report gadgets from inadvertently viewing data from other projects that they do not have access to.

  3. The custom SQL report queries after creation must be run and saved against a Sample Project, so that the report does not load with the data of an un-intended project.

  4. The Report DB has tables like testcase, testexecutions, etc. which have user IDs instead of the actual information of the users. This information should be queried by writing an SQL Join with user IDs from `users` table now available in the Report DB schema.

In this section:
© 2026
Publication date: