Advance Query Reports

1 Introduction
2 Understanding Screen Layout
- 2.1 Report Database Schema / Entity Relationship Diagram
- 2.2 Query Builder
- 2.3 Adding Filters
- 2.4 Gadget Charts
3 Saving Gadgets
4 Modify Chart Colors
- 4.1 Step 1: Create a Custom Report
- 4.2 Step 2: Modify Chart Colors
5 Edit Gadgets
6 Exporting Gadget Data
7 Best Practices

Permissions: QMetry Insight module will only be visible if the user has the View and Modify rights for QMetry Insight.

Introduction

Advance Query Reports of QMetry Insight is designed for technical users with 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 the ability to create custom reports by writing basic to complex SQL statements.

The interface is divided into two main sections. The left panel contains a list of tables and their fields, representing consolidated QMetry data needed for reporting. This data is accessible in just 15 tables which are synced in real-time. For making the query creation easier the names of the tables have a resemblance with QMetry test assets & different modules.

Understanding Screen Layout

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

Report Database Schema / Entity Relationship Diagram

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

Report Database Schema/ER Diagram

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.

In Requirements and Issues tables, field names having "Jira" as prefix are renamed with “Ext” prefix. For more details refer this page.

	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 a quick overview of tables available as part of the report schema and the kind of data stored on it:

Table Name	Details
issues	Details of QMetry and external issues.
issuecycle	Details of cycles associated with issues.
issueextudf	Details of 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	Details of issues linked with requirements.
requirementextudf	Details of 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	Details of release and cycle associated with a requirement version.
requirements	Details of QMetry and Jira requirements.
requirementtestcase	Details of Testcases linked to requirements.
testcaseissue	Details of issues directly linked with test cases without execution. The table is provided to enable the creation of reports containing defects linked directly to the test case.
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.

If you have opted for the Advanced Reports App

Custom Fields (of Requirements and Issue modules) which are mapped to Jira, Rally, or Azure, will 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 mapped to the Issue module are included in the issueextudf table.
External custom fields mapped to the Requirement module are included in the requirementextudf table.

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

If you have not opted for the Advanced Reports App

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

Query Builder

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.

Gadgets, apart from tables, are generated on 500 records.
While creating queries, during execution the query will return just the first 500 records. However, all data is available in the reports export.
By default, the latest test execution status for uncovered test cases is shown as "null." To display a status other than "null" for such uncovered test cases, use the COALESCE function in the query with the following syntax:

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.

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.

Adding Filters

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 a single project is selected.

To fetch data from a specific folder, an equal (=) operator must be used. For example:

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

To retrieve data from subfolders, the IN operator should be applied. For example:

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

Scenario 1: Single Project Selection:

When the user selects 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.

Gadget Charts

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.

Saving Gadgets

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.

Modify Chart Colors

In QMetry, the Advance SQL Query provides an ability to generate custom reports with the required fields/values; let’s understand how to customize/modify colors on your custom report charts.

Let’s take an example of a Stacked Bar Chart report.

Step 1: Create a Custom Report

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

SQL Query

Count Of Test Cases by Status Over Priority

Apply the Filter

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.

Step 2: Modify Chart Colors

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.

Click on the cog icon on the graph. It opens the Chart Settings panel.
You can customize the color of the legends as per your requirement.
You can show/hide the legends on the graph by enabling/disabling the Visible settings.
You can also show/hide the axis labels on the legends by enabling/disabling the Visible settings.
Click on the “Reset All” option under the Colors section to revert the changes (it will reset the colors to system-defined colors).

Edit Gadgets

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.

Exporting Gadget Data

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.

Best Practices

Rights to write custom queries must be provided only to those users who have knowledge of writing SQL queries and can access any QMetry data, as there is direct access to all QMetry data in Report Schema DB.
The custom SQL queries must always include a project filter specified as : @FILTER.PROJECT. This will prevent the recipients of the shared report gadgets from inadvertently viewing data from other projects that they do not have access to.
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.
The Report DB has tables like testcase, testexecutions, etc. which now only 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.