The following types of gadget charts/custom reports can be generated using QMetry’s Advance Query Reports.
Table Chart
A table is a chart that organizes information in rows and columns and its statistics are usually presented in a tabular format. Let’s take an example of Table Chart report.
Report : Generate a report that displays no. of issues logged and has been opened/Ageing in a specific project (i.e. Defect Ageing Report).
SQL Query
Defect Ageing Report
SELECT
issues.entityKey as "QMetry Entity Key",
issues.jiraKey as "Jira Entity Key",
issues.summary as "Issue Summary",
issues.issuestatus "Issue Status",
DATEDIFF(CURDATE(), issues.createdDate) AS "Days Open"
FROM issues
WHERE issues.projectID in (`@Filter.project`)
AND issues.issueStatus IN ("OPEN", "TO DO", "In Progress")
Click on the “Fetch Filters & Verify” button to specify the filter value (i.e. in our case you will be asked to specify the Project); clicking on “Run Query” gives output by default in tabular format.
Apply the Filter
Query Output
Once you get your desired report, you can save the report as a Gadget by clicking on the Add Gadget button.
Once you save the gadget, it will be available in the QMetry Custom Gadget section > My Gadget tab.
Column Chart
A column chart is a data visualization where each category is represented by a rectangle, with the height of the rectangle being proportional to the values being plotted. The column charts are of two types.
Basic Column Chart
Stacked Column Chart
In QMetry, the Advance SQL Query provides an ability to generate both the charts as with the required values; let’s understand how both types of column chart can be generated by writing a SQL queries in detail.
Basic Column Chart
Let’s take an example of a basic Column Chart report.
Report : Generate a chart that displays the Count of the test cases by their assignees.
Count Of Test Cases By Assignee
SELECT COUNT(testexecutions.testcaseID) as "Testcase Count", AssignedTester.userAlias as "Assignee" FROM testexecutions JOIN users as AssignedTester ON testexecutions.assignedTester = AssignedTester.userID WHERE testexecutions.projectID in (`@filter.project`) GROUP BY testexecutions.assignedTester
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 Column Chart.
You can adjust the Columns based on the requirement to X-Axis, Y-Axis and Cross Tab.
X-axis always holds a single Test entity field with any value.
Multiple Test entity field on Y-axis creates a stacked chart; Y-axis always contain fields with numeric values.
Once you get your desired report, you can save the report as a Gadget by clicking on the Add Gadget button.
Once you save the gadget, it will be available in the QMetry Custom Gadget section > My Gadget tab.
Stacked Column Chart
Let’s take an example of a Column Chart report.
Report : Generate a chart that displays the Count of the test case execution per release in a project (i.e. Count Of Test Case Execution Per Release)
SQL Query
Count Of Test Case Execution Per Release
SELECT
testexecutions.releaseName as "Release Name",
testexecutions.tcExecutionStatusName "Exec Status",
COUNT(1) as "Execution Count"
FROM testexecutions
WHERE testexecutions.projectID in (`@Filter.project`)
AND testexecutions.tcExecutionStatusName NOT IN ("Not Run")
GROUP BY testexecutions.releaseName, testexecutions.tcExecutionStatusName
Apply the Filter
Query Output
By default, it gives you output in tabular format. You can modify the output format. You can switch to the available chart types from the table. In our case, we will switch it to the Column Chart.
You can adjust the Columns based on the requirement to X-Axis, Y-Axis and Cross Tab.
X-axis always holds a single Test entity field with any value.
Multiple Test entity field on Y-axis creates a stacked chart; Y-axis always contain fields with numeric values.
Cross tab field will help to create a Group Stacked chart.
Once you get your desired report, you can save the report as a Gadget by clicking on the Add Gadget button.
Once you save the gadget, it will be available in the QMetry Custom Gadget section > My Gadget tab.
Bar Chart
A bar graph is a chart that uses bars to show comparisons between categories of data. The bars can be either horizontal or vertical. There are two types of the bar chart.
Basic Bar Chart
Stacked Bar Chart
In QMetry, the Advance SQL Query provides an ability to generate both the charts as with the required values; let’s understand how both types of bar charts can be generated by writing a SQL queries in detail.
Basic Bar Chart
Let’s take an example of a normal Bar Chart report.
Report : Generate a bar chart that displays the Count of the requirements by status
Count Of Requirements By Status
SELECT COUNT(requirements.entityKey) as "Requirement Count", requirements.requirementStatus as "Status" FROM requirements WHERE requirements.projectID in (`@filter.project`) GROUP BY requirements.requirementStatus
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 data columns based on your requirement to Label and Data section.
Label : Label always holds a single Test entity field with any value.
Data : Multiple Test entity field on Data creates a stacked chart; Data always contain fields with numeric values.
Once you get your desired report, you can save the report as a Gadget by clicking on the Add Gadget button.
Once you save the gadget, it will be available in the QMetry Custom Gadget section > My Gadget tab.
Stacked Bar Chart
Let’s take an example of a Stacked Bar Chart 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
SELECT count(*) as "Testcase Count", COALESCE(testcases.priority, 'No Priority') as "Test Case Priority", COALESCE(testcases.testCaseStatus, 'No Status') as "Status" FROM testcases WHERE testcases.projectID in (`@filter.project`) GROUP BY testcases.priority, testcases.testCaseStatus
Apply the Filter
Query Output
By default, it gives you output in tabular format. You can modify the output format. You can switch 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.
Once you get your desired report, you can save the report as a Gadget by clicking on the Add Gadget button.
Once you save the gadget, it will be available in the QMetry Custom Gadget section > My Gadget tab.