The following types of gadget charts/custom reports can be generated using QMetry’s Advance Query Reports.
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).
Defect Ageing Report
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.
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.
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.
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
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 |
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.
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)
Count Of Test Case Execution Per Release
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 |
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.
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.
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
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 |
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.
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.
Count Of Test Cases By Status Over Priority
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 |
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.
A line chart usually displays the information on a series of data points. Let’s take an example of a Line Chart report.
Report : Generate a report that displays the Count of Issues per a release by priority.
Count Of Issues Per Release By Priority
Count Of Issues Per Release By Priority
SELECT COUNT(issues.entityKey) as "Count of Issue Entity Key", issues.priority as "Issue Priority", testexecutions.releaseName as "Executions Release Name", testexecutions.cycleName as "Executions Cycle Name" FROM testexecutions INNER JOIN testexecutionissue ON testexecutions.tcExecutionID = testexecutionissue.tcExecutionID INNER JOIN issues ON testexecutionissue.issueID = issues.issueID WHERE issues.projectID IN (`@filter.project`) AND issues.priority in (`@filter.issues.priority`) GROUP BY issues.priority, testexecutions.releaseName |
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 Line 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.
An area chart represents the change in a one or more quantities over time. It is made by plotting a series of data points over time, connecting those data points with line segments, and then filling in the area between the line and the x-axis with color or shading. Let’s take an example of an Area Chart report.
Report : Generate an area chart that displays the Count of the test cases executed per day along with the execution status (i.e. Test Case Execution Per Day)
Test Case Execution Per Day
Test Case Execution Per Day
SELECT
DATE_FORMAT(testexecutions.tcExecutionEndTime, "%m/%d/%Y") "Execution Date",
testexecutions.tcExecutionStatusName "Execution Staus",
COUNT(1) as "Test Case Count"
FROM testexecutions
WHERE testexecutions.projectID in (`@filter.project`)
AND testexecutions.tcExecutionStatusName NOT IN ("Not Run", "Not Applicable")
GROUP BY DATE_FORMAT(testexecutions.tcExecutionEndTime, '%m/%d/%Y'), testexecutions.tcExecutionStatusName |
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 Area 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.
Pie charts are generated based on count for an entity. Let’s take an example of a Pie Chart report.
Report : A pie chart showing number of issue logged by its status in a Project (Name : Count of Defects By Status For A Project).
Count of Defects By Status For A Project
Count of Defects By Status For A Project
SELECT count(*) as "Issue Count", issues.issueStatus as "Issue Status" FROM issues WHERE issues.projectID in (`@Filter.project`) GROUP BY issues.issueStatus |
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 Pie Chart.
You can adjust the Columns based on the requirement to the Label and Value sections.
Label always holds a single Test entity field with any value.
Value always holds a single Test entity field with any value (i.e. 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.
Doughnut charts are generated based on count for an entity. Let’s take an example of a Doughnut Chart report.
Report : Generate a chart that displays the Count of Issues logged, by their priority for a specific project (Name - Count of Defects By Priority For A Project).
Count of Defects By Priority For A Project
Count of Defects By Priority For A Project
SELECT issues.priority as "Priority", count(1) as "Issue Count" FROM issues WHERE issues.projectID in (`@Filter.project`) GROUP BY issues.priority |
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 Doughnut Chart.
You can adjust the Columns based on the requirement to the Label and Value sections.
Label always holds a single Test entity field with any value.
Value always holds a single Test entity field with any value (i.e. 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.
Cross table reports are two dimensional charts that are grouped on entities at row and column level. Mostly the summary charts are generated using cross tables. Let’s take an example of a Cross Table report.
Report : Generate a report that displays the count of test cases executed for a sprint i.e cycle grouped by their execution status for a project (Name : Summary of Test Case Execution Status By Sprint).
Summary of Test Case Execution Status By Sprint
Summary of Test Case Execution Status By Sprint
SELECT testexecutions.projectName as "Project Name", testexecutions.releaseName as "Release Name", testexecutions.cycleName as "Sprint", testexecutions.tcExecutionStatusName "TestCase Execution Status", COUNT(1) as "Execution Count" FROM testexecutions WHERE testexecutions.projectID in (`@Filter.project`) GROUP BY testexecutions.releaseName, testexecutions.cycleName, testexecutions.tcExecutionStatusName |
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 Cross Table report.
You can adjust the Columns based on the requirement to the Row Header, Column Header, and Values sections.
Row Header : Multiple Test Entity fields creates a grouped view in the Row section.
Column Header : Multiple Test Entity fields creates a grouped view in the Column section.
Values : Multiple/Single Value fields.
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.
A Scatter chart displays points that show the relationship between two sets of data. Let’s take an example of a Scatter Chart report.
Report : Generate a chart that displays a count of test cases authored in Draft and Approved for Execution day-wise for a Project (i.e. Test Case authoring daily progress - Draft Vs Approved For Execution)
Test Case authoring daily progress - “Draft” Vs “Approved For Execution”
Test Case authoring daily progress - “Draft” Vs “Approved For Execution”
SELECT
DATE_FORMAT(testcases.versionCreatedDate, '%m/%d/%Y') as "Testcase Created Date",
testcases.testCaseStatus as "Status",
COUNT(1) as "Testcase Count"
FROM testcases
WHERE testcases.projectID in (`@filter.project`)
AND testcases.testCaseStatus IN ("Draft", "Approved For Execution")
GROUP BY DATE_FORMAT(testcases.versionCreatedDate, '%m/%d/%Y'), testcases.testCaseStatus |
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 Scatter 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.
The advance report Guage chart is used to display the percentage of a type from total number of records. Let’s take an example of a Gauge Chart report.
Report : Generate a chart that displays the Percentage of Automated Executions for a combination of a Project, Release, and Cycle.
Percentage Of Automated Executions For A Project, Release, and Cycle
Percentage Of Automated Executions For A Project, Release, and Cycle
SELECT COUNT(testcases.entityKey) as "Total Testcase Execution", COUNT(CASE WHEN testexecutions.isAutoExecuted=1 THEN 1 END) as "Automated Execution", COUNT(CASE WHEN testexecutions.isAutoExecuted=1 THEN 1 END)/COUNT(testcases.entityKey)* 100 as "Percentage" FROM testcases inner join testexecutions on testcases.testcaseID = testexecutions.testcaseID and testcases.tcVersion = testexecutions.executedVersion WHERE testexecutions.projectID in (`@filter.project`) AND testexecutions.releaseID in (`@filter.release`) AND testexecutions.cycleID in (`@filter.cycle`) |
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 Gauge Chart.
You can adjust the Columns based on the requirement to the Label and Value sections.
Label always holds a single Test entity field with any value.
Value always holds a single Test entity field with any value (i.e. 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.