How to Create dasbhaord in PowerBI, that shows the list of Test Cases which do not have Attachment?

[sravs2007]

Can anyone tell me how to get llist of Test Cases which do not have Attachment(Tested Evidence) in ADO Test Plan ..and create dasbhoard in PowerBI.

[v-venuppu]

Hi @sravs2007 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @sravs2007 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rohit1991]

Hi @sravs2007 

To create a dashboard in Power BI that shows the list of test cases without attachments (test evidence) from Azure DevOps (ADO) Test Plans, you first need to connect Power BI to your ADO environment. You can do this either by using the Azure DevOps Analytics Views or the OData feed. Once connected, pull in the relevant tables, such as Test Cases and Attachments.

Then, create a relationship between the test cases and their corresponding attachments based on Test Case ID or a similar key. After that, you can create a measure or use a filter to identify test cases that do not have any associated attachments — typically by checking for a blank or null value in the attachment field. Finally, design a table visual in Power BI to display only these test cases, and then publish the report to Power BI Service to pin it to a dashboard. This will give you a live view of all test cases missing evidence, helping your QA process stay on track.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[sravs2007]

Am not finding an option to add in Analytics View and OData feed. as well

[v-venuppu · Accepted solution]

Hi @sravs2007 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna @rohit1991  for the prompt response.

Since Attachments aren't exposed via Analytics Views/OData, use the Azure DevOps REST API to retrieve both:

• Test Cases
• Test Case Attachments

Here's few instructions:

1.Connect Power BI to Azure Devops:

Use Azure DevOps Analytics Views or the REST API to fetch data on test cases and their attachments. Since the "Attachment Count" field isn't available, you can use the Test Cases API and Attachments API in the Azure DevOps REST API documentation.

2.Fetch Data:

Retrieve test cases and check if attachments exist using the Test Cases API and Attachments API. You can filter out test cases with no attachments by identifying where Attachment Count = 0.

3.Create Relationships:

Create a relationship between the test cases and their corresponding attachments based on Test Case ID or a similar key.

4.Create Measures:

Create a measure to identify test cases without attachments:

Test Cases Without Attachments =
CALCULATE(
    COUNTROWS(TestCases),
    FILTER(TestCases, ISBLANK([AttachmentID]))
)

5.Design Table Visualization:

Create a table visualization in Power BI to display test case details, filtered to show only those without attachments.

Test Cases API:

Test Suites - Get Test Cases - REST API (Azure DevOps Test) | Microsoft Learn

Attachments API:

Test Suites - REST API (Azure DevOps Test) | Microsoft Learn

Loop over the list of Test Case IDs and retrieve attachments.

You can script this using Power Query (M) in Power BI with a custom function to call this API per Test Case ID.

• Ensure you have the necessary permissions to access Azure Devops data.
• Use Power Query to transform and filter the data as needed.
• Publish the report to Power BI Service to pin it to a dashboard for a live view of all test cases missing evidence.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

 

[sravs2007]

Am Getting 404 error when i try to hit these 2 APIs (Updated Project and Org in APIs) 

[v-venuppu]

Hi @sravs2007 ,

Can you try copy and paste links in browser as I was able to open it.

Thank you.

[v-venuppu]

Hi @sravs2007 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Akash_Varuna]

Hi @sravs2007 You could try by connecting Power BI to Azure DevOps through Analytics Views or the REST API to fetch data on test cases and their attachments. Then, use Power Query to filter out test cases with no attachments by identifying where Attachment Count = 0 or linking test case data with attachment details. Finally, create a table visualization in Power BI to display test case details, filtered to show only those without attachments.

[sravs2007]

@Akash_Varuna i cannot find the attached count field to add in Analytics View .. regarding RestAPI.. could you please give me sample restAPi if you know.. it would be greatful

[Akash_Varuna]

@sravs2007 Since the "Attachment Count" field isn't available, you could check the Test Cases API and Attachments API in the Azure DevOps REST API documentation. Use these to retrieve test cases and check if attachments exist. Here are the relevant docs:

1. REST API Overview

2. Test Cases API

3. Attachments API