locked
Get valid history for TFS Test case Outcome field after execution. RRS feed

  • Question

  • Dear Friends,

    Recently I was looking into the TFS Test case execution results. Some of the result were modified and we had to get into Run Explorer to verify who has done the changes.

    Now from run explorer we may get the details of who has run the test case however to get the track of test case results and what was the last value of the execution is bit tricky. So I thought of creating a SSRS report which will hold the value of all the test case results, execution date, who ran it. I saw the table in Warehouse DB which stores the information and thought it would be a pretty simply report to create. I wrote a sample query to check the data.

    select TestCaseId, ResultDate, ResultOutcome from [dbo].[TestResultView]
    where TestCaseId = '154169'
    order by ResultDate

    I got 12 results for it.

    TestCaseId	ResultDate	ResultOutcome
    154169	2016-08-25 14:16:34.257	Not applicable
    154169	2016-08-25 16:09:31.477	Never Run
    154169	2016-09-01 11:32:34.190	Passed
    154169	2016-09-01 11:32:34.190	Never Run
    154169	2016-09-01 11:32:40.997	Passed
    154169	2016-09-01 11:32:40.997	Not applicable
    154169	2016-09-01 11:32:41.923	Passed
    154169	2016-09-01 11:32:41.923	Passed
    154169	2016-09-01 11:32:47.010	Passed
    154169	2016-09-01 11:32:47.010	Passed
    154169	2017-05-09 16:16:39.923	Passed
    154169	2017-05-09 16:16:39.923	Passed
    

    For getting the name who ran them I will use left join with [dbo].[DimPerson] on personsk and Resultexecutedbysk.

    All was good. However to test the correctness of report I made one  test case failed with an other user ID. I has a thought the 12 row result will become 13 row after changing a test cases outcome from passed to failed.

    However it added 6 records !!!!!

    TestCaseId	ResultDate	ResultOutcome
    154169	2016-08-25 14:16:34.257	Not applicable
    154169	2016-08-25 16:09:31.477	Never Run
    154169	2016-09-01 11:32:34.190	Passed
    154169	2016-09-01 11:32:34.190	Never Run
    154169	2016-09-01 11:32:40.997	Passed
    154169	2016-09-01 11:32:40.997	Not applicable
    154169	2016-09-01 11:32:41.923	Passed
    154169	2016-09-01 11:32:41.923	Passed
    154169	2016-09-01 11:32:47.010	Passed
    154169	2016-09-01 11:32:47.010	Passed
    154169	2017-05-09 16:16:39.923	Passed
    154169	2017-05-09 16:16:39.923	Passed
    154169	2017-06-05 14:35:06.497	Failed
    154169	2017-06-05 14:35:06.497	Passed
    154169	2017-06-05 14:35:06.903	Failed
    154169	2017-06-05 14:35:06.903	Passed
    154169	2017-06-05 14:35:07.090	Failed
    154169	2017-06-05 14:35:07.090	Failed

    Now though the table has 'Change Number' column which stores some number (may version no.) but I just did one action, Now my problem is. To Management I want to send a report which show:

    If a test case was moved from Active -> Failed -> Passed -> Failed. I want to extract correct data i.e. only four records for the actual changes. How to do this ? In the above query result If u see last 6 rows I am not even sure why  Failed->Passed->Failed->Passed->Failed->Failed action are reported. I just failed it once.

    I can't even go with - pick the last result for a result date because in  a day many a times one has change the outcome of test case execution which is valid scenario !!!!

    I hope I was able to explain my issue clearly. Could some one please share some knowledge on how to get correct outcome values for the test cases execution.

    Thank you.


    Ahetejazahmad Khan.

    Monday, June 5, 2017 11:12 AM

Answers

  • Hi Ahetejaz,

    Each time test case outcome change, will create a test run, you could order by TestRunId:

    select TestCaseId,TestRunId, ResultOutcome,ResultDate from [dbo].[TestResultView] where TestCaseId = '154169' order by TestRunId,ResultDate

    You will find that all the records with the same test run id, ResultOutcomes are the same. This is the valid status which you want.

    You could also use this query to find the test case result history:

    SELECT [ResultSK],[ResultBK],[ResultId],[TestCaseId],[Outcome]  FROM [Tfs_Warehouse].[dbo].[DimTestResult] where TestCaseId ='154169'  order by DateCompleted

    Best Regards


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ahetejaz Thursday, June 8, 2017 11:22 AM
    Tuesday, June 6, 2017 9:56 AM