Reporting API limits, endpoints, Timetracker OData query extended parameters, data formatters, and connecting to API (Excel, C#, Python, Javascript)

7pace Timetracker API & Reporting Tutorial:

In addition you could check our tutorial video about the Reporting page, where we also utilize our API: 7pace Timetracker's Reporting Page

Contents

Timetracker Reporting API overview

Reporting and API Limits

WorkItem Fields Naming Convention

API endpoints overview

• worklogsOnly endpoint for worklogs without work items
• worklogsWorkItems worklogs with work item object
• workItems list of work items without hierarchy
• workItemsHierarchy / workItemsHierarchyAllLevels / hierarchical endpoints for work items
• budgets endpoint for budgets
• Expanding / Retrieving linked objects

Timetracker OData query extended parameters

• worklogsFilter for filtering worklogs before executing queries
• workItemsFilter for filtering work items before executing queries
• customFields for including own fields into response
• rollupFields for adding summary of specific fields
• prefilter (filter by Me)
• Using post requests

Budget information in worklogs and work items

Widget Chart Parameter Options 

• Line Chart Fields
• Column  Chart Fields
• Stacked Bar Chart Fields
• Pie/Donut Chart Fields
• Number Chart Fields
• Table Fields
• Drilldown Table Fields

Data formatters

• TimeLength
• NullToComment
• Number
• String
• WorkItemId
• WorkItemType
• WorkItemIndicator
• WorkItemIdWithIndicator
• LocaleSettingsDateTime
• CustomDateTime
• DateTimeAgo
• MathExpression

How Reporting works

Connecting to API (Excel, C#, python, javascript)

• Excel / Power BI
• Pivot table example using Excel
• Filtering data / expanding work item columns
• C#
• Python
• Javascript
• Node.js
• Using Dynamic Parameters in Excel Power Query

Timetracker Reporting API Overview

With 7pace Timetracker 5, we introduce the Reporting API v3. This API encompasses Timetracker data such as worklogs, the work items linked to worklogs, and the ability to build queries to work items and their hierarchy.

The Timetracker Reporting API and most of the syntax supported is based on the OData framework.  For an overview on OData query language, please follow these pages: OData documentation and Aggregation Extension. If you see something that's not working, please let us know.

For the entire API reference, please see API reference. 

To find your Reporting API URL, navigate to the "Settings" tab of Timetracker -> Reporting & REST API section.

Reporting and API Limits

When Reporting is enabled, there are several things of which you should be aware. First, the Service Account should be enabled to make the Reporting API work.

All data displayed in 7pace Timetracker that is related to work items, switches to use the Reporting API internally.  This means that changes to work items in DevOps might not be reflected immediately in the interface, but may take between two minutes to an hour to display. To force an update, please navigate to Settings -> Reporting and API -> API and click "Schedule update now".

The API returns data as paged:

• Worklogs endpoints return data by 1000 items per page
• All endpoints related to work items return data by 100 items per page

When data is paged, there is a "@odata.nextLink" property presented in the response object, with a link to the next page.

Please check out the 7pace website for general API limits applied both for REST and Reporting API.

Note: Abuse or excessively frequent requests to GitHub via the API may result in the temporary or permanent suspension of your Account's access to the API. GitHub, in our sole discretion, will determine abuse or excessive usage of the API. We will make a reasonable attempt to warn you via email prior to suspension.

You may not share API tokens to exceed GitHub's rate limitations.

WorkItem Fields Naming Convention

Complex Field Names

Due to OData specifics, we can’t use a standard DevOps delimiter for field references, and therefore, we use “_” in complex field names instead of “.” everywhere.

Example: If the DevOps field name is “System.Id”, it will be “System_Id” in Timetracker Reporting API responses.

Object and Sub-Object Access

In 7pace Timetracker Reporting widgets, when viewing or reconfiguring the the OData Query and Console fields within a widget, please note that the OData Query field requires a forward slash ("/"), and the Console field requires a dot ("."). Therefore, the same data would display as in the examples, below:

OData Query: WorkItem/Microsoft_VSTS_Common_Activity

Console: WorkItem.Microsoft_VSTS_Common_Activity

Keep in mind that for custom fields parameters, you must provide the original DevOps field reference, so if you have “Some.CustomField”, you should pass it to the customFields parameter unchanged. Please refer to this section for additional details on custom fields.

Also, if an object contains other objects as a part of it, you must split the objects with a forward slash. For example: WorkItem.Microsoft_VSTS_Common_Activity - Here, the WorkItem is the object and the field (Microsoft_VSTS_Common_Activity) is another object, so we separate it with a forward slash "/": Microsoft/VSTS_Common_Activity.

Finally, a note on pulling data related to Activity Types: You will need to use an endpoint that contains the 7pace Timetracker-specific field ActivityType, like the WorkLogsWorkItems or WorkLogsOnly endpoints. The field Microsoft.VSTS.Common.Activity is a DevOps field, and ActivityType is a 7pace Timetracker field.

API endpoints overview

This overview details the purpose and intended use of every Reporting endpoint that 7pace Timetracker provides.

In addition to the standard OData parameters that Timetracker provides, there are also several extending options to the query; please see the links, below, for more detail.

Note: Worklog time is provided in seconds; if you require hours in your environment, please divide PeriodLength (and all similar properties in the API) by 3600.

worklogsOnly

This endpoint is the fastest one; it only provides data related to worklogs without joining worklogs to the related work items. 

If you need to integrate to other systems based on work item IDs only, this is the endpoint you should choose.

Usage scenarios:

1. Report: "How many hours did every user add into the system during a specific period?"

   /workLogsOnly?$apply=filter(Timestamp ge 2019-11-01T00:00:00Z and Timestamp lt 2019-12-01T00:00:00Z)
   /groupby((User/Name),aggregate(PeriodLength with sum as PeriodLength))
   &$orderby=User/Name
   

2. "Sum of time for the whole team during specific date range"
   

   /workLogsOnly?$filter(Timestamp ge 2019-11-01T00:00:00Z and Timestamp lt 2019-12-01T00:00:00Z)
   /aggregate(PeriodLength with sum as PeriodLength)

3. Scenarios with aggregating worklogs by User, Activity Type, Budget, Date and others

worklogsWorkItems

This endpoint provides data on all worklogs, but also includes information on the work item that the worklog linked to in the work item property.

The workItem field contains all standard fields that DevOps has itself and additional fields CustomStringField1-CustomStringField5, CustomNumericField1-CustomNumericField5, CustomBooleanField1-CustomBooleanField5. 

Use these fields to load data from fields you created for work items manually and that are not a part of any DevOps standard template.

Note: There is no way to load HTML or text fields over Timetracker's Reporting API.

Check full reference here.

Usage scenarios:

1. Report: "Team personal time per Project"
   Sample query:
   

   /workLogsWorkItems?$apply=groupby((User/Name,WorkItem/System_TeamProject,WorklogDate/ShortDate),
   aggregate(PeriodLength with sum as PeriodLength))
   &$orderby=WorklogDate/ShortDate desc

2. Report: "Distribution of time per project"
   

   /workLogsWorkItems?$apply=groupby((WorkItem/System_TeamProject),
   aggregate(PeriodLength with sum as PeriodLength))
   &$orderby=WorkItem/System_TeamProject

3. Scenarios with aggregating worklogs by Project, Area Path, Iteration, Assigned To and others

workItems

As opposed to the  worklogsOnly and worklogsWorkItems enpoints, this endpoint (and workItemsHierarhy, workItemsHieararchyAnyLevel) provides you with a way to retrieve time data already aggregated by work items. 

The workItems endpoint displays all the standard fields of work items and the ability to get any custom field created in work items.

Further, this endpoint provides information as a flat object with combined information from DevOps and Timetracker, extending work item objects with the following fields from Timetracker:

• TrackedItself - sum of time for all related worklogs
• TrackedItselfBillable - sum of Billable time for all related worklogs
• BudgetAssignmentType - type of budget assignment for this work item
• Budget - budget or empty
• Parent - optional property containing parent work item, if present, click here on how to retrieve linked optional properties in Timetracker Reporting API
• CustomStringField1-5, CustomNumericField1-5 and CustomBooleanField1-5 can be used to retrieve non-standard fields from work items, please click here for details. 

Usage scenarios:

1. Get all work items that have time tracked on them:
   

   /workItems?$filter=TrackedItself gt 0

2. Get a summary on an Iteration: how many hours tracked on a specific iteration:

   /workItems?$filter=TrackedItself gt 0 and System_IterationPath eq 'Project Name/Iteration Path'

 workItemsHierarchy / workItemsHierarchyAllLevels 

Both workItemsHierarchy and workItemsHierarchyAllLevels provide information similar to workItems endpoint, but allows you to also get information on Parent-Children.

The key differences between workItemsHierarchy and workItemsHierarchyAllLevels are the following: 

• workItemsHierarchy by default returns only items that do not have parents (e.g. Epics) and it is possible to navigate to children through parents. 
• *workItemsHierarchyAllLevels returns data irrelevant of the Parent-Children hierarchy, so it can return the Parent and its Child in the same response, on the same level, if the user doesn’t apply the correct filtering. It is up to the API consumer to provide the correct filtering, e.g. by work item type (see samples, below).      Please note: *For this endpoint, workitemsFilter may be required for some actions. If you see an error, it may be because your query doesn't contain the required workItemsFilter parameter. To add that parameter, follow the steps, below:

1. Select workItemsHierarchyAllLevels and click the Load button.

​​

2. Double-click on the query to open the Power Query Editor.

​​

3. Click Advanced Editor on the top-left part of the window.

​​

4. Change the script by adding the workItemsFilter field like on the screenshot, below.

​
​
5. Click Done.

Similar to workItems object, the work item is extended with fields from Timetracker. It has all fields that workItems provides, along with the following additions:

• TrackedTotal - summary of all tracked time against the item and all children work items
• TrackedTotalBillable - summary of all billable time against the item and all children work items
• HasChildren - flag identifying that specific work item has children items
• Parent - optional property containing parent work item, if present
• RollupValue1-RollupValue5 - set of up to 5 fields allowing to roll-up numeric values to the work item from all children, e.g. rollup Effort or Story Points. Click here for more details. 

To identify whether the item has children items, HasChildren is used and a call to the Children path:

/workItemsHierarchy(x)/Children

Sample usage:

1. Get a report for Epic work items that have time within a specific period, add summary for Effort as an additional field, and order by tracked time, descending:
   

   /workItemsHierarchy?$select=System_Id,System_WorkItemType,System_TeamProject,
   System_Title,TrackedTotal,TrackedItself,RollupValue1,
   System_State,System_AreaPath,System_AssignedTo,HasChildren
   &$filter=TrackedTotal gt 0&$orderby=TrackedTotal desc
   &rollupFields=Microsoft.VSTS.Scheduling.Effort

2. Get a report for all Product Backlog Items and Bugs for a specific period (from 3.1 API version):
   

   /v3.1/workItemsHierarchyAllLevels?$select=System_Id,System_WorkItemType,System_TeamProject,System_Title,TrackedTotal,TrackedItself,System_State,
   System_AreaPath,System_AssignedTo,HasChildren,Microsoft_VSTS_Scheduling_Effort
   &$filter=TrackedTotal gt 0&workItemsFilter=System_WorkItemType eq 'Product Backlog Item' or System_WorkItemType eq 'Bug'

This endpoint provides data related to Timetracker budgets. 

If you need to integrate to other systems based on Budgets, including related Work Items and Worklogs, this is the endpoint you should choose.

Usage scenarios:

1. Get overview of all budgets

   /budgets

Expanding / Retrieving linked objects

Some properties or linked objects can be retrieved by requesting specific paths or providing $expand parameters. This applies to workItems / workItemsHieararchy / workItemsHierarchyAllLevels endpoints.

Parent expanding

Retrieves the Parent item of all work items returned by the API.

(In old API versions it might be called ParentItem)

Sample:

/workItems?$expand=Parent&$select=System_Id,Parent

Parent endpoint

Retrieves the Parent of specific work items (x is the ID of work item). The endpoint is available in API v3.1 and later:

workItems(x)/Parent

Children

Endpoint provides access to the information of direct children work items.

Sample:

/workItems(x)/Children

DirectWorklogs / AllWorklogs

Returns worklogs related only to that specific work item (DirectWorklogs) or all worklogs from the item itself and all children (AllWorklogs). AllWorklogs endpoint is available in API v3.1 and later:

Sample:

/workItems(x)/DirectWorkLogs

/workItems(x)/AllWorklogs

Timetracker OData Query Extended Parameters

• worklogsFilter for filtering worklogs before executing queries
• workItemsFilter for filtering work items before executing queries
• customFields for including own fields into response
• rollupFields for adding summary of specific fields
• prefilter (filter by Me)

We covered topics, above, that detailed how to get information on work items and pure worklogs. 

When retrieving data by work items, you might need to get work items time tracked to those work items, during a specific timeframe and/or by a specific person, Activity Type, etc.

Timetracker provides extension syntax for filtering worklogs and work items in any of the endpoints listed above.

As additional parameter types are not part of the OData syntax, they do not have $-sign prefix.

Note: All filters use OData syntax; see syntax description here.

worklogsFilter

Use this filter to limit worklogs returned by the API. This filter should be applied first in the processing of the query, with all other filters applied after it, including workItemsFilter.

How worklogsFilter is applied, only green Worklogs will be processed

Example:

Let’s assume we need to pull a report on "How much time was spent on an Epic during a specific timeframe?". The following query will return all Epics, but it will not filter worklogs by date:

/workItemsHierarchy/?$select=System_Id,System_WorkItemType,System_TeamProject,System_Title,
TrackedTotal,TrackedItself
&$filter=System_WorkItemType eq 'Epic'

To achieve our goal we should define worklogsFilter in the request:

/workItemsHierarchy/?$select=System_Id,System_WorkItemType,System_TeamProject,System_Title,
TrackedTotal,TrackedItself
&$filter=System_WorkItemType eq 'Epic'
&worklogsFilter=Timestamp ge 2019-09-30T00:00:00Z and Timestamp lt 2019-11-05T00:00:00Z

The filter parameter ...

worklogsFilter=Timestamp ge 2019-09-30T00:00:00Z and Timestamp lt 2019-11-05T00:00:00Z

... will make the API return TotalTracked and TrackedItself for work items computed only by workLogs between 30 September and 5 November 2019. 

workItemsFilter

Similar to worklogsFilter, this filter applies to the work items that will be processed by the API call. The filter is applied after worklogsFilter.

How workItemsFilter is applied, only green Worklogs and Work Items will be processed

Note: This filter is applied after the worklogsFilter

Example:

We want to build a report on “How much time was spent on Project A”.
The following query may help:

/worklogsOnly?$apply=aggregate(PeriodLength with sum as PeriodLength)

This will return the sum of all projects, not just Project A, so to filter by Project A _before_ aggregating, we should use a workItemsFilter:

/worklogsOnly?$apply=aggregate(PeriodLength with sum as PeriodLength)
&workItemsFilter=System_TeamProject eq 'Project A'

Now, the number will be computed only by work items from Project A.

workItemsFilter has an additional parameter: workItemFilterApplyTarget

This parameter accepts following values:

• AllWorkItems (default value): when this value is provided, the workItemsFilter will be applied to all work items
• RootWorkItems: when this value is provided, the workItemsFilter will be applied only to top-level work items (items that do not have parents)

customFields

Displays any custom string field for workItems
Endpoints: workItems; workItemsHierarchy
How to use: Specify up to five (5) custom field names for each data type (string, numeric, boolean), separated by a comma in the select part of your query or the OData field in our Reporting Widget. They will appear in:
CustomStringField1...CustomStringField5 fields in result objects for string fields
CustomNumericField1...CustomNumericField5 fields in result objects for numeric fields
CustomBooleanField1...CustomBooleanField5 fields in result objects for boolean fields

7pace Timetracker detects the type of passed field and puts the value in accordingly. All fields are sequentially numbered (e.g. when you select custom fields with different data types, as described in the example below).
Please note that it takes up to 24 hours for DevOps custom fields to appear in Timetracker reporting.

Example:

Let's say you have 'ProjectCode', 'Custom.Finance' and 'AdditionalField.Order' custom fields in your work items. You can add a parameter this way:

/workItemsHierarchy?$select=System_Id,System_Title,TrackedTotal,CustomStringField1,CustomStringField2,CustomNumericField3&customFields=ProjectCode,Custom.Finance,AdditionalField.Order

To find the correct name of your custom field you will have to navigate to your Organization Settings Processes and open the Process where the custom field is located. Once there, select the field you wish to use and check Field Reference Name under the field Options.

"CustomStringField1": "648521",
"CustomStringField2": "Finance 2",
"CustomNumericField3": 3

To use our customFields/rollupFields parameters in Excel or Power BI:

1. Double click on the query to open Power Query Editor.

2. Click Advanced Editor in the top-left part of the window.

3. Add customFields/rollupFields parameter using "Query" statement with the syntax from the following screenshot and save the query.

Field reference name for pt.3, above, can be found when editing a field in process customization settings in DevOps (Organization Settings -> Process -> Select process -> Select Work Item Type -> Edit field).

rollupFields

Allows you to get roll-up summary fields that contain the sum of specified numeric field values from all children items, including the current item.
endpoints: workItemsHierarchy
How to use: specify up to five (5 ) roll-up field name,s separated by comma. They will appear in
RollupValue1 ... RollupValue5 fields in result objects.

Example:

Let's say you use the 'Effort' field in work items. If you'd like to sum efforts of an item and all its child items, you can add a parameter this way:

/workItemsHierarchy?rollupFields=Microsoft.VSTS.Scheduling.Effort

You'll get work items with fields such as:

"RollupValue1": 47,

prefilter (filter by Me)

If you'd like to see just data related to your account only, you can use the query parameter prefilter=Me.
In this case, all data will be filtered by an authorized account that performs a query.
Default parameter value is prefilter=NoFilter (not required).

Using POST Requests

The query options part of an OData URL can be quite long, potentially exceeding the maximum length of URLs supported by components involved in transmitting or processing the request.

For POST requests append /$query to the resource path of the URL and pass the query options part of the URL in the request body. The request body must use the content-type text/plain.

Usage scenarios:

1 Report: "How many hours did every user add into the system during a specific period?"

Sample query for GET request:

/workLogsOnly?$apply=filter(Timestamp ge 2019-11-01T00:00:00Z and Timestamp lt 2019-12-01T00:00:00Z)
/groupby((User/Name),aggregate(PeriodLength with sum as PeriodLength))
&$orderby=User/Name

Sample query for POST request:

/workLogsOnly/$query

Headers:

Content-Type: text/plain

Body:

apply=filter(Timestamp ge 2019-11-01T00:00:00Z and Timestamp lt 2019-12-01T00:00:00Z)
/groupby((User/Name),aggregate(PeriodLength with sum as PeriodLength))
&$orderby=User/Name

2 Report: "Team personal time per Project"

Sample query for GET request:

/workLogsWorkItems?$apply=groupby((User/Name,WorkItem/System_TeamProject,WorklogDate/ShortDate),
aggregate(PeriodLength with sum as PeriodLength))
&$orderby=WorklogDate/ShortDate desc

Sample query for POST request:

/workLogsWorkItems/$query

Headers:

Content-Type: text/plain

Body:

$apply=groupby((User/Name,WorkItem/System_TeamProject,WorklogDate/ShortDate),
aggregate(PeriodLength with sum as PeriodLength))
&$orderby=WorklogDate/ShortDate desc

Budget Information in Worklogs and Work Items

{
   "chartSettings": {
       "strokeWidth": number,
       "strokeColor": string, - //for example "#38ACEC"
       "backgroundColor": string, - //for example "#38ACEC"
   },
   "xAxis": {
       "type": "date" or "value" or "category" or "duration",
       "title": string,
       "rotation": number, - //Rotation of the axis title in degrees
       "granularity": "minute" or "hour" or "day" or "week" or "month" or "year", - //Used to indicate what are the base units of your data.
       "toolTipDisabled": boolean,
       "minGridDistance": number, - //Minimum distance in pixels between grid elements.
       "durationFormat": string,
       "minValue": number, - //A minimum value for the axis scale. (Only for "duration" axios type)
   },
   "yAxis": {
       "type": "date" or "value" or "category" or "duration",
       "title": string,
       "rotation": number, - //Rotation of the axis title in degrees
       "granularity": "minute" or "hour" or "day" or "week" or "month" or "year", - //Used to indicate what are the base units of your data.
       "toolTipDisabled": boolean,
       "minGridDistance": number, - //Minimum distance in pixels between grid elements.
       "durationFormat": string,
       "minValue": number, - //A minimum value for the axis scale. (Only for "duration" axios type)
   },
   "series": {
       "fieldY": string,
       "fieldX": string
   },
   "colorField": string, - //field name where stored color value
   "dataAdapters": [
{
           "name": "NullToComment" or "YearMonthToDate" or "NotSetActivityTypeFormat" or "StrokeForLightColor" or "SumOverPeriod", "FillMissingDatePoints", "AggregateDataSetsByMutualField"
           "params": string
         }
   ]
}

{
   "chartSettings": {
       "backgroundColor": string, - //for example "#38ACEC"
   },
   "xAxis": {
       "type": "date" or "value" or "category" or "duration",
       "title": string,
       "rotation": number, - //Rotation of the axis title in degrees
       "granularity": "minute" or "hour" or "day" or "week" or "month" or "year", - //Used to indicate what are the base units of your data.
       "toolTipDisabled": boolean,
       "minGridDistance": number, - //Minimum distance in pixels between grid elements.
       "durationFormat": string,
       "minValue": number, - //A minimum value for the axis scale. (Only for "duration" axios type)
   },
   "yAxis": {
       "type": "date" or "value" or "category" or "duration",
       "title": string,
       "rotation": number, - //Rotation of the axis title in degrees
       "granularity": "minute" or "hour" or "day" or "week" or "month" or "year", - //Used to indicate what are the base units of your data.
       "toolTipDisabled": boolean,
       "minGridDistance": number, - //Minimum distance in pixels between grid elements.
       "durationFormat": string,
       "minValue": number, - //A minimum value for the axis scale. (Only for "duration" axios type)
   },
   "series": {
       "fieldY": string,
       "fieldX": string
   },
   "colorField": string, - //field name where stored color value
   "dataAdapters": [
         {
           "name": "NullToComment" or "YearMonthToDate" or "NotSetActivityTypeFormat" or "StrokeForLightColor" or "SumOverPeriod", "FillMissingDatePoints", "AggregateDataSetsByMutualField"
           "params": string - //parameters depends on adapter type
         }
   ]
}

{
   "chartSettings": {
      "backgroundColor": string, - //for example "#38ACEC"
  },
   "xAxis": {
       "type": "date" or "value" or "category" or "duration",
       "title": string,
       "rotation": number, - //Rotation of the axis title in degrees
       "granularity": "minute" or "hour" or "day" or "week" or "month" or "year", - //Used to indicate what are the base units of your data.
       "toolTipDisabled": boolean,
       "minGridDistance": number, - //Minimum distance in pixels between grid elements.
       "durationFormat": string,
       "minValue": number, - //A minimum value for the axis scale. (Only for "duration" axios type)
       "opposite": boolean - //Indicates whether Axis should be drawn on the opposite side of the plot area than it would normally be drawn based on chart's settings.
   },
   "yAxes": [{
       "type": "date" or "value" or "category" or "duration",
       "title": string,
       "rotation": number, - //Rotation of the axis title in degrees
       "granularity": "minute" or "hour" or "day" or "week" or "month" or "year", - //Used to indicate what are the base units of your data.
       "toolTipDisabled": boolean,
       "minGridDistance": number, - //Minimum distance in pixels between grid elements.
       "durationFormat": string,
       "minValue": number, - //A minimum value for the axis scale. (Only for "duration" axios type)
  }],
   "series": {
       "fieldY": string, - //field name for category in X axis
       "fieldX": string, - //field name for category in Y axis
       "seriesFields": [
         {
           "field": string,
           "stacked": boolean,
           "axis": string,
           "title": string
         }
       ]
  },
  "dataAdapters": [
         {
           "name": "NullToComment" or "YearMonthToDate" or "NotSetActivityTypeFormat" or "StrokeForLightColor" or "SumOverPeriod", "FillMissingDatePoints", "AggregateDataSetsByMutualField"
           "params": string - //parameters depends on adapter type
         }
   ]
}

{
   "chartSettings": {
      "name": string, - //series name
      "innerRadius": number, - //donut circle inner radius,
      "radius": number, - //donut circle outer radius
      "disableTitle": bool, - //define if show title in chart for each segment
      "disableTicks": bool, - //should ticks be disabled
      "disableLabels": bool, - //define if show labels
      "labelsMaxWidth": number - //maximum label width in chart
      "tooltip": string, - //tooltip text
      "showPercent": bool, - //if show percent or just values
      "units": string - //units name
  },
   "categoryField": string, - //field name where to put data from for category
   "valueField": string, - //field name where to put data from for value
   "valueFormat": string - //format string
   "colorField": string, - //field name where stored color value
   "dataAdapters": [
         {
           "name": "NullToComment" or "YearMonthToDate" or "NotSetActivityTypeFormat" or "StrokeForLightColor" or "SumOverPeriod", "FillMissingDatePoints", "AggregateDataSetsByMutualField"
          "params": string - //parameters depends on adapter type
         }
   ]
}

{
   "showCount": boolean, - //if true widget will show not the data itself, but their number
   "formatter": string, - //formatter name to format number
   "fieldName": string - //field name where to get data from
}

{
  "columns": [{
        "field": string || string[], - //field name can be as just string as array.
        "title": string, - //column title
        "formatter": string, - //formatter name to format column
        "width": number - //column width
     }
  ]
}

{
"hierarchyField": string, -// parentId to filter children query
"hierarchyType": string, -// criteria to expand children items
  "columns": [{
       "field": string || string[], - //field name can be as just string as array.
       "title": string, - //column title
       "formatter": string, - //formatter name to format column
       "width": number - //column width
       "expand": true, - //if column expandable
       "child": {
        "field": "yearMonth" //expand field to show
      }
    }
 ]
}

{
 "columns": [
 ...,
 {
   "field": "TrackedItself",
   "title": "Tracked",
   "width": 100,
   "formatter": "TimeLength"
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_AssignedTo",
   "title": "Assigned To",
   "formatter": "NullToComment",
   "width": 150
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_Id",
   "title": "ID",
   "formatter": "Number",
   "format": "#,###.00",
   "width": 100
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_AssignedTo",
   "title": "Assigned To",
   "formatter": "String",
   "width": 150
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_Id",
   "title": "ID",
   "formatter": "WorkItemId",
   "width": 100
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": [
     "System_WorkItemType",
     "System_TeamProject"
 ],
 "title": "Type",
 "formatter": "WorkItemType",
 "width": 100
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": [
     "System_Title",
     "System_WorkItemType",
     "System_TeamProject"
 ],
 "title": "Title",
 "formatter": "WorkItemIndicator",
 "width": 100
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
 "field": [
    "System_Title",
    "System_WorkItemType",
    "System_TeamProject"
 ],
 "title": "Title",
 "formatter": "WorkItemIdWithIndicator",
 "width": 100
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_CreatedDate",
   "title": "Date",
   "formatter": "LocaleSettingsDateTime",
   "width": 200
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_CreatedDate",
   "title": "Date",
   "formatter": "CustomDateTime",
   "format": "DD.MM.YYYY H:mm",
   "width": 200
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
   "field": "System_CreatedDate",
   "title": "Date",
   "formatter": "DateTimeAgo",
   "width": 200
 },
 ...
 ]
}

