| Table of Contents | ||
|---|---|---|
|
...
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. |
...
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.
...
| icon | false |
|---|
.
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.
...
- SELECT * clause is not allowed. Column names need to be specified in the select statement.
- UPDATE and DELETE queries are not allowed.
- GROUP BY on text column is restricted. QMetry reporting tables store custom/udf fields of type lookup & multi-lookup as text fields. Hence GROUP BY on these fields are not possible.
- The gadgets other than table are generated on the 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.
| Info | ||
|---|---|---|
| ||
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.
...
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.