AxoSoft OnTime – Metrics – Defects by Severity

By XeroCube | July 18, 2013

I have recently been introduced to a new tool, Axosoft‘s OnTime. For what it is, it works well as an Agile project management tool, complete with defect and incident tracking. From a quality assurance perspective, it’s nice to have flexibility of workflow management, custom fields and such. What I have found a bit lacking, however, is consistent metrics. I’m prone to create my own metrics reporting, so I have a tendency of querying databases directly.

In OnTime, the data structure is fairly straightforward. However, our organizational structure within the tool’s concepts of Releases and Projects is hierarchal in nature and makes my queries a tad more complex.

First, I had to decide to base my metrics off or projects or releases. I selected Releases, since we tend to have more projects in a single release.

Second, I had to determine how to traverse through a particular release and all sub-releases. For this, I created a temporary table to set up the hierarchy of releases. As an added bonus, I got to delve into a bit of recursion. In this temporary table, you must determine the ReleaseID of your parent release and use that as your anchor. The commented line shows you where to enter that value.

Third, it was a matter of constructing my select statement. You will notice several left joins, as many of the fields we track aren’t required. Later, in the where clause is where you can filter your query based on the data you want to see.

Lastly, I needed to generate a PIVOT to allow me to view the Severity values as individual columns. You will need to ensure that the values in your Severity query match the values in your table.

This will give you a snapshot count of Defects by Severity.

I have found this very useful in communicating a bunch of useful data in a simple to consume chart. You can also take this a step further to track your snapshots over a time period for trend analysis.

Here is the query:

WITH AllReleases AS
    SELECT ParentRelease.ReleaseId, ParentRelease.Name
    FROM Releases AS ParentRelease
    WHERE ParentRelease.ReleaseId = 1 — This is the Parent Release ID
    SELECT SubRelease.ReleaseId, SubRelease.Name
    FROM Releases AS SubRelease
        INNER JOIN AllReleases AS AR
            ON SubRelease.ParentReleaseId = AR.ReleaseID
    ‘Release Name’ AS Release,
    [High Impact],
    [Medium Impact],
    [Low Impact],
    [No Impact]
          Sev.Name AS “Severity”
            Defects AS D
                LEFT JOIN Projects AS P
                    ON D.ProjectId = P.ProjectId
                LEFT JOIN StatusTypes AS S
                    ON D.StatusTypeId = S.StatusTypeId
                LEFT JOIN SeverityTypes AS Sev
                    ON D.SeverityTypeId = Sev.SeverityTypeId
                LEFT JOIN PriorityTypes AS Pri
                    ON D.PriorityTypeId = Pri.PriorityTypeId
                LEFT JOIN WorkflowSteps AS WF
                    ON D.WorkflowStepId = WF.WorkflowStepId
                LEFT JOIN Releases AS R
                    ON D.ReleaseId = R.ReleaseId
                LEFT JOIN DefectCustomFields AS DCF
                    ON D.DefectId = DCF.DefectId   
        WHERE D.ReleaseId IN
                SELECT ReleaseId FROM AllReleases
                    WHERE AllReleases.Name NOT LIKE ‘%backlog%’ — Filter the releases by name.
        ) AS tbl
            FOR “SEVERITY” IN (Critical, [High Impact], [Low Impact], [Medium Impact], [No Impact] )
        ) AS pvt

Here are the results from this query (both Raw Data and fed through an Excel graph).

To learn more about OnTime, take a look at their website:

Topics: Code, Quality Assurance, SQL | No Comments »