Advance Query Reports of QMetry Insight is designed for technical users with the SQL knowledge who can write SQL queries and generate custom reports. The Advance Query view gives more power and flexibility to the users by providing them an ability to create custom reports by writing basic to complex SQL statements. The view majorly divided into two sections. On the left side, there is a list of tables and their fields that has the consolidated QMetry data required for reports and are made available to users in just 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 & different modules.
QMetry Insight module will only be visible if the user has the View and Modify rights for QMetry Insight.
The QMetry Insight module consists of the Query Builder. The query builder is used to create custom queries and add filters to generate reports accordingly. This document includes details about how to create Custom Reports using SQL query.
The screen is divided into four parts:
A. View Report Schema DB Tables
B. Query Builder
C. Filter panel
D. Result panel
The following is the database schema to view the entity-relationship diagram for advanced query reports.
In QMetry Insight, you can see the list of tables and their fields on the left panel. The panel contains the consolidated QMetry data in just 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.
Fields with this icon indicate the Type - varchar. | |
Fields with this icon indicate the Type - bigint/int. | |
Fields with this icon indicate the Type - text. | |
Fields with this icon indicate the Type - datetime. |
On expanding the table, you will find all the associated fields in it. It will give you the details of the field's `Type` and `Size` when hovering the mouse over any of the fields.
Here is the quick overview of tables available as part of report schema and kind of data stored on it :
Table Name | Details |
issues | Details of QMetry and Jira issues. |
requirementissue | Details of issues linked with requirements. |
requirementreleasecycle | Details of release and cycle associated with a requirement version. |
requirements | Details of QMetry and Jira requirements. |
requirementtestcase | Details of Testcases linked to requirements. |
testcasereleasecycle | Details of release and cycle associated with a test case version. |
testcases | All the test case details. |
testcasetestsuite | Details of test cases linked with test suites. |
testexecutionissue | Details of 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 | Details of release and cycle associated with test suites. |
testsuites | All the test suites details. |
users | All the details of users. All ids like createdBy, owner, executed by, etc will be mapped with `users` table to get the username, userAlias, fisrtname, lastname etc. |
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 as shown in the below image.
Note:
Note: Custom Fields (of Requirements and Issue modules) which are mapped with Jira 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.
Once you edit the query using required fields, click Run Query to generate the report.
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.
You can write a query and run it to generate the report. Different types of charts can be generated and then added as a report to dashboard using Add Gadget button.
The gadget will be added to QMetry Custom Gadget section.
The gadgets created under Advance Query Reports can be edited from the QMetry Custom Gadget.
1. Open the My Gadget tab.
2. Click on the Edit icon for the gadget that you want to edit. It navigates you to the Advance Query Reports (SQL query builder view) of the QMetry Insight module.
The excel file can contain maximum 1000000 records in one time export. Individual Gadget data can be exported into CSV with the same limit.
You can download the chart image in PNG, JPEG, SVG Vector, and PDF formats.
Export All Data: It will schedule the Export Report task. You can see the scheduled job in the Scheduled Task section and download the report in CSV. This will be a detailed report.
Export column Data in XLS: It will immediately download the details of the Chart Data Table (tabular details) in XLS.
You can also export all the custom dashboard gadgets through the API call. Refer to the link - API for Reports for more details.