Reporting API for DevOps Server (on-prem)
Accessing 7pace Timetracker Server (on-prem) with Node.js
Reporting API for DevOps Services (cloud)
Using 7pace Timetracker API in your own project
Introduction
This article covers v2.1, for the latest API version check following article.
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 7pace 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 7pace Timetracker data by authenticating the OData feed (using Excel as an example).
Reporting API for DevOps Server on-prem
*Please note ongoing issue with Tableau: https://community.tableau.com/thread/202580
Activating the API
1. On the 7pace Timetracker menu bar, click Settings.
2. In the left panel, click REPORTING & REST API -> REST API.
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 7pace 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 "Settings" 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 DevOps Server 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 and this video from YouTube may also be helpful:
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 7pace 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);
});
});
Reporting API for DevOps Services (cloud)
This article covers v2.1, for the latest API version check following article.
Activating the API
Configuring the API to access 7pace Timetracker for DevOps Services' data is a two-step process. First, you access the Settings > API page in 7pace Timetracker, activate API access, and then create an access token. Second, you will authenticate the OData feed into the system of your choice using that access token.
Once you successfully perform both steps, you can access 7pace Timetracker data via the system you have chosen.
1. On the 7pace Timetracker menu bar, click Settings.
2. In the left panel, select "Reporting and REST API" -> The Personal API Settings page displays.
3. Click the "Activate API Access" checkbox -> Additional information about the API displays, including the access token status, indicating if you have an already-created token that is active, inactive, or not yet created.
Important Info on Access Tokens
An access token is your personal password and allows you to authenticate the OData feed and keep accessing updated 7pace Timetracker data indefinitely, just by refreshing the data.
Once you create a token, it is very important that you save it somewhere safe in your system. 7pace Timetracker does not store this access token anywhere in the database and it is displayed on the page only once. If you try to access any other page without first saving the token, it will be lost.
Your access token is valid for a period of one year. On the Personal API settings page, you can view the token's expiry date, (as detailed in the above screenshot) and you can use the same token to authenticate your OData feed for your account until it expires. If you create a new access token, your existing token is deactivated and you will need to authenticate your OData feed again with the new one.
You are now ready to begin the process of accessing 7pace Timetracker data.
Accessing 7pace Timetracker Data by Authenticating OData Feed in Excel
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. Copy the URL of your 7pace Timetracker account (from the Personal API section of the "Settings" tab, as mentioned under "Activating the API", above) and paste it into the URL field, above.
5.. Click the OK button -> The system displays the "Navigator" dialog box upon successful verification of the URL.
*6. In the left navigation tree, select TimeExport -> The right pane displays both the required and optional fields for you to enter before accessing the data.
Note: On the first request, the system may ask for basic authorization before you select your query parameters.
*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 in Step 6, 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 and this video from YouTube may also be helpful:
7. In the StartDate field, enter the start date from when you want to access the data.
Enter the date in YYYY-MM-DD format.
8. 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.
9. Click the Apply button -> The system displays a dialog box to authenticate your OData feed.
10. In the left pane, click Basic or Token.
Note: Leave the Username field blank or use the word "token".
11. Copy your access token. You can either use a new access token or you can copy the access token that you already created and stored in your system.
12. In the Password field, paste the access token that you created.
14. Click the Connect button -> The Navigator dialog box displays a preview of your OData feed if authentication is successful.
15. Click the Load button -> The data is loaded to Excel and the corresponding query is listed at the right side under "Workbook Queries".
16. 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.
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 7pace 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 -> 7pace 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.