none
Report Builder 3.0 Report on Last Test Run Result

    Question

  • I am writing a report that shows a test plans last run results for each test.  I have 2 datasets that I am using, but I need to join the datasets so i can report the most recent outcome.

    The final result would be TestPlanId, SuiteName, Test, Max_TestRun, and OutCome.  Below are my two datasets, how can i join them and report the most current outcome?

    GetMaxTestRunResult

    SELECT
      DimTestPlan.TestPlanId
    ,DimTestSuite.SuiteName
      ,DimTestResult.TestCaseId
      ,MAX(FactTestResult.TestRunSK) AS Max_TestRunSK
    FROM
      FactTestResult
      INNER JOIN DimTestResult
        ON FactTestResult.ResultSK = DimTestResult.ResultSK
      INNER JOIN DimTestPlan
        ON FactTestResult.TestPlanSK = DimTestPlan.TestPlanSK
      INNER JOIN DimTestSuite
        ON FactTestResult.TestSuiteSK = DimTestSuite.TestSuiteSK
    WHERE
      DimTestPlan.TestPlanId = @TestPlanId
      AND DimTestSuite.SuiteName LIKE @SuiteName
    GROUP BY
      DimTestPlan.TestPlanId
      ,DimTestSuite.SuiteName
      ,DimTestResult.TestCaseId

    GetOutComeForMaxTestRun

    SELECT
      DimTestPlan.TestPlanId
      ,DimTestSuite.SuiteName
      ,FactTestResult.TestCaseId
      ,DimTestResult.Test
      ,DimTestRun.TestRunSK
      ,DimTestResult.Outcome
    FROM
      FactTestResult
      INNER JOIN DimTestResult
        ON FactTestResult.ResultSK = DimTestResult.ResultSK
      INNER JOIN DimTestRun
        ON FactTestResult.TestRunSK = DimTestRun.TestRunSK
      INNER JOIN DimTestPlan
        ON FactTestResult.TestPlanSK = DimTestPlan.TestPlanSK
      INNER JOIN DimTestSuite
        ON FactTestResult.TestSuiteSK = DimTestSuite.TestSuiteSK
    WHERE
      DimTestPlan.TestPlanId = @TestPlanId
      AND DimTestSuite.SuiteName = @SuiteName
    GROUP BY
      DimTestPlan.TestPlanId
      ,DimTestSuite.SuiteName
      ,FactTestResult.TestCaseId
      ,DimTestResult.Test
      ,DimTestRun.TestRunSK
      ,DimTestResult.Outcome

    Thank you!

    Wednesday, September 04, 2013 4:44 PM

Answers

All replies

  • Hi M

    MLGerow,

    Follow the below link that will help you in resolving your issue:

    http://www.sqlcircuit.com/2012/03/ssrs-2008-r2-lookup-how-to-use-multiple.html

    Thanks,

    Vishal

    Wednesday, September 04, 2013 5:00 PM
  • I am using the Lookup function, but it still returns all of the rows for every run.

    Column =Lookup(Fields!Max_TestRunId.Value, Fields!TestRunId.Value, Fields!Outcome.Value, "Outcome")

    Rows =Count(Lookup(Fields!Max_TestRunId.Value, Fields!TestRunId.Value, Fields!Outcome.Value, "Outcome")

    The first dataset has one row for each Plan/Suite/Test, but when i "join" to the second to display the MaxRunId Outcome, the count is >1.  If the join was working i would only have 1 outcome for each test.  For the attached example my total should never be more than 6, the total number of test cases...

    1st dataset

    SELECT
      DimTestPlan.TeamProjectCollectionSK
      ,DimTestPlan.TestPlanSK AS [DimTestPlan TestPlanSK]
      ,FactTestResult.TestPlanSK AS [FactTestResult TestPlanSK]
      ,DimTestPlan.TestPlanId
      ,DimTestSuite.SuitePath
      ,FactTestResult.TestSuiteSK
      ,DimTestSuite.SuiteName
      ,FactTestResult.TestCaseId
      ,DimTestResult.Test
      ,FactTestResult.TestRunSK
      ,MAX(DimTestRun.TestRunId) AS Max_TestRunId
    FROM
      FactTestResult
      INNER JOIN DimTestResult
        ON FactTestResult.ResultSK = DimTestResult.ResultSK
      INNER JOIN DimTestRun
        ON FactTestResult.TestRunSK = DimTestRun.TestRunSK
      INNER JOIN DimTestPlan
        ON FactTestResult.TestPlanSK = DimTestPlan.TestPlanSK
      INNER JOIN DimTestSuite
        ON FactTestResult.TestSuiteSK = DimTestSuite.TestSuiteSK
    WHERE
      DimTestPlan.TeamProjectCollectionSK = 13
      AND DimTestPlan.TestPlanId = @TestPlanId
      AND DimTestSuite.SuitePath Like '%' + @SuitePath + '%'
      AND DimTestResult.Test IS NOT NULL

    GROUP BY
      DimTestPlan.TeamProjectCollectionSK
      ,DimTestPlan.TestPlanSK
      ,FactTestResult.TestPlanSK
      ,DimTestPlan.TestPlanId
      ,DimTestSuite.SuitePath
      ,FactTestResult.TestSuiteSK
      ,DimTestSuite.SuiteName
      ,FactTestResult.TestCaseId
      ,DimTestResult.Test  
      ,FactTestResult.TestRunSK
    ORDER BY 11 DESC

    2nd Dataset

    SELECT
      DimTestPlan.TestPlanId
      ,DimTestResult.TestCaseId AS [DimTestResult TestCaseId]
      ,DimTestResult.Outcome
      ,DimTestRun.TestRunId
      ,FactTestResult.TestCaseId AS [FactTestResult TestCaseId]
      ,FactTestResult.TeamProjectSK
    FROM
      FactTestResult
      INNER JOIN DimTestResult
        ON FactTestResult.ResultSK = DimTestResult.ResultSK
      INNER JOIN DimTestRun
        ON FactTestResult.TestRunSK = DimTestRun.TestRunSK
      INNER JOIN DimTestPlan
        ON FactTestResult.TestPlanSK = DimTestPlan.TestPlanSK
    WHERE
      DimTestPlan.TestPlanId = @TestPlanId
    GROUP BY
      DimTestPlan.TestPlanId
      ,DimTestResult.TestCaseId
      ,DimTestResult.Outcome
      ,DimTestRun.TestRunId
      ,FactTestResult.TestCaseId
      ,FactTestResult.TeamProjectSK


    mlgerow

    Monday, September 30, 2013 9:51 PM
  • Vishal,

    Thanks in advance for your assistance!.

    mlgerow


    mlgerow

    Monday, September 30, 2013 9:53 PM