Requirements Covered in Tests Report, Where the Tests are Filtered

The default test coverage number on a requirement is COVERAGE_COUNT_TOTAL:

 https://spiradoc.inflectra.com/Reporting/Custom-Report-Tables/#:~:text=COVERAGE_COUNT_TOTAL

This number is Test Case Status agnostic - it doesn't care if a requirement is covered in obsolete-status tests, tests in a pending status, tests that have some custom property that qualifies them, or whatever. Personally I think there should be a mechanism to identify which states in the state engine are valid, but in the absence of a built-in feature we can work it out in the graph feature's raw sql code (and hopefully call the graph from the API later to get these numbers pushed to a remote target at intervals)

If you want to express test coverage of a requirement as a percentage, it is 

(x/y)*100 = % coverage where

x = # of valid requirements covered in valid tests

y = # of requirements that meet whatever requirement criteria you are using (such as a type) that represents the 'total valid requirements possible'

In order to get Y (the easy part), you can run a query like:

--Count of traceable requirements
SELECT COUNT(Req.REQUIREMENT_ID) AS TotalReqs
FROM SpiraTestEntities.R_Requirements AS Req
WHERE Req.PROJECT_ID = --your project ID
    AND Req.REQUIREMENT_TYPE_NAME = 'Feature' --an example of a filter on the total requirements
    AND Req.IS_DELETED = FALSE
    AND CAST(Req.CUST_17 AS INT32)  = 0000000333 --another example of a filter on the total requirements

In order to get X, you join across the junction table to test cases, but be careful to make sure you count DISTINCT requirement ids, otherwise you get a count of every row in the table (requirements multiplied by covering test cases).

--count of valid covered traceable requirements
SELECT COUNT(DISTINCT RQ.REQUIREMENT_ID) AS qualifiedReqs
FROM SpiraTestEntities.R_Requirements AS RQ
LEFT JOIN SpiraTestEntities.R_RequirementTestCases as RT on RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID
LEFT JOIN SpiraTestEntities.R_TestCases as TC on RT.TEST_CASE_ID = TC.TEST_CASE_ID
where RQ.PROJECT_ID = --your project ID
AND RQ.REQUIREMENT_TYPE_NAME = 'Feature' --same filter demo as Y query above
AND RQ.IS_DELETED = FALSE
AND TC.TEST_CASE_STATUS_NAME = "Approved" --new tighter TC filter example to make X a fraction(a percentage) of Y
AND TC.IS_DELETED = FALSE
AND CAST(RQ.CUST_17 AS INT32)  = 0000000333 --same second filter demo as Y query above

After you have X and Y, you can build a composite query to sort the percentage:

SELECT X.TotalReqs
, Y.qualifiedReqs AS CoveredReqs
--In the below logic, you have to multiply the values by a decimal to hint that you're looking for decimal division.
, ROUND((Y.qualifiedReqs * 1.0) / (X.TotalReqs * 1.0), 2) * 100 AS PercentReqsCovered
FROM
--subquery is just copy of X query example above
(--Count of traceable requirements
    SELECT COUNT(Req.REQUIREMENT_ID) AS TotalReqs
    FROM SpiraTestEntities.R_Requirements AS Req
    WHERE Req.PROJECT_ID = --your project ID
        AND Req.REQUIREMENT_TYPE_NAME = 'Feature'
        AND Req.IS_DELETED = FALSE
        AND CAST(Req.CUST_17 AS INT32)  = 0000000333
) AS X
JOIN 
--subquery is just copy of y query example above
(--count of valid covered traceable requirements
    SELECT COUNT(DISTINCT RQ.REQUIREMENT_ID) AS qualifiedReqs
    FROM SpiraTestEntities.R_Requirements AS RQ
        LEFT JOIN SpiraTestEntities.R_RequirementTestCases AS RT ON RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID
        LEFT JOIN SpiraTestEntities.R_TestCases AS TC ON RT.TEST_CASE_ID = TC.TEST_CASE_ID
    WHERE RQ.PROJECT_ID = --your project ID
        AND RQ.REQUIREMENT_TYPE_NAME = 'Feature'
        AND RQ.IS_DELETED = FALSE
        AND TC.TEST_CASE_STATUS_NAME = "Approved"
        AND TC.IS_DELETED = FALSE
        AND CAST(RQ.CUST_17 AS INT32)  = 0000000333
) AS Y ON TRUE=TRUE --just table join nonsense need to hook the tables together

This article is based on the forum post by our user Frank.