Manual test execution trend sample report

Azure DevOps Services

This article shows you how to get the execution state of one or more Test Plans in Power BI.

Important

Power BI integration and access to the OData feed of the Analytics Service are in Preview. The sample queries provided in this article are valid only against Azure DevOps Services and depend on v3.0-preview. We encourage you to use these queries and provide us feedback. As we add features, we will post them on the Microsoft DevOps blog.

The report generated is similar to following image and the Outcome trend chart of the Track test status - Progress report.

Sample - Execution Trend - Report

The report displays a trend chart that summarizes the number and outcome states of Test Plans executed over a specified period of time.

  • Passed: Number of test cases passing.
  • Failed: Number of test cases failing.
  • NotApplicable: Number of test cases
  • Blocked: Number of test cases blocked from running.
  • NotExecuted: Number of test cases defined but not executed.

Questions the report answers

This report helps you track the team's progress with respect to planned testing of your product or service by answering the following questions:

  • How much testing is complete?
  • What is the current status of tests passing, failing, or being blocked?
  • How many tests are passing and what does the trend indicate about software quality?
  • How many tests are failing?
  • How many tests are blocked and what does the trend indicate?

A healthy test execution trend report shows a steady progress in test plans running and passing. Ideally, the report shows a relatively flat number of test cases for a given plan. As the development cycle progresses, the number of passing test cases should increase, and the numbers of test cases in other states should decrease.

Note

This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

Prerequisites

For the report to generate useful data, the team must perform the following activities to manage test plans:

  • Define test plans, test suites, and test cases. Specify their state. For a Test Suite to run, it must be in the In Progress state. For a Test Case to run, it must be in the Ready state. For details, see Create test plans and test suites and Create manual test cases.

  • Run manual tests and verify the results. Mark the results of each validation step in the test case as passed or failed. For details, see Run manual tests.

    Note

    Testers must mark a test step with a status if it is a validation test step. The overall result for a test reflects the status of all the test steps that were marked. Therefore, the test will have a status of failed if any test step is marked as failed or not marked.

Sample queries

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let 
    Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/TestPointHistorySnapshot?" 
        &"$apply=filter((TestSuite/TestPlanTitle eq '{testPlanTitle}') and (DateSK ge {startDate} and DateSK le {endDate}))" 
        &"/groupby(" 
            &"(DateSK)," 
            &"aggregate(" 
                &"$count as TotalCount," 
                &"cast(ResultOutcome  eq 'Passed', Edm.Int32) with sum as Passed," 
                &"cast(ResultOutcome  eq 'Failed', Edm.Int32) with sum as Failed," 
                &"cast(ResultOutcome eq 'Blocked', Edm.Int32) with sum as Blocked," 
                &"cast(ResultOutcome eq 'NotApplicable', Edm.Int32) with sum as NotApplicable," 
                &"cast(ResultOutcome eq 'None', Edm.Int32) with sum as NotExecuted," 
                &"cast(ResultOutcome ne 'None', Edm.Int32) with sum as Executed 
            ) 
        )", null, [Implementation="2.0"]) 
in 
    Source 

Substitution strings

Each query contains the following strings that you must substitute with your values. Do not include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

  • {organization} - Your organization name
  • {project} - Your team project name, or omit "/{project}" entirely, for a cross-project query
  • {testPlanTitle} - Title of your test plan. Example: Fabrikam test plan.
  • {startDate} and {endDate} - Date range of interest. You can enter the dates in YYYYMMDD format. e.g. 20190822 for 22nd August 2019.

Query breakdown

The following table describes each part of the query.

Query partDescription
filter((TestSuite/TestPlanTitle eq '{testPlanTitle}'))Return data for only selected test plan. You can add multiple plans with a clause like filter((TestSuite/TestPlanTitle eq '{testPlanTitle1}' or TestSuite/TestPlanTitle eq '{testPlanTitle2}')). You can also apply any other filters related to test suites, test configurations here.
and (DateSK ge {startDate} and DateSK le {endDate})Date range of interest. You can enter the dates in YYYYMMDD format.
/groupby((DateSK)Group the data into bins of same date. This produces one set of values per day in given date range.
/aggregate($count as TotalCount,Aggregate data across the filtered test points with having count as TotalCount
cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as Passed,While aggregating, type-cast test points having latest execution outcome 'Passed' to 1 and sum them up as 'Passed' metric.

Power BI transforms

In Power BI, do the following steps.

When finished, you may choose to rename columns.

  1. Right-click a column header and select Rename...

    Power BI Rename Columns

  2. Change the type of count columns to Whole Number and percentage fields to Decimal Number.

    Power BI Change Column Type

  3. You also may want to rename the query from the default Query1, to something more meaningful.

    Power BI Rename Query

  4. Once done, choose Close & Apply to save the query and return to Power BI.

    Power BI Close & Apply

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Sample - Execution Trend - Fields

To create the report, do the following steps:

  1. Create a Power BI visualization Stacked Area Chart.
  2. Drag and drop DateSK in Axis.
  3. Drag and drop Passed, Failed, Blocked, NotApplicable and NotExecuted in Values.

Your report should look similar to the following image.

Sample - Execution Trend - Report

Full list of sample reports for Test Plans