generate a report that shows the total time spent testing a configuration...
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.
configuration name | product | duration
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,
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>
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)
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!
"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