Created On:  14 September 2012


With SCTM 12.0 the report "Manual Test Result Document" does not incorportate results from my Manual Execution Plan (test cycle) data.


A future release of SCTM will include updates to the reporting features of SCTM.  In the meantime the script below can be used to create the report " Manual Test Result Document" which will incorporate Test Cycles.

SELECT cycle.NodeID_pk AS CycleId, cycle.NodeName AS CycleName,

       etn.NodeID_pk AS ExecDefID, etn.NodeName AS ExecutionName,

       cycle.AvailableTimeInCycle AS TotalAvailableTimeInCycle,

       cycle.CycleStartDate, cycle.CycleEndDate,

       etn.ChangedBy, etn.ChangedAt, etn.ProjectID_fk AS ProjectID,

       tpn.NodeID_pk AS TestDefID, tpn.Name AS TestName, tde.TestDefPath,

       steps.Name AS StepName, steps.Description, steps.Result AS ExpectedResults,

       tde.Status, tde.StatusName,

       mtr.ChangedAt AS LastChangedAt, mtr.ChangedBy AS LastChangedBy,

       mtr.Status AS StepStatus, tdsn.StatusName AS StepStatusName, mtr.Result AS StepResultComment


FROM (SELECT ExecDefID_fk, MAX(ExecDefRunID_pk_fk) AS ExecDefRunId FROM TM_ExecDefinitionRuns

        GROUP BY ExecDefID_fk) AS lastRun

    INNER JOIN TM_ExecTreeNodes etn ON etn.NodeID_pk = lastRun.ExecDefID_fk

    INNER JOIN TM_ExecTreeNodes cycle ON cycle.NodeID_pk = etn.ParentFolderID_fk  

    INNER JOIN TM_V_TestDefExecutions tde ON lastRun.ExecDefRunId = tde.ExecDefRunID_fk   

    INNER JOIN TM_TestPlanNodes tpn ON tpn.NodeID_pk = tde.TestDefID_fk

    INNER JOIN TM_TestDefinitions td ON td.TestPlanNodeID_pk_fk = tpn.NodeID_pk

    LEFT JOIN TM_ManualSteps steps ON steps.TestAssetNodeID_fk = tpn.NodeID_pk

    LEFT JOIN TM_ManualTestStepResults mtr

           ON mtr.TestDefExecID_fk = tde.TestDefExecID_pk_fk AND mtr.StepID_fk = steps.StepID_pk

    LEFT JOIN TM_TestDefStatusNames tdsn ON tdsn.StatusID_pk = mtr.Status

  WHERE tpn.ProjectID_fk = ${$PROJECTID} AND TestType = '_ManualTestType'

    AND cycle.NodeID_pk =${$CYCLEID}

                 ORDER BY etn.NodeName;


Incident #2582001