Test result with status reason

[ratkals]

Hi,

Is there any way to get test result with status reason with the execution plan or folder level in the form of PDF or any report OR by using filter to know status reason assigned to test.

We want to know the status reason in case if the execution was not started to check whether any status reason is assigned or they have not started. Which is difficult to know if there are more than 100 users and 1000 script been run in weekly basis.

[Hubert Inreiter]

Hi ratkals,

I think the query will depend on how you define a "not started" execution. Maybe it also matters if you want to query automated or manual tests.

I suggest to look into the DataMart (http://documentation.microfocus.com/help/topic/com.microfocus.sctm.doc/GUID-4435C070-99AA-4EA7-B7BB-550A45C51D36.html?cp=9_0_2_5_3_0), there you can find views for querying run data.

Regards,

Hubert

[ratkals]

Hi Hubert,

Thank you for reply.

I looked into the DataMart tables. In our database we do not have any data.

Here is the query which i used to get the 'Test Status summary report' but this does not get status reason,

SELECT
parent.NodeName,
COUNT(DISTINCT tdeds.ExecTreeNodeID_pk_fk) ExecDefNumber,
COUNT(*) TestDefNumber,
SUM(tdeds.PassedCount) AggrPassedCount,
SUM(tdeds.FailedCount) AggrFailedCount,
SUM(tdeds.NotExecutedCount) AggrNotExecutedCount,
SUM(tdeds.PassedCount + tdeds.FailedCount + tdeds.NotExecutedCount) AggrOverallCount,
SUM(CASE WHEN tde.Duration > 0 THEN tde.Duration ELSE 0 END) LastDuration
FROM TM_TestDefExecDefStatuses tdeds
INNER JOIN TM_ExecTreePaths etp ON etp.NodeID_pk_fk = tdeds.ExecTreeNodeID_pk_fk
INNER JOIN TM_ExecTreeNodes parent ON parent.NodeID_pk = etp.ParentNodeID_pk_fk
INNER JOIN TM_V_ExecTreeNodes etn ON etn.NodeID_pk = tdeds.ExecTreeNodeID_pk_fk
INNER JOIN TM_TestDefExecutions tde on tde.TestDefExecID_pk_fk = tdeds.TestDefExecID_fk
WHERE etp.ParentNodeID_pk_fk = ${execNode_Id_0|1|ExecNodeID}
AND parent.ProjectID_fk = ${$PROJECTID}
AND etn.IsArchived = 0
AND etn.TestContainerID_fk IS NOT NULL
GROUP BY parent.NodeID_pk, parent.NodeName

There is a table 'TM_StatusReasons sr ON sr.StatusReasonID_PK=tde.StatusReasonID_FK' which can be used, but this does not provide type of status reason assigned for the Execution Plan.

Any idea is there any other way to get 'Status Reason' with test from folder level.

Regards,
Shruti

[Hubert Inreiter]

Hi Shruti,

[quote user="ratkals"]
I looked into the DataMart tables. In our database we do not have any data.
[/quote]

Which version of Silk Central are you on?

If you do not have any in the DataMart tables then it is either turned off or there is something not working as it should. If you check the output of this SQL then the output for column "LoadTo" should not be older than a few minutes (depending on your settings):

SELECT *
FROM DM_DataLoads

Regarding the SQL requested, based on your query I added an example how to also include the status reasons (please use the status reasons of your project):

SELECT
parent.NodeName,
COUNT(DISTINCT tdeds.ExecTreeNodeID_pk_fk) ExecDefNumber,
COUNT(*) TestDefNumber,
SUM(tdeds.PassedCount) AggrPassedCount,
SUM(tdeds.FailedCount) AggrFailedCount,
SUM(tdeds.NotExecutedCount) AggrNotExecutedCount,
SUM(tdeds.PassedCount + tdeds.FailedCount + tdeds.NotExecutedCount) AggrOverallCount,
SUM(CASE WHEN tde.Duration > 0 THEN tde.Duration ELSE 0 END) LastDuration,
SUM(CASE WHEN sr.ReasonText = 'Unsupported' THEN 1 ELSE 0 END) ReasonUnsupported,
SUM(CASE WHEN sr.ReasonText = 'Unresolved' THEN 1 ELSE 0 END) ReasonUnresolved,
SUM(CASE WHEN sr.ReasonText = 'System crashed' THEN 1 ELSE 0 END) ReasonSystemCrashed,
SUM(CASE WHEN sr.ReasonText IS NULL THEN 1 ELSE 0 END) NoReason
FROM TM_TestDefExecDefStatuses tdeds
INNER JOIN TM_ExecTreePaths etp ON etp.NodeID_pk_fk = tdeds.ExecTreeNodeID_pk_fk
INNER JOIN TM_ExecTreeNodes parent ON parent.NodeID_pk = etp.ParentNodeID_pk_fk
INNER JOIN TM_V_ExecTreeNodes etn ON etn.NodeID_pk = tdeds.ExecTreeNodeID_pk_fk
INNER JOIN TM_TestDefExecutions tde on tde.TestDefExecID_pk_fk = tdeds.TestDefExecID_fk
LEFT JOIN TM_StatusReasons sr ON sr.StatusReasonID_pk = tde.StatusReasonID_fk
WHERE etp.ParentNodeID_pk_fk = ${execNode_Id_0|1|ExecNodeID}
AND parent.ProjectID_fk = ${$PROJECTID}
AND etn.IsArchived = 0
AND etn.TestContainerID_fk IS NOT NULL
GROUP BY parent.NodeID_pk, parent.NodeName

Regards,

Hubert

[ratkals]

Hi Hubert,

Thank You. Query you provided helped me to proceed.

I have one more query. Its regarding Duration of execution in silk it shows as 'Used Time' as '00:03' .
In BIRT it has a function used to display in above format convertDate(dataSetRow["LASTRUNAT"]).
When i use it below error is received,
Table (id = 3765):
+ A BIRT exception occurred. See next exception for more information.
ReferenceError: "convertDate" is not defined.

Any idea what am i missing in initializing.

Regards,
Shruti

[Hubert Inreiter]

Hi Shruti,

Have you defined the function convertDate() yourself? If you take a Silk Central standard report as template you can find examples for the implementation and usage of convertDate() and getDuration() there.

Regards,
Hubert

[ratkals]

Hi Hubert,

No i have not defined convertDate() and getDuration() function.

I referred SCTM standard report.

Kindly let me know how can i define these functions.

Regards,
Shruti

[ratkals]

Hi Hubert,

In SCTM Standard report found function initialization in XML Source,

function getDuration(durationInS) {
if (durationInS >= 0) {
var durationHrs = Math.floor(durationInS / 3600);
var durationMin = Math.floor((durationInS - durationHrs * 3600) / 60);
var durationSec = Math.floor(durationInS - durationHrs * 3600 - durationMin * 60);
var hours = "" + durationHrs;
var minutes = "" + durationMin;
var seconds = "" + durationSec;
if (durationHrs < 10) hours = "0" + hours;
if (durationMin < 10) minutes = "0" + minutes;
if (durationSec < 10) seconds = "0" + seconds;
return hours + ":" + minutes + ":" + seconds;
}
else return "-";
}

function convertDate(dateTime) {
// date is stored as gmt - convert to user time zone
if (dateTime == null) {
return null;
}
var dateFormatter = new java.text.SimpleDateFormat(params["currShortDateFormatter"]);
var dbDate = new Packages.java.util.Date(dateTime);
var dbTime = dbDate.getTime();
var timeZoneEntity = Packages.java.util.TimeZone.getTimeZone(params["currUserTimeZone"]);
var correctedTime = dbTime + timeZoneEntity.getOffset(dbTime);
return dateFormatter.format(correctedTime);

Is this the one where initialization will be done ?

I imported and tried, now i am receiving below error,

ReportDesign (id = 1):
+ An exception occurred during processing. Please see the following message for details:
Binding "LASTRUNAT" has dependency cycle.

Kindly let me know if i have missed out anything.

Regards,
Shruti

[Hubert Inreiter]

Hi Shruti,

You should see the scripts in BIRT report designer if you select the "Outline" view and expand the "Scripts" folder.

Is your BIRT report based on a Silk Central report?

Could you upload your template?

Regards,
Hubert