Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

How can I write an SQL query that will return the Requirements Status Overview from all active projects in SilkCentral TestManager?

How can I write an SQL query that will return the Requirements Status Overview from all active projects in SilkCentral TestManager?

Problem:

How can I write an SQL query that will return the Requirements Status Overview from all active projects in SilkCentral TestManager?

Resolution:

QUESTION
----------------------------------
How can I write an SQL query that will return the Requirements Status Overview from all active projects in SilkCentral TestManager?

ANSWER
----------------------------------
The following is an SQL example of how to retrieve the Requirements Status Overview from all the active projects in SilkCentral TestManager.

SELECT rcs.ReqCoverageName AS "Coverage Status",
              Count(rc.ReqTreeNodeID_pk_fk) AS "No. of Requirements"
              FROM TM_ReqCoverageNames rcs LEFT JOIN
              (SELECT r.ReqTreeNodeID_pk_fk, req.Priority,
              MAX(CASE when td.CoverageStatus is null then 4
              else td.CoverageStatus end) AS CoverageStatus
              FROM TM_RequirementsCoverages r
              INNER JOIN TM_RequirementTreeNodes req
              ON (req.ReqTreeNodeID_pk = r.ReqTreeNodeID_pk_fk)
              LEFT JOIN
              (SELECT tds.TestPlanNodeID_pk_fk AS TestID,
              CASE when tds.CurrentExecStatus > 3 then 3
              else tds.CurrentExecStatus end AS CoverageStatus
              FROM TM_TestDefinitions tds) td ON (r.TestPlanNodeID_pk_fk = td.TestID)
              WHERE req.MarkedForDeletion = 0               
              GROUP BY r.ReqTreeNodeID_pk_fk, req.Priority) rc
              ON (rc.CoverageStatus = rcs.ReqCoverageID_pk)
              GROUP BY rcs.ReqCoverageID_pk, rcs.ReqCoverageName
              ORDER BY rcs.ReqCoverageID_pk


The original can be found in SilkCentral by going to

1. TestManager > Reports
2. Select any project
3. Select Requirements Coverage
4. Select Requirements Status Overview
5. Select the Properties tab
6. Select Edit

If you scroll down the SQL statement you will find that the only difference between that statement and this is that the PROJECT_ID statement has been removed.

Old KB# 24544

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2013-02-15 19:46
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.