7pace Timetracker for DevOps Server's Application Programming Interface (API) feature provides a dedicated URL that allows you to access and extract pure, updated data from Timetracker into the system of your choice as OData feed, e.g. Microsoft Excel, PowerBI, *Tableau, Qlikview, SQL, or any third-party system that “talks” the protocol that our API is offering. Once the data is loaded, you can save the file, and later refresh the data at any time to view updated information from our database. Below, we will guide you through the process of activating the API and then how to access Timetracker data by authenticating the OData feed (using Excel as an example).
*Please note ongoing issue with Tableau: https://community.tableau.com/thread/202580
Activating the API
1. On the Time menu bar, click Settings.
2. In the left panel, click API -> The "Personal API Settings" page displays.
3. The collection support is inside the root with api/{collection}/odata. Copy the URL under "Your reporting API root is:"
You are now ready to begin the process of accessing Timetracker data.
Accessing 7pace Timetracker Data by Authenticating OData Feed in Excel
As mentioned, you can access and extract 7pace Timetracker into the system of your choice as OData feed. Below, we will take you through the steps to do this in Excel.
Important: By default, 7pace Timetracker supports Microsoft Excel 2016; if you are using an earlier version, like Excel 2015, you will need to install the "Microsoft Power Query for Excel" add-in. You can download this add-in from the Microsoft download center.
Note: Remember to wrap the parameter values in your query into single (' ') quotes.
1. Open Microsoft Excel.
2. In a new workbook, click the Data tab.
3. On the Data tab, click the New Query menu and select From Other Sources > From OData Feed -> The OData Feed dialog box displays.
4. In the URL field, above, paste the URL of your 7pace Timetracker account (from the Personal API section of the "Configuration" tab, as mentioned under "Activating the API", above).
5. Click the OK button -> The dialog box to authenticate your OData feed displays.
6. In the left pane, select "Windows".
7. The default is "Use my Current credentials". If sticking with this selection, .enter your username and password. If TFS belongs to a specified domain, select "Use alternate credentials" and enter the credentials for that domain.
8. Click the "Connect" button -> The system displays the "Navigator" dialog box, below, upon successful verification of the URL.
*9. In the left navigation tree, select TimeExport -> The right pane displays the required and optional fields for you to enter before accessing the data.
Please Note: If you are having issues connecting to the Odata feed from Power BI, and you are not able to see the exposed TimeExport function to configure StateDate EndDate etc., as detailed above, you can enter the advanced query editor in Power BI and then enter the function invocation by hand from looking at the function definition within the Excel advanced query editor view.** For example:
let
Source = OData.Feed("feed url address", null, [Query=[#"api-version"="2.1"]]),
#"TimeExport_function (startDate as text, endDate as text, populateTopParentColumns as nullable logical, groupTimeByDateByUser as nullable logical, includeBillable as nullable logical) as table" = Source{[Name="TimeExport",Signature="function (startDate as text, endDate as text, populateTopParentColumns as nullable logical, groupTimeByDateByUser as nullable logical, includeBillable as nullable logical) as table"]}[Data],
#"Invoked FunctionTimeExport_function (startDate as text, endDate as text, populateTopParentColumns as nullable logical, groupTimeByDateByUser as nullable logical, includeBillable as nullable logical) as table1" = #"TimeExport_function (startDate as text, endDate as text, populateTopParentColumns as nullable logical, groupTimeByDateByUser as nullable logical, includeBillable as nullable logical) as table"("2018-01-01", "2018-12-31", null, null, null)
in
#"Invoked FunctionTimeExport_function (startDate as text, endDate as text, populateTopParentColumns as nullable logical, groupTimeByDateByUser as nullable logical, includeBillable as nullable logical) as table1"
**Note: For full instructions on how to do this, please see this FAQ article in our online community.
10. In the StartDate field, enter the start date from when you want to access the data.
Enter the date in YYYY-MM-DD format.
11. In the EndDate field, enter the end date up to which you want to access the data.
Enter the date in YYYY-MM-DD format.
(Optional) In the IsComputeTopPbi field, enter true to enable it.
(Optional) In the GroupByDays field, enter true to enable it.
(Optional) In the IsBillable field, enter true to enable it.
12. Keep the default settings and click the Connect button -> If the authentication is successful, the Navigator dialog box displays the preview of your OData feed.
13. Click the Load button to load the data feed to the Excel -> The data is loaded to Excel and the corresponding query is listed at the right side under Workbook Queries.
14. Click the query to load the corresponding data.
(Optional) Select the query and click the Refresh button (
) to refresh the data corresponding to the query.
Accessing 7pace Timetracker with Node.js
To use Timetracker REST/SignalR API in the DevOps Server environment, you don’t need tokens. All requests must be authorized with NTLM authorization.
To connect via node, this library allows for simple NTLM authentication and returns the body correctly: https://www.npmjs.com/package/httpntlm.
Examples are available here:
1. Common example - https://stackoverflow.com/questions/53302948/ntlm-api-access-with-node-js
2. Working example for getting data from our API:
app.route('/api/getAllTimetrackerData/:StartDate/:EndDate').get((req, res) => {
let _startDate = req.params.StartDate;
let _endDate = req.params.EndDate;
var httpntlm = require('httpntlm');
httpntlm.get({
url: "LINK/odata/TimeExport(StartDate='"+ _startDate +"',EndDate='" + _endDate + "'%20,PopulateTopParentColumns=null,GroupTimeByDateByUser=null,IncludeBillable=null)",
username: 'user',
password: 'Password',
workstation: 'DESKTOP-',
domain: 'Domain',
headers:
{
'Accept': 'application/json; odata.metadata=minimal'
}
}, function (err, response) {
if (err) {
return err;
}
res.send(JSON.parse(response.body).value);
});
});
Using 7pace Timetracker API in Your Own Project
To help you access 7pace Timetracker's data through our API, we've provided the source code of a simple application that demonstrates basic Timetracker API usage in GitHub.
You can get more information about our API model and parameters at Settings -> Reporting and API -> Reporting and REST API -> Timetracker API Reference.
Related Articles
Comments
2 comments
How does the "IsComputeTopPbi" work actually? I was expecting that all sub tasks of a top pbi (e.g. a user story) will be summed up and the total amount would be displayed in a dedicated column. I could not find it though.
Hello, Michael.
Parameter "IsComputeTopPbi" is changing how field "TopTFSStoryPoint" is computed. If set to "false", just actual value of "Effort" field of top parent is used. If set to "true" - value will be set as sum of all "Effort" fields of all children recursively of that top parent item. Hope this is what you're looking for.
Regards,
Eugene
Please sign in to leave a comment.