Is there a way within SilkCentral Test Manager to determine how many projects that were tested against through the year?  A SilkCentral Test Manager database could have 29 projects for example, all of which were not tested against in a given year, therefore, is it possible to find out which ones were actively tested againt for a given year?

It is possible to return this information using an SQL query that can be run against the SilkCentral Test Manager database.  Below are example queries that demonstrate the type of information we can return from the SilkCentral Test Manager database on projects, execution definitions and test definitions that were actively tested against in a given year.

In order to run the queries below, please either use a SQL Server Management Studio installation or use the Reports section of Test Manager.  To use the Reports Section of Test Manager:

1.  Create a new report
2.  In the 'Create New Report' dialog, select the 'Advanced Query' button
3.  Then copy and paste the required query into the editor and click the 'Check SQL' button to compile the query
4.  When the compilation is complete, click the 'Finish' button.  You can then view the results of the query in the 'Data' tab of the selected report.


Example SQL Queries to get actively tested items of a SilkCentral Test Manager databse:

Get Test Definitions that were executed in a given year:

SELECT     SCC_Projects.ProjectID_pk AS [PROJECT ID], SCC_Projects.ProjectName AS [PROJECT NAME], LQM_Tests.TestID_pk AS [TEST ID],
                      LQM_Tests.Name AS [TEST DEFINITION NAME], YEAR(TM_TestDefinitions.LastExecutedAt) AS [YEAR EXECUTED]
FROM         LQM_Tests INNER JOIN
                      SCC_Projects ON LQM_Tests.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
                      TM_TestDefinitions ON LQM_Tests.TestID_pk = TM_TestDefinitions.TestPlanNodeID_pk_fk
WHERE     (YEAR(TM_TestDefinitions.LastExecutedAt) = 2010)


Get Projects that had a Test Definition executed in a given year:

SELECT     SCC_Projects.ProjectID_pk AS [PROJECT ID], SCC_Projects.ProjectName AS [PROJECT NAME]
FROM         LQM_Tests INNER JOIN
                      SCC_Projects ON LQM_Tests.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
                      TM_TestDefinitions ON LQM_Tests.TestID_pk = TM_TestDefinitions.TestPlanNodeID_pk_fk
WHERE     (YEAR(TM_TestDefinitions.LastExecutedAt) = 2010)
GROUP BY SCC_Projects.ProjectID_pk, SCC_Projects.ProjectName


Get Execution Definitions that were executed in a given year:

SELECT     SCC_Projects.ProjectID_pk AS [PROJECT ID], SCC_Projects.ProjectName AS [PROJECT NAME], LQM_Tests.TestID_pk AS [TEST ID],
                      LQM_Tests.Name AS [TEST DEFINITION NAME], TM_ExecDefs_TestDefs.ExecDefID_pk_fk AS [EXECUTION DEFINITION ID],
                      TM_ExecTreeNodes.NodeName AS [EXECUTION DEFINITION NAME], YEAR(TM_ExecTreeNodes.LastRunAt) AS [YEAR EXECUTED]
FROM         LQM_Tests INNER JOIN
                      SCC_Projects ON LQM_Tests.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
                      TM_ExecDefs_TestDefs ON LQM_Tests.TestID_pk = TM_ExecDefs_TestDefs.TestDefID_pk_fk INNER JOIN
                      TM_ExecTreeNodes ON TM_ExecDefs_TestDefs.ExecDefID_pk_fk = TM_ExecTreeNodes.NodeID_pk
WHERE     (YEAR(TM_ExecTreeNodes.LastRunAt) = 2010)


Get Projects that had an execution definition executed in a given year:

SELECT     SCC_Projects.ProjectID_pk AS [PROJECT ID], SCC_Projects.ProjectName AS [PROJECT NAME]
FROM         LQM_Tests INNER JOIN
                      SCC_Projects ON LQM_Tests.ProjectID_fk = SCC_Projects.ProjectID_pk INNER JOIN
                      TM_ExecDefs_TestDefs ON LQM_Tests.TestID_pk = TM_ExecDefs_TestDefs.TestDefID_pk_fk INNER JOIN
                      TM_ExecTreeNodes ON TM_ExecDefs_TestDefs.ExecDefID_pk_fk = TM_ExecTreeNodes.NodeID_pk
WHERE     (YEAR(TM_ExecTreeNodes.LastRunAt) = 2010)
GROUP BY SCC_Projects.ProjectID_pk, SCC_Projects.ProjectName


NB: The above queries were created against a SilkCentral Test Manager 2010 SQL Server database, therefore, if using a previous version of SCTM, please review the Test Manager Database Model for changes to the database schema that may affect the query.