How do I generate a Test Definition Status report that includes the percentage?



How do I generate a Test Definition Status report that includes the percentage?


Currently in SilkCentral Test Manager there is a sample report that gives the Test Definition Status Overview totals;-
SCTM | Reports | Test Plan | Status Report | Test Definition Status Overview

This report does not give a value for the percentage. To get this you can follow the steps below:-

1. Create a new Report – In reports section, Right Click | New Report and give the new report an appropriate name. Eg - Test Definition Status Overview (With Percentage)

2. Click on the New Report name and choose Edit:-

3. Paste in the following SQL query in the SQL section.

SELECT sn.StatusName AS Status, COUNT(td.TestID) AS [No. of Test Definitions],

(round((100/(SELECT CAST(count(TestID)AS float)

dbo.RTM_V_TestDefinitions AS tds
where ProjectID = ${$PROJECTID}))* COUNT(td.TestID),2)) as "Percentage"

FROM (SELECT StatusID_pk, StatusName
FROM dbo.TM_TestDefStatusNames AS tdsn
WHERE (StatusID_pk
(SELECT TestID, CASE WHEN tds.CurrentExecStatus = 8 THEN 1 ELSE CASE WHEN tds.CurrentExecStatus = 9 THEN 2 ELSE tds.CurrentExecStatus END END AS StatusId FROM dbo.RTM_V_TestDefinitions AS tds WHERE (ProjectID = ${$PROJECTID})) AS td ON td.StatusId = sn.StatusID_pk
GROUP BY sn.StatusID_pk, sn.StatusName
ORDER BY sn.StatusID_pk

This Query selects the same fields as in the Test Definition Status Overview report, but also includes the percentage. The percentage is accurate to 2 decimal places.

4. Confirm that the SQL is correct my clicking on “Check SQL” and choose Finish.

5. Run the new report and it should bring back the required information (See below):-

Old KB# 25178
Comment List
Related Discussions