Manual test suites aggregated view sample report

Azure DevOps Services

Some scenarios have a hierarchical organization of test suites as per organization departments or modules as shown below.

Sample - Test Plan structure for aggregated view

As shown in the above image, there are level 3 test suites with same names under different level 2 test suites. In that case, an aggregation at a particular test suite level may be required. In this example it is explained how to do this for all level 3 test suites in a test plan.

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.

For a sample test suite hierarchy in a test plan like below, you can configure this report.

Sample - Test Suites Aggregated View - Report

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/TestPoints?" 
        &"$apply=filter((TestSuite/TestPlanTitle eq '{testPlanTitle}' and TestSuite/IdLevel3 ne null))" 
            &"/groupby(" 
                &"(TestSuite/TitleLevel3)," 
                &"aggregate(" 
                    &"$count as TotalCount," 
                    &"cast(LastResultOutcome eq 'Passed', Edm.Int32) with sum as PassedCount," 
                    &"cast(LastResultOutcome eq 'Failed', Edm.Int32) with sum as FailedCount," 
                    &"cast(LastResultOutcome eq 'None', Edm.Int32) with sum as  NotRunCount," 
                    &"cast(LastResultOutcome ne 'None', Edm.Int32) with sum as RunCount" 
            &")" 
        &")" 
        &"/compute(" 
            &"RunCount mul 100 div TotalCount as RunPercentage," 
            &"NotRunCount mul 100 div TotalCount as NotRunPercentage," 
            &"iif(TotalCount gt NotRunCount, PassedCount mul 100 div RunCount,0) as PassedPercentage," 
            &"iif(TotalCount gt NotRunCount, FailedCount mul 100 div RunCount,0) as FailedPercentage" 
        &")" 
        &"&$orderby=RunPercentage desc", null, [Implementation="2.0"]) 
in 
    Source 

Substitution strings

Each query contains the following strings that you must substitute with your values. Don't 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.

Query breakdown

The following table describes each part of the query.

Query partDescription
filter((TestSuite/TestPlanTitle eq '{testPlanTitle}' and TestSuite/IdLevel3 ne null))Return data for only selected test plan. Also return the data for level 3 test suites onwards. You can add multiple plans with a clause like filter((TestSuite/TestPlanTitle eq '{testPlanTitle1}' or TestSuite/TestPlanTitle eq '{testPlanTitle2}' and TestSuite/IdLevel3 ne null)). You can also apply any other filters related to test suites, test configurations here.
/groupby((TestSuite/TitleLevel3),The test suites to group by. If you are interested in getting report for all distinct test suites lying at level 3 of test suite hierarchy, you can specify this. You can change the level number from 1-14. If you wish to separate test suites with same name, you can use /groupby((TestSuite/TitleLevel3, TestSuite/IdLevel3), to sure that even if you have two test suites with same name, they will be shown as two different entries as they would have two different IDs.
/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.
/compute(Executed mul 100 div TotalCount as ExecPctProvide a computed metric ExecPct which is equal to (Executed test points / Total count * 100).

Power BI transforms

In Power BI, do the following steps.

When finished, you may choose to rename columns.

  1. Expand TestSuite

    • Choose the expand button.

      Power BI Expand Test Suite

    • Select the fields to flatten.

      Power BI select fields to flatten

    • The table now contains entity field of TestSuite.IdLevel3 and TestSuite.TitleLevel3.

      Power BI expanded test suite

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

    Power BI Rename Columns

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

    Power BI Change Column Type

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

    Power BI Rename Query

  5. 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.

To create the report, do the following steps:

  1. Create a Power BI visualization Table.
  2. Add the columns TestSuite.TitleLevel3, NotRunCount, RunCount, PassedCount, TotalCount.
  3. Select Sum as aggregation for Count.

    Power BI select Sum as aggregation

Your report should look similar to the following image.

Sample - Test Suites Aggregated View - Report

Full list of sample reports for Test Plans