Connecting to Timetracker Reporting API for DevOps, exporting custom fields, connecting SSIS, and using dynamic parameters in Excel Power Query
Reporting API for DevOps Server (on-prem)
Accessing 7pace Timetracker Server (on-prem) with Node.js
7pace Timetracker Client API: Pairing with DevOps Server (on-prem) Workflow
Reporting API for DevOps Services (cloud)
7pace Timetracker Client APO: Pairing DevOps Services (cloud) Workflow
7pace Timetracker Reporting API: Exporting DevOps Server/Services Custom Fields
Connecting SSIS to 7pace Timetracker for DevOps Services (cloud) and Server (on-prem)
7pace Timetracker Reporting API: Using Dynamic Parameters in Excel Power Query
Introduction
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 for both DevOps Server on-prem and DevOps Services cloud and then show you 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 there is an ongoing issue with Tableau. Please use NTLM authentication.
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:" Copy this API root.
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 (see screenshot, below).
*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, please check the 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 > Reporting and REST API page in 7pace Timetracker and 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".
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. Create one if it's not already created or is missing by clicking "Create New Token".
3. Copy "Your API Root" or "Legacy API Root".
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. Paste the URL you copied 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 system displays a dialog box to authenticate your OData feed.
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.
7pace Timetracker Reporting API: Exporting DevOps Server/Services Custom Fields
Assumption: User has accessed 7pace Timetracker data by authenticating OData feed.
1. After accessing 7pace Timetracker data via the API, as detailed above, create a query in DevOps Server/DevOps Services that will load all necessary work items.
2. Load Query to Excel (for more information, please see "Add work items", steps 1 - 5 inclusive, at the following link: https://www.visualstudio.com/en-us/docs/work/office/bulk-add-modify-work-items-excel).
3. Create a new data source from the table:
4. Select Merge in Excel:
5. Merge the 7pace Timetracker export using the "Work Item ID" or "workitem" column and the DevOps Server/DevOps Services query using the ID column. In the "Join Kind" dropdown, select "Left Outer":
6. Click "Ok" -> DevOps Server/DevOps Services data is merged with 7pace Timetracker data.
Note: Cross-project data collection is not currently supported by Excel (as of March 31, 2017). However, it might be possible to make multiple calls to different projects, append as one big table, and then apply the merge to that consolidated table.
Connecting SSIS to 7pace Timetracker for DevOps Server/Services
When trying to access the 7pace Timetracker API, if you encounter the following error message for the SSIS OData-Source - "The payload kind 'ServerDocument' of the given data feed is not supported" - please change your settings as per the screenshots, below.
Connecting SSIS to 7pace Timetracker DevOps Services (cloud)
Navigate to the "Settings" tab of 7pace Timetracker and then select "Reporting and REST API".
Copy the link found under the 'Your Reporting API Root Is' label:
... and paste here ...
... and this into the OData Source:
Resource path:
TimeExport(StartDate='2018-01-01',EndDate='2019-01-01',PopulateTopParentColumns=null,GroupTimeByDateByUser=null,IncludeBillable=null)
Connecting SSIS to 7pace Timetracker for DevOps Server (on-prem)
Set this into your OData Connection Manager Editor (link to copy: http://%TIMETRACKER_URL%:8090/api/%COLLECTION_URL%/odata) ...
... and this into the OData Source:
7pace Timetracker Reporting API: Using Dynamic Parameters in Excel Power Query
7pace Timetracker's reporting API feature that allows you real-time access to 7pace Timetracker data secured by OAuth, available in the format OData via REST in Microsoft Excel. Once accessed, it is just a couple of extra clicks to add the data from DevOps Server in the same way, dynamically, from DevOps Server itself. Your application for analysis will allow you to merge the data on your platform so that you have a much more flexible, dynamic experience than before.
Below, you'll find the steps on how you can dynamically use the parameters in Excel Power Query and load DevOps Server data based on the specified parameter values.
IMPORTANT: Remember to wrap the parameter values in your query into single (' ') quotes.
1. Open Microsoft Excel.
2. In a new worksheet, click Insert > Table and create a table of two (2) columns and five (5) rows, including the header row. You can name the first column header "Parameter" and the second column header "Parameter Value".
3. In the table, set up the parameters list as shown in the first screenshot, below (the Reporting API uses a specific parameters format, therefore, make sure to use these parameter values - see also, second screenshot):
4. Then enter in the "Parameter Values" column according to the screenshot, below, ensuring the entries you select from the "Description" are formatted according to the "Data Type":
Important: You must format the parameter values for "Start Date" and "End Date" in the Parameters column to Text. You must format "Populate Top Parent Items", "Group Time by Date by User", and "Include Billable" values in the Parameter Value column to boolean. If your version of Excel won't recognize "TRUE" or "FALSE" as their boolean equivalents, you will need to convert them. An IF statement will accomplish this task:
=IF (OR(A24,A24="TRUE"),TRUE,FALSE)
5. Open a new Excel sheet and access the 7pace Timetracker data as OData feed.
6. For more information, see 7pace Reporting API for DevOps Server/Services.
7. Click the Query tab and then click the Edit button to open the original query text.
8. Click the Advance Editor menu.
The system displays the query in the advanced query editor.
Below is the actual query text:
|
let Source = OData.Feed("https://tfstimetrackervsts-ci.azurewebsites.net/webapi/odata"), #"TimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table" = Source{[Name="TimeExport",Signature="function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table"]}[Data], #"Invoked FunctionTimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table1" = #"TimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table"("2016-01-01", "2016-01-01", null, null) in #"Invoked FunctionTimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table1" |
In the query, the highlighted text actually shows the values that you specified for the parameters in the "Navigator" window. Parameters with null value indicate that you have not specified any value.
9. Update the query as shown, below.
Add the variables for the parameters and replace the actual values with these parameter variables.
The blue highlighted text, below, represents the changes made to the original query.
|
let Parameter = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], // Report parameters table name Param_StartDate = Parameter{0}[Formatted Value], // First table row: Start Date Param_EndDate = Parameter{1}[Formatted Value], // Second table row: End Date Param_ParentItems = Parameter{2}[Formatted Value], // Second table row: Top Parent Items Param_GroupByDays = Parameter{3}[Formatted Value], // Second table row: Group Time by date by user Source = OData.Feed("https://tfstimetrackervsts-ci.azurewebsites.net/webapi/odata"), #"TimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table" = Source{[Name="TimeExport",Signature="function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table"]}[Data], #"Invoked FunctionTimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table1" = #"TimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table"(Param_StartDate, Param_EndDate, Param_ParentItems, Param_GroupByDays) in #"Invoked FunctionTimeExport_function (StartDate as text, EndDate as text, IsComputeTopPbi as nullable logical, GroupByDays as nullable logical) as table1" |
10. Click the Done button and go to the sheet where you accessed the OData feed.
11. Click the Query tab and select Refresh. You can also right-click within the sheet and select Refresh.
The data is loaded as per the changed parameter values. Afterwards, you can update the values in the Parameters table and refresh the query to reload the updated data.
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.