Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How do I include empty test suites in Suite-Level aggregation

Hello Community, 

 

I'm currently learning how to do Power BI reports. Right now, I'm in this page: https://docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-test-plans-aggregate-data-level?view=azure-devops&tabs=powerbi

 

Using the below sample query from the website, how do I modify it so that the result will include empty test suites. I have 10 test suites at the moment (5 has test cases, 5 has none). My query only returns the 5 test suites containing test cases.

 

Any help is very much appreciated. Thank you!

 

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

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    3 years ago

    Hi Anonymous ，

    Could you pls share a sample data and expect output result,Remember to remove confidential data.

     

     

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi there v-luwang-msft ,

       

      So here's an example, In ADO I have a test plan with 3 test suites. 2 of them have test cases inside, the other 1 is empty.

       

      In Power BI, I used this query:

      let
      Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/TestPoints?"
      &"$apply=filter((TestSuite/TestPlanTitle eq '[Dummy Test Plan] Sprint 01' and TestSuite/IdLevel2 ne null))"
      &"/groupby("
      &"(TestSuite/TitleLevel2),"
      &"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

       

      And yields this result:

       

      What I am trying to do is to include the empty test suite in the result. My expected would be another row with a value in TestSuite.TitleLevel2 column and 0s for the rest.