Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Reqs Covered in Tests, wh...
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
Thanks Frank!
Hi Frank
Would it be OK to publish as a KB article so we can increase its visibility.
Regards
David
Sure David, go for it.
Thanks. Here it is: https://www.inflectra.com/Support/KnowledgeBase/KB787.aspx
And if you have any questions, please email or call us at +1 (202) 558-6885