generate a report that shows the total time spent testing a configuration...

[JamesA99]

Hi, is there a way to generate a report that shows the total time spent testing a configuration?

It would be similar to the execution status overview report, but what I would like is to have the report showing the total duration of testing for each configuration (a device).

grouping would be by configuration.

e.g.

test cycle

   configuration name | product | duration 

thanks!

James

[Hubert Inreiter]

Hi James,

The below SQL (assuming you use MSSql) takes the ID of the testing cycle as parameter and should return the duration:

SELECT tc.NodeName TestingCycle, conf.NodeName ConfigurationName, p.ProductCode Product,  CONVERT(varchar, DATEADD(ms, conf.LastDuration, 0), 114) DurationFormatted,

conf.LastDuration/1000 DurationInSeconds

FROM TM_ExecTreeNodes tc

INNER JOIN TM_ExecTreeNodes conf ON tc.NodeID_pk = conf.ParentFolderID_fk

INNER JOIN SCC_Versions v ON conf.VersionID_fk = v.VersionID_pk

INNER JOIN SCC_Products p ON v.ProductID_fk = p.ProductID_pk

WHERE tc.NodeID_pk = <TESTING_CYCLE_ID>

Regards,

Hubert

[JamesA99]

Hi Hubert, thanks, your help, it is very much appreciated.
thank you for the sql this helped me clarify what I want reported on.
we want our testers to be entering their planned time, but also ensuring that the 'used time' is being captured.

I would like to capture the planned time and the used time in the report, although duration would be useful as it would show how long the execution plan has been started(is this correct?)
is this possible to report on ?

or does the duration represent the 'used time'? (sorry...help files are not really clear on this)


James

[JamesA99]

Hi Hubert, I attached a jpg for visual reference of the test cycle properties, I added the 2 metrics I would like included in the report.

I want to try and capture the total planned time (of all TC's ) in the configuration and the total used time.

report would be similar to this:

test cycle name | configuration name | planned time | used time | duration

is this workable?

if you have any other suggestion for capturing these metrics in one report I am all ears...thanks!

[Hubert Inreiter]

Hi James,

"Used time" vs. "duration":

- Used time: You can track the used time in the Manual Testing window. This is stored for every test run.

- Duration: If there are no times set for all the tests within one configuration then the time between start and finish is set as duration. As soon as there is a used time tracked for one of the tests then the sum of all the used times of the tests is reported as duration. The duration is calculated when the testing cycle was finished.

"Planned time" vs. "capacity":

- Planned time: Sum of the planned times of tests

- Capacity: The sum of the capacities set per user when planning a testing cycle

This SQL should show the data:

SELECT cycle.NodeName TestingCycle, conf.NodeName Configuration,

CAST(cycle.AvailableTimeInCycle / 60 AS Varchar) + ':' +  CAST(cycle.AvailableTimeInCycle % (60) AS Varchar) "Capacity Cycle (hh:mm)",

CAST(pt.PlannedTime / 60 AS Varchar) + ':' +  CAST(pt.PlannedTime % (60) AS Varchar) "Planned Time Tests (hh:mm)",

CAST(pt.UsedTime/60 / 60 AS Varchar) + ':' +  CAST(pt.UsedTime/60 % (60) AS Varchar) "Used Time - Tests (hh:mm)",

CAST(edr.Duration/60000 / 60 AS Varchar) + ':' +  CAST(edr.Duration/60000 % (60) AS Varchar) "Duration - Configuration (hh:mm)"

FROM TM_ExecTreeNodes cycle

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

INNER JOIN TM_ExecDefinitionRuns edr ON conf.NodeID_pk = edr.ExecDefID_fk

INNER JOIN (

 SELECT SUM(mtr.PlannedTime) PlannedTime, SUM(UsedTime) UsedTime, tde.ExecDefRunID_fk

 FROM TM_TestDefExecutions tde

 INNER JOIN TM_ManualTestResults mtr ON tde.TestDefExecID_pk_fk = mtr.TestDefExecID_pk_fk

 GROUP BY tde.ExecDefRunID_fk

) pt ON edr.ExecDefRunID_pk_fk = pt.ExecDefRunID_fk

WHERE cycle.NodeID_pk = 2461 -- << your testing cycle ID here

ORDER BY TestingCycle, Configuration

Regards,

Hubert