Created On:  14 May 2012

Problem:

We would like to create a stock report template that we can use in SCTM to report on all tests within a Test Container showing basic test information of test name, description and status.

Resolution:

Attached to this article is a basic report template that reports on all tests within a specified Test Container.  The report displays the projectID, parentID (of the test), testID, testName, testDescription and the tests current status as data columns.

The query used by the birt report is as follows:

SELECT    *
FROM         TM_TestDefStatusNames INNER JOIN
                      TM_V_TestDefinitions ON TM_TestDefStatusNames.StatusID_pk = TM_V_TestDefinitions.CurrentExecStatus INNER JOIN
                      LQM_V_Tests ON TM_V_TestDefinitions.TestID = LQM_V_Tests.TestID_pk
WHERE     TM_V_TestDefinitions.TestContainerID = ?


To allow SCTM to pass a parameter to the Birt report, you need to create a new report in SCTM that contains the following query in the Advanced Query option:

SELECT    *
FROM         TM_TestDefStatusNames INNER JOIN
                      TM_V_TestDefinitions ON TM_TestDefStatusNames.StatusID_pk = TM_V_TestDefinitions.CurrentExecStatus INNER JOIN
                      LQM_V_Tests ON TM_V_TestDefinitions.TestID = LQM_V_Tests.TestID_pk
                      WHERE dbo.TM_V_TestDefinitions.TestContainerID = ${TestContainerID|1}


The parameter TestContainerID will then be passed from SCTM to the Birt report.

Attached is an example Birt report template.  In order to use this report template for your SCTM installation, you will need to make the following updates:

1) Open the report in BIRT RCP Report Designer (downloadable from SCTM | Help | Tools)
2) From the Outline pane, expand Data Sources, and double-click Data Source to open the Edit Data Source dialog.
3) Specify net.sourceforge.jtds.jdbc.Driver (v1.2) for the Driver Class
4) Specify jdbc:jtds:sqlserver:///;instance= for the Driver URL
    
     Note: replace with your SCTM server hostname, with your SCTM database name,
     and with your SQL server instance name.

5) Specify the database username and password
6) Click the Test Connection button to confirm a successful connection and click OK to close the Edit Data Source dialog.
7) From the Outline pane, expand Body | Table | Detail | Row
8) Click once on the 4th cell, then double-click the selected string in the report layout to open the Expression Builder
9) In the Expression Builder, edit the existing string replacing with your SCTM server hostname.
10) Click OK to save changes and close the Expression Designer.  Save all changes and close the report.

You can now upload the report in SCTM as a new report template.  See the help topic Uploading Report Templates in the SilkCentral Administration Module Help.


Tests Overview Report showing details of all tests within a specified Test Container ID