{
 "columns": [
 ...,
 {
    "field": [
       "TrackedTotal", // {0}
       "Microsoft.VSTS.Scheduling.Effort" // {1}
     ],
    "title": "Pace",
    "width": 80,
    "formatter": "MathExpression",
    "format": {
       "expression": "{0} / 3600 / {1}",
       "resultFormat": "#.#"
    }
},
 ...
 ]
}

"""
This module accesses the 7pace time tracking Reporting API 
​
Example usage:
​
.. code-block:: python
​
 7pace_time_test.py
"""
import argparse
import base64
import json
import requests
​
​
def main(options):
 authentication = ('', '<your API token>')
 headers = {'$select': 'System_Id,System_WorkItemType,System_TeamProject,System_Title,TrackedTotal,TrackedItself,RollupValue1,System_State,System_AreaPath,System_AssignedTo,HasChildren', '$filter': 'TrackedTotal gt 0', '$orderby': 'TrackedTotal desc', 'rollupFields': 'Microsoft.VSTS.Scheduling.StoryPoints'}
 response = requests.get('<YOUR API ROOT>/workItemsHierarchy', auth=authentication, headers=headers).json()
 print (response)
if __name__ == '__main__':
 # pylint: disable=invalid-name
 parser = argparse.ArgumentParser(description='Access the 7pace time tracking REST API')
