Advance Query Reports

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

Insight lists tables and their fields on the left panel. The panel contains the consolidated data in 15 tables which are synced in real-time. For making the query creation easier, the names of the tables have resemblance with test assets and different modules.

In Requirements and Issues tables, field names having "Jira" as prefix are renamed with “Ext” prefix.

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

Custom Fields (of Requirements and Issue modules) which are mapped to Jira, Rally, or Azure, appears in 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.

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 is 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 Insight tables. Only Fields data and external tracker System Fields data which are synced will be 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 as shown in the below image.

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 are not displayed in Insight tables. Only Fields data and Jira System Fields data which are synced will be available for generating reports.

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

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.

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

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

Scenario 1: Single Project Selection:

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

Select 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 to preview the report.

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.

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 Customizations. You can not modify the colors from chart settings as the option remains grayed out for execution status (refer to the attached screenshot).