Test Track Dashboard | Seapine

Tracking defects, issues, and feature requests is a critical component of any software development and quality control process.The earlier and quicker bugs are resolved, the lower your development cost and the higher your product quality.

Here is a dashboard screenshot for “TestTrack Pro”

Image

 

Behind the Scenes SQL for the dashboard

Following are sample queries used in our dashboard. The TestTrack project was built using a MySQL database. Similar queries could be created for SQL Server.
/* Select count for each state type */

SELECT S.Name,
count(D.DefectNum)
FROM DEFECTS D,
STATES S
WHERE D.Status = S.StateOrder
GROUP BY S.Name
ORDER BY S.StateOrder

/* Select count of open issues for each priority type */
/* Note: If the project is modified and the Priority field is relabeled, this query will be querying against that new field (label) */

SELECT FP.Descriptor,
count(D.DefectNum)
FROM DEFECTS D,
STATES S,
FLDPRIOR FP
WHERE D.Status = S.StateOrder
AND S.Attrib = 1 — open attribute type
AND D.idPriority = FP.idRecord
GROUP BY FP.Descriptor
ORDER BY FP.idRecord

/* Select count of open issues for each severity type */
/* Note: If the project is modified and the Severity field is relabeled, this query will be querying against that new field (label) */

SELECT count(D.DefectNum) AS SeverityCount,
FS.Descriptor
FROM DEFECTS D,
STATES S,
FLDSEVER FS
WHERE D.Status = S.StateOrder
AND S.Attrib = 1 — open attribute type
AND D.idSeverity = FS.idRecord
GROUP BY FS.Descriptor
ORDER BY FS.idRecord

/* Select count of open Bugs */
/* For this example, using the Type field to designate “Bug”, “Feature Request”, or “Other” which have idRecord values of 4, 5 and 6 respectively. These values can be ascertained by querying the FLDTYPE table. */
/* Note: If the project is modified and the Type field is relabeled, this query will be querying against that new field (label) */

SELECT F.Descriptor,
count(D.idType)
FROM DEFECTS D,
FLDTYPE F,
STATES S
WHERE D.idType = F.idRecord
AND D.Status = S.StateOrder
AND S.Attrib = 1
AND F.idRecord = 4 — idRecord for a “Bug” type
GROUP BY F.Descriptor

/* Select count of open Feature Requests */
/* For this example, using the Type field to designate “Bug”, “Feature Request”, or “Other” which have idRecord values of 4, 5 and 6 respectively. These values can be ascertained by querying the FLDTYPE table. */
/* Note: If the project is modified and the Type field is relabeled, this query will be querying against that new field (label) */

SELECT F.Descriptor,
count(D.idType)
FROM DEFECTS D,
FLDTYPE F,
STATES S
WHERE D.idType = F.idRecord
AND D.Status = S.StateOrder
AND S.Attrib = 1
AND F.idRecord = 5 — idRecord for a “Feature Request” type
GROUP BY F.Descriptor

/* Select count of records created, grouped by day */
/* Depending on the database, use the appropriate function to get just the date part of the dateCreate field */

SELECT date(dateCreate),
count(dateCreate)
FROM DEFECTS
GROUP BY date(dateCreate)

/* Select detail records of open issues where the Severity value is “Causes Crash” */
/* Based on the Severity field, change the value in the where clause for other detail lists */

SELECT D.DefectNum AS DefectNumer,
D.Summary AS Summary,
FP.Descriptor AS Priority,
FS.Descriptor AS Severity,
S.Name AS CurrentStatus
FROM DEFECTS D,
FLDSEVER FS,
STATES S,
FLDPRIOR FP
WHERE D.idSeverity = FS.idRecord
AND FS.Descriptor = “Causes Crash”
AND D.Status = S.idRecord
AND S.Attrib = 1
AND D.idPriority = FP.idRecord