​
 main(parser.parse_args())

var request = require('request');

var options = {
 'method': 'GET',
 //this is sample request, use your query here
 'url': 'https://%ORG_NAME%.timehub.7pace.com/api/odata/v3.0/worklogsWorkItems?$top=1&$select=WorkItem/System_Id,Id',
 'headers': {
 'Authorization': 'Bearer %TOKEN%' //token is generated on Settings -> Reporting & REST API
 }
};
request(options, function (error, response) { 
 if (error) throw new Error(error);
 console.log(response.body);
});

app.get((req, res) => { 

  var httpntlm = require('httpntlm');

  httpntlm.get({ 
    url: "[your_API_URL_with_query]", 
    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); 
  }); 
});

let

    Source = OData.Feed("https://[your_organization_name].timehub.7pace.com/api/odata/v3.1/workLogsWorkItems?$orderby=Timestamp desc&$top=100&$select=EditedTimestamp,WorkItemId,Timestamp,PeriodLength,User,ActivityType,WorkItem/System_Title,WorkItem/System_WorkItemType,WorkItem/System_TeamProject,Comment", null, [Implementation="2.0"])

in

    Source

let

Parameter = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content], // Report parameters table name

Param_StartDate = Parameter{0}[Parameter Values], // First table row: Start Date

Param_EndDate = Parameter{1}[Parameter Values], // Second table row: End Date

Source = OData.Feed("https://[your_organization_name].timehub.7pace.com/api/odata/v3.1/workLogsWorkItems?$select=WorkItemId,Timestamp,PeriodLength,User,ActivityType,WorkItem/System_Title,EditedTimestamp&workLogsFilter=EditedTimestamp gt "& Param_StartDate & " and EditedTimestamp lt "& Param_EndDate, null, [Implementation="2.0"])
in
Source