I was playing around today with some TFS data (that’s were we store all our product data ) and of course I was looking for a way to get this data into Power Pivot and Excel. I found a way
It turns out TFS online supports OData for most of their services. See this blog post on how to enable it and where to connect to: https://tfsodata.visualstudio.com/
The important thing is this:
Team Foundation Service authentication:
(Optional) In order to authenticate with Team Foundation Service, you will need to enable and configure basic auth credentials on tfs.visualstudio.com:
- Navigate to the account that you want to use on https://tfs.visualstudio.com. For example, you may have https://account.visualstudio.com.
- In the top-right corner, click on your account name and then select My Profile
- Select the Credentials tab
- Click the ‘Enable alternate credentials and set password‘ link
- Enter a password. It is suggested that you choose a unique password here (not associated with any other accounts)
- Click Save Changes
To authenticate against the OData service, you need to send your basic auth credentials in the following domainusername and password format:
- accountusername
- password
- Note: account is from account.visualstudio.com, username is from the Credentials tab under My Profile, and password is the password that you just created.
So I did that for my TFS online instance and opened up PQ and selected import from OData:
Next I pasted in my workitems OData url (that I got from the site above):
This prompts me for authentication, and I choose basic auth and enter my credentials:
This opens up the query editor and I am good to go from that point:
It’s pretty cool that this worked and I though I share it
perfect. Thank you! Was just asked by some stakeholders for more reporting from VSO yesterday morning, and read this in the afternoon, perfect timing!
I live to serve, great timing 🙂
When I try to do this (also by the browser) I get “Not found – The TFS Project Collection named DefaultCollection was not found.”
Idea?
It does not work for me 🙁 I got this error : “OData: The given URL neither points to an OData service or a feed:”