Absent Member.
Absent Member.
1012 views

need help generating a report showing workload for a testers in a project

Hi, is there a silk central report (built in or developed) that shows the workload of testers? 

includes the count of number of  tests assigned to them (could include the pass \ failed \ NA of those tests).

I would likw to have the report generated that shows total tests for all testers in a project:

 

eg

tester count

bob    120

sue    130

joe     4

 

another report could have the number of times they have executed plans in a project:

 

e.g. 

execution plan tester count

execplan1        bob      10

execplan1        sue       5

execplan2        bob       6

 

are there any reports similar in silk central that can be used or is there some SQL that can be used or developed?

thanks for having a look!

 

James

 

 

 

 

0 Likes
8 Replies
Micro Focus Expert
Micro Focus Expert

Hi James,


Are you working with Testing Cycles or plain execution plans?

Are you looking for planned, already running, or finished runs?


Regards,
Hubert
0 Likes
Absent Member.
Absent Member.

Hi Hubert,

thanks for looking into this.

I am working with testing cycles at the top-level,
a good example would be in the 'test assignments' section where it shows the test cycles and than the users underneath with their assigned tests in the right hand pane, what would be a good is to just have a a roll up of the totals for all test cycles in the project for each tester.
so an amendment to my above example would include the test cycle...

test cycle | tester | total count of tests
testcycle1 - joeb - 20
testcycle2 - joeb - 10

I am hoping to capture how many tests a tester has been assigned across all test cycles, but looking at this I see that another report that would be useful is how many test cycles a tester is in as well from a project perspective,

project | tester | total count of test cycles
prj1 - joeb - 20
prj2 - marys - 10

maybe the above reports would be an extension of the dashboard reports 'testing cycle summary' and 'manual tests assigned to me', but at the 10,000 foot level and including totals only.

I can see using planned, already running, and finished runs as being separate reports as well:

tester | planned | already running | finished
joeb - 10 - 4 -2

I am generalizing and might be missing other required info please fell free to correct, add and update where needed based on what can be pulled or maybe I am way off base in what can be reported on?

does that help clear up what I am looking for or did I just confuse it more 🙂

thanks for your help Hubert!

James
0 Likes
Micro Focus Expert
Micro Focus Expert

Hi James,

Here are 2 queries which could help:

 

1. Tester per Cycle (only run or running) - if you want only the data from running or finished testing cycles then it is rather easy as all the required data is already in the runs:

 

SELECT tcs.TesterLogin, etn.NodeName TestingCycleName,

  CASE WHEN TestingCycleState = 2 THEN 'Running' ELSE 'Finished' END TestingCycleState,

  SUM(tcs.PassedCount) PassedCount, SUM(tcs.FailedCount) FailedCount, SUM(tcs.NotExecutedCount) NotExecutedCount

FROM RV_TestingCycleStatus tcs

INNER JOIN TM_ExecTreeNodes etn ON tcs.TestingCycleID = etn.NodeID_pk

WHERE ProjectID = ${$PROJECTID} AND tcs.TesterLogin IS NOT NULL

GROUP BY TesterLogin, etn.NodeName, TestingCycleState

ORDER BY TesterLogin, etn.NodeName

 

 

2. Tester ID per Cycle (all testing cycle status) - if you want also the not yet started testing cycles then the query has to get the data differently:

 

SELECT enu.UserID_pk_fk TesterID, tc.NodeName TestingCycleName,

 CASE WHEN tc.TestingCycleState = 1 THEN 'Planned'

   WHEN tc.TestingCycleState = 2 THEN 'Running'

   ELSE 'Finished' END TestingCycleState,

 COUNT(edtd.TestDefID_pk_fk) NrOfTests

FROM TM_ExecNodes_Users enu

INNER JOIN TM_ExecDefs_TestDefs edtd ON enu.ExecNodeID_pk_fk = edtd.ExecDefID_pk_fk

INNER JOIN TM_ExecTreeNodes tcChild ON enu.ExecNodeID_pk_fk = tcChild.NodeID_pk

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

WHERE tc.TestingCycleState IS NOT NULL

 AND  tc.ProjectID_fk = ${$PROJECTID}

GROUP BY enu.UserID_pk_fk, tc.NodeName, tc.TestingCycleState

ORDER BY enu.UserID_pk_fk, tc.NodeName

 

Hope that helps,

Hubert

0 Likes
Absent Member.
Absent Member.

thanks Hubert for helping out!
The column name 'TestingCycleName' is coming back as invalid, sorry I should have let you know that I am working on SC 17 with HF 4 installed (we are not upgrading until jan.). I checked the schema and I do not see the column name 'TestingCycleName', is there another column name it is going by or in another table for SC 17?

James
0 Likes
Micro Focus Expert
Micro Focus Expert

Hi James,

You are right, the TestingCycleState column was added in SC 18.0. Quick fix for an older database is to just remove all references to the column from the queries. If you need the testing cycle state then the queries get a bit more complex:

--1

SELECT tcs.TesterLogin, etn.NodeName TestingCycleName, cycleStates.CycleState,

SUM(tcs.PassedCount) PassedCount, SUM(tcs.FailedCount) FailedCount, SUM(tcs.NotExecutedCount) NotExecutedCount

FROM RV_TestingCycleStatus tcs

INNER JOIN TM_ExecTreeNodes etn ON tcs.TestingCycleID = etn.NodeID_pk

INNER JOIN (

SELECT NodeID_pk CycleID,

   CASE WHEN MaxStatus = 0 THEN 'Planned' WHEN MaxStatus = 7 THEN 'Running' ELSE 'Finished' END CycleState

 FROM (

   SELECT cycle.NodeID_pk, MAX(COALESCE(edr.Status, 0)) MaxStatus

   FROM TM_ExecTreeNodes cycle

   LEFT JOIN TM_ExecTreeNodes cycleChild ON cycleChild.ParentFolderID_fk = cycle.NodeID_pk

   LEFT JOIN TM_ExecDefinitionRuns edr ON edr.ExecDefID_fk = cycleChild.NodeID_pk

   WHERE cycle.AvailableTimeInCycle IS NOT NULL AND cycle.IsArchived = 0

     AND (cycleChild.NodeID_pk IS NULL OR cycleChild.IsArchived = 0)

     AND edr.ExecServerID_fk IS NULL AND edr.ExecServerName IS NULL

   GROUP BY cycle.NodeID_pk

 ) statusMapping

) cycleStates ON tcs.TestingCycleID = cycleStates.CycleID

WHERE ProjectID = ${$PROJECTID} AND tcs.TesterLogin IS NOT NULL

GROUP BY TesterLogin, etn.NodeName, cycleStates.CycleState

ORDER BY TesterLogin, etn.NodeName

--2

SELECT enu.UserID_pk_fk TesterID, tc.NodeName TestingCycleName, cycleStates.CycleState,

 COUNT(edtd.TestDefID_pk_fk) NrOfTests

FROM TM_ExecNodes_Users enu

INNER JOIN TM_ExecDefs_TestDefs edtd ON enu.ExecNodeID_pk_fk = edtd.ExecDefID_pk_fk

INNER JOIN TM_ExecTreeNodes tcChild ON enu.ExecNodeID_pk_fk = tcChild.NodeID_pk

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

INNER JOIN (

SELECT NodeID_pk CycleID,

   CASE WHEN MaxStatus = 0 THEN 'Planned' WHEN MaxStatus = 7 THEN 'Running' ELSE 'Finished' END CycleState

 FROM (

   SELECT cycle.NodeID_pk, MAX(COALESCE(edr.Status, 0)) MaxStatus

   FROM TM_ExecTreeNodes cycle

   LEFT JOIN TM_ExecTreeNodes cycleChild ON cycleChild.ParentFolderID_fk = cycle.NodeID_pk

   LEFT JOIN TM_ExecDefinitionRuns edr ON edr.ExecDefID_fk = cycleChild.NodeID_pk

   WHERE cycle.AvailableTimeInCycle IS NOT NULL AND cycle.IsArchived = 0

     AND (cycleChild.NodeID_pk IS NULL OR cycleChild.IsArchived = 0)

     AND edr.ExecServerID_fk IS NULL AND edr.ExecServerName IS NULL

   GROUP BY cycle.NodeID_pk

 ) statusMapping

) cycleStates ON tc.NodeID_pk = cycleStates.CycleID

WHERE tc.ProjectID_fk = ${$PROJECTID}

GROUP BY enu.UserID_pk_fk, tc.NodeName, cycleStates.CycleState

ORDER BY enu.UserID_pk_fk, tc.NodeName

 

Regards,

Hubert

0 Likes
Absent Member.
Absent Member.

thanks Hubert very helpful!

one question, is it possible to get user names where the tester ids are being used for the 2nd sql report?
0 Likes
Micro Focus Expert
Micro Focus Expert

Hi James,

Silk Central is a bit restrictive in regards of user data. Out of the box, it is not possible to report directly on the user table from within Silk Central.

In your case this is no problem for data about running or finished tests, as there at least the login name for a user is already available in the run data itself. But it is a limitation for planned but not started tests as there only a reference (ID) to the user is stored.

If you want to enable reporting on user data too, then it is necessary to add a view to the database. For example:

CREATE VIEW V_Users

AS

SELECT UserID_pk, Login, FirstName, LastName

FROM SCC_Users

 

Add this join to the query:

INNER JOIN V_Users userDetails ON enu.UserID_pk_fk = userDetails.UserID_pk

 

Then it is possible to select login and name for a user too.

 

Regards,

Hubert

0 Likes
Absent Member.
Absent Member.

thanks Hubert...appreciate all the help once again!
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.