Help Wanted - does anyone know the correct query format to return a workitem list from VSO in JSON?

[Virtual_Ames]

Hello:

Does anyone know the correct query format – using REST API - to return a list of workitems from VSO in JSON format?

Background:

We are producing a PowerBI dashboard to monitor multiple projects as they proceed through the lifecycle. VSO project health and status data is required. Project Managers respond to a set of questions to provide the critical information we will monitor.

We have created a .NET component that reads the PM responses and goes out to VSO and returns the values.

PM responses are of two types:

1. VSO ID numbers.This works fine via JSON. No problem. 
2. VSO Named Queries/Custom queries. For items like “Test Cases” – where many VSO IDs will be in the response – we ask the PM to create a custom query (referencing tags, etc). We want to be able to look at all the test cases and see if there are SEV1 or PRI1 bugs, or what percentage of test cases have been completed.

Problem: we are not able to get the responses from the Named Query.

If a PM creates the named query and uses the copy URL function in VSO, we get a query formulated like this:

_https://microsoftit.visualstudio.com/DefaultCollection/OneITVSO/SP-CRMERP-SAP%20ERP%20Service/_worki...

We can see this data in a URL – however, we cannot easily extract the data.

Via help, we found that we should be able to use the REST API to access the VSO data – in this format:

                https://[instance-name].visualstudio.com/DefaultCollection/[project-name]/_apis/wit/wiql/[query-guid]"

However, using our info – like this:

                https://microsoftit.visualstudio.com/DefaultCollection/OneITVSO/_apis/wit/wiql/XXXX8423f074-893f-XXX...

(I am thinking that the GUID is the tempQueryId)

The response is “Webpage cannot be found”.

Help Wanted:

                Does anyone know how to format the REST API query to return a JSON list all the workitems?

Thanks,

[v-qiuyu-msft · Accepted solution]

Hi @Virtual_Ames,

In your scenario, you need to make sure how to call VSO rest API to return the expected results firstly. You can post a thread in Visual Studio Team Services forum.

Once you make sure the VSO rest API, then you can use web data source in Power BI desktop to get data, you can refer to this sample: Calling REST APIs and Parsing JSON made simple with Power BI.

Best Regards,
Qiuyun Yu

[Seth_C_Bauer]

@Virtual_Ames While this won't answer your question directly, I just wanted you to be aware that MSFT released the connector to VSTS (VSO) to the Desktop in the January release... So unless there was a different reason other than "there was no other way" to access the info, this might streamline your efforts.