Postman collection

Find a collection of the above custom widget queries and additional query examples for Postman

In order to be able to import our Postman collection, you must have a public Postman account. When importing, you should choose the "Fork" option, which will create a copy of the collection in your account, which you can then use its requests and make changes to without affecting the original. You can also choose to subscribe to updates to the original collection by checking "Watch original collection".

How to fork the Postman collection data to your workspace:

1. Open the following link:
   https://go.postman.co/workspace/Team-Workspace~36b01b3c-6ff9-4bd8-892a-1dd3367f07cc/collection/20406970-6c9f851b-19fb-40fc-9adb-d6cd0eded530?action=share&creator=20583389

2. Click

1. OData collection

1. .

2. Click

1. Fork

1. .

   Image Modified

2. Choose the workspace where the collection should be stored.
   

   Image Modified

The current collection has several variables which you should change according to your chosen environment:

Image Added

Reporting Widgets Gallery

Work Items Activity

This donut widget displays work items' tracked time grouped by the DevOps' "Activity" field (Microsoft.VSTS.Common.Activity).

 To learn more about how this field is used, see Capacity planning.

Image Modified

{"valueField":"PeriodLength","categoryField":"WorkItem.Microsoft_VSTS_Common_Activity","id":"","query":"$apply=groupby((WorkItem/Microsoft_VSTS_Common_Activity),aggregate(PeriodLength

with

sum

as

PeriodLength))&$orderby=WorkItem/Microsoft_VSTS_Common_Activity","endpoint":"workLogsWorkItems","type":"Pie","title":"Activity","subTitle":null,"w":4,"h":2,"chartSettings":{"backgroundColor":"#104547","showLegend":true,"name":"","innerRadius":30,"disableTicks":false,"disableTitle":false},"labelRadius":10,"autoSubtitle":true,"timeframeFilter":null,"y":20,"x":0,"valueFormat":"duration","":"","odataVersion":"v3.2"}

Work Items with Estimations

This drill-down table provides a list of work items (all levels) with estimations (Effort, Original Estimate, Completed Work, Remaining Work) as roll-up fields. To learn more about how roll-up fields work in reporting, see Reporting API Overview.

Image Modified

{"type":"HierarchyTable","id":"","title":"Work

Items

with

estimations","subTitle":"","w":8,"h":4,"endpoint":"workItemsHierarchy","query":"$select=System_Id,System_WorkItemType,System_TeamProject,System_Title,TrackedTotal,TrackedItself,RollupValue1,RollupValue2,RollupValue3,RollupValue4,System_State,System_AreaPath,System_AssignedTo,HasChildren&$filter=TrackedTotal

gt

0&$orderby=TrackedTotal

desc&rollupFields=Microsoft.VSTS.Scheduling.Effort,

Microsoft.VSTS.Scheduling.OriginalEstimate,

Microsoft.VSTS.Scheduling.RemainingWork,

Microsoft.VSTS.Scheduling.CompletedWork","hierarchyField":"System_Id","childrenQuery":"({hierarchyField})/Children?{query}","columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-1,"expand":true},{"field":"System_State","title":"State","width":80},{"field":"System_AreaPath","title":"Area","width":100},{"field":"System_AssignedTo","title":"Assigned

To","width":140},{"field":"TrackedTotal","title":"Tracked","width":100,"formatter":"TimeLength"},{"field":"RollupValue1","title":"Effort","width":60,"formatter":""},{"field":"RollupValue2","title":"Original

Estimate","width":60},{"field":"RollupValue3","title":"RemainingWork","width":80,"formatter":"Number","format":"#.#"},{"field":"RollupValue4","title":"CompletedWork","width":80,"formatter":"Number","format":"#.#"}],"autoSubtitle":false,"timeframeFilter":null,"childrenEndpoint":"workItemsHierarchy","y":6,"x":0,"odataVersion":"v3.2"}

Work items filtered by

tags

There is a possibility to tag work items:

Image Modified

Our reporting feature supports filtration by these tags. Here is an example of the Work Item table filtered by the tag 'capex'.

Image Modified

{"id":"","query":"$select=TrackedItself,System_Id,System_Title,System_TeamProject,System_WorkItemType,System_AreaPath,System_State,System_AssignedTo,CustomStringField1&customFields=System.Tags&$filter=contains(CustomStringField1,

'capex')&$orderby=TrackedItself

desc&$top=30","endpoint":"workItems","type":"FlatTable","title":"Top

30

Work

Items

this

Month","subTitle":null,"w":8,"h":3,"columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100},{"field":"System_State","title":"State","width":100},{"field":"System_AreaPath","title":"Area","width":100},{"field":"System_AssignedTo","title":"Assigned

To","width":150},{"field":"TrackedItself","title":"Tracked","width":100,"formatter":"TimeLength"},{"field":"CustomStringField1","title":"Tags","width":100,"formatter":""}],"autoSubtitle":false,"timeframeFilter":null,"y":23,"x":0,"odataVersion":"v3.2"}

Here is also an example of a number widget. It contains the total tracking time count filtered by tag 'capex':

Image Modified

{"id":"","query":"$apply=filter(contains(CustomStringField1,

'capex'))/aggregate(TrackedItself

with

sum

as

PeriodLength)&customFields=System.Tags","endpoint":"workItems","type":"Number","title":"Capex

time

this

month","subTitle":"","fieldName":"PeriodLength","formatter":"TimeLength","w":2,"h":1,"timeframeFilter":{"from":null,"to":null,"filterType":"Month"},"autoSubtitle":true,"y":27,"x":0,"odataVersion":"v3.2"}

For more information on capex reporting, check out our webinar: Software Development Capitalization - Increasing Percentage of Work that is Capitalized & Reporting Tips

Latest

worklogs

This flat table provides a list of your latest 100 workLogs for the selected date period.

It is recommended for the "Personal Overview" report.

Image Modified

{"type":"FlatTable","id":"","query":"$orderby=Timestamp

desc&$top=100&$select=WorkItemId,Timestamp,PeriodLength,User,ActivityType,WorkItem/System_Title,WorkItem/System_WorkItemType,WorkItem/System_TeamProject,Comment","endpoint":"workLogsWorkItems","title":"My

workLogs","subTitle":"Latest

100","w":8,"h":4,"columns":[{"field":"WorkItemId","title":"Work

Item

Id","formatter":"WorkItemId","width":100},{"field":["WorkItem.System_Title","WorkItem.System_WorkItemType","WorkItem.System_TeamProject"],"title":"Work

Item

Title","width":-200,"formatter":"WorkItemIndicator"},{"field":["Comment"],"title":"Comment","width":-100},{"field":"ActivityType.Name","title":"Activity

Type","width":-100},{"field":"Timestamp","title":"Date","width":-100,"formatter":"DateTimeAgo"},{"field":"PeriodLength","title":"Tracked","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":29,"x":0,"odataVersion":"v3.2"}

Work Items with Pace

This drill-down

table provides a list of work items (all levels) with a calculated

pace based on Effort/Original Estimate/Story Point as the roll-up field. 
To learn more about how to add calculated columns with "MathExpression" formatter, see Reporting Api Overview - MathExpression
To learn more about how roll-up fields work in reporting, see Reporting API Overview - rollup fields.

Image Modified

Work

Items with Pace based on Effort

{"type":"HierarchyTable","id":"","title":"Work

Items

Pace","subTitle":"","w":8,"h":4,"endpoint":"workItemsHierarchy","query":"$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","hierarchyField":"System_Id","childrenQuery":"({hierarchyField})/Children?{query}","columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100,"expand":true},{"field":"System_State","title":"State","width":80},{"field":"System_AreaPath","title":"Area","width":80},{"field":"System_AssignedTo","title":"Assigned

To","width":130},{"field":"TrackedTotal","title":"Tracked","width":80,"formatter":"TimeLength"},{"field":"RollupValue1","title":"Effort","width":80},{"field":["TrackedTotal","RollupValue1"],"title":"Pace","width":80,"formatter":"MathExpression","format":{"expression":"{0}

/

3600

/

{1}","resultFormat":"#.#"}}],"autoSubtitle":false,"childrenEndpoint":"workItemsHierarchy","y":34,"x":0,"odataVersion":"v3.2"}

Work Items with Pace based on Original Estimate

{"type":"HierarchyTable","id":"","title":"Work

Items

Pace","subTitle":"","w":8,"h":4,"endpoint":"workItemsHierarchy","query":"$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.OriginalEstimate","hierarchyField":"System_Id","childrenQuery":"({hierarchyField})/Children?{query}","columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100,"expand":true},{"field":"System_State","title":"State","width":80},{"field":"System_AreaPath","title":"Area","width":80},{"field":"System_AssignedTo","title":"Assigned

To","width":130},{"field":"TrackedTotal","title":"Tracked","width":80,"formatter":"TimeLength"},{"field":"RollupValue1","title":"Original

Estimate","width":80},{"field":["TrackedTotal","RollupValue1"],"title":"Pace","width":80,"formatter":"MathExpression","format":{"expression":"{0}

/

3600

/

{1}","resultFormat":"#.#"}}],"autoSubtitle":false,"childrenEndpoint":"workItemsHierarchy","y":29,"x":0,"odataVersion":"v3.2"}

Work Items with Pace based on Story Points

{"type":"HierarchyTable","id":"","title":"Work

Items

Pace","subTitle":"","w":8,"h":4,"endpoint":"workItemsHierarchy","query":"$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","hierarchyField":"System_Id","childrenQuery":"({hierarchyField})/Children?{query}","columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100,"expand":true},{"field":"System_State","title":"State","width":80},{"field":"System_AreaPath","title":"Area","width":80},{"field":"System_AssignedTo","title":"Assigned

To","width":130},{"field":"TrackedTotal","title":"Tracked","width":80,"formatter":"TimeLength"},{"field":"RollupValue1","title":"Story

Points","width":80},{"field":["TrackedTotal","RollupValue1"],"title":"Pace","width":80,"formatter":"MathExpression","format":{"expression":"{0}

/

3600

/

{1}","resultFormat":"#.#"}}],"autoSubtitle":false,"childrenEndpoint":"workItemsHierarchy","y":27,"x":0,"odataVersion":"v3.2"}

Work Items Area Path

This donut widget displays work items' tracked time grouped by the DevOps "Area Path" field (System_AreaPath).

Image Modified

{"valueField":"PeriodLength","categoryField":"WorkItem.System_AreaPath","id":"","query":"$apply=groupby((WorkItem/System_AreaPath),aggregate(PeriodLength

with

sum

as

PeriodLength))","endpoint":"workLogsWorkItems","type":"Pie","title":"Area

Path","subTitle":null,"w":4,"h":2,"chartSettings":{"backgroundColor":"#104547","showLegend":true,"name":"","innerRadius":30,"disableTicks":false,"disableTitle":false},"labelRadius":10,"autoSubtitle":false,"timeframeFilter":null,"y":32,"x":0,"valueFormat":"duration","":"","odataVersion":"v3.2"}

Team Time per Project

This flat table provides daily tracked time summary per person per

project.

Image Modified

{"id":"","query":"$apply=groupby((User/Name,WorkItem/System_TeamProject,WorklogDate/ShortDate),aggregate(PeriodLength

with

sum

as

PeriodLength))&$orderby=WorklogDate/ShortDate

desc","endpoint":"workLogsWorkItems","type":"FlatTable","title":"Team

Time

per

Project","subTitle":null,"w":5,"h":3,"columns":[{"field":"User.Name","title":"Person","width":-100},{"field":"WorkItem.System_TeamProject","title":"Project","width":-100,"formatter":"NullToComment"},{"field":"WorklogDate.ShortDate","title":"Date","width":-100,"formatter":"CustomDateTime","format":"DD.MM.YYYY"},{"field":"PeriodLength","title":"Tracked

(h)","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":35,"x":0,"odataVersion":"v3.2"}

Time per Project

This flat table provides simple tracked time summary per project.

Image Modified

{"id":"","query":"$apply=groupby((WorkItem/System_TeamProject),aggregate(PeriodLength

with

sum

as

PeriodLength))&$orderby=PeriodLength","endpoint":"workLogsWorkItems","type":"FlatTable","title":"Time

per

Project","subTitle":null,"w":3,"h":2,"columns":[{"field":"WorkItem.System_TeamProject","title":"Project","width":-100,"formatter":"NullToComment"},{"field":"PeriodLength","title":"Tracked

(h)","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":39,"x":0,"odataVersion":"v3.2"}

Budgets

This flat table provides budgets information. It's not affected by any filtering.

Image Modified

{"type":"FlatTable","id":"","query":"

$orderby=BudgetName","odataVersion":"v3.2","endpoint":"budgets","title":"Budgets","subTitle":"","w":3,"h":2,"columns":[{"field":"BudgetName","title":"Name","width":-100},{"field":"Billed","title":"Billable","width":90,"formatter":"TimeLength"},{"field":"Tracked","title":"Tracked","width":90,"formatter":"TimeLength"},{"field":"Planned","title":"Planned","width":90,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":53,"x":0}

Time per Custom Work Item Field (InternalProject in this example)

This widget displays a pie chart of the time spent on any given project based on a custom field set by the user at the Work Item level. Each slice represents a value in such a field. For example, a user may want to add a new field to store project names, departments, or categories.

Image Modified

{"type":"Pie","valueField":"PeriodLength","categoryField":"WorkItem.CustomStringField1","id":"","query":"$apply=groupby((WorkItem/CustomStringField1),aggregate(PeriodLength

with

sum

as

PeriodLength))&$orderby=WorkItem/CustomStringField1&customFields=Custom.InternalProject","odataVersion":"v3.2","endpoint":"worklogsWorkItems","title":"My

Internal

Projects","subTitle":null,"w":4,"h":3,"valueFormat":"duration","chartSettings":{"showLegend":true,"name":"","innerRadius":30,"disableTicks":true},"autoSubtitle":false,"timeframeFilter":null,"":"","y":20,"x":0}

Tracked Time per Person in a Bar Chart

This widget shows the total amount of time tracked by each person in an organization. The time

range can be set using the filters on the Dashboard UI. It is possible to exclude the time logged against a specific Activity Type from the calculation, which is achieved by setting the parameter {$filter=(ActivityType/Name ne 'placeholder')} to the desired value. In this example, the time logged against ‘Testing’ is not displayed on the chart ($filter=(ActivityType/Name ne 'Testing')). Just remove the whole filter to display the total time tracked across all Activity Types. 

Image Modified

{"type":"Columns","title":"All

activities

except

Testing

by

person","subTitle":"","id":"","query":"$apply=groupby((ActivityType/Name,User/Name),aggregate(PeriodLength

with

sum

as

PeriodLength))&$filter=(ActivityType/Name

ne

'Testing')","odataVersion":"v3.2","endpoint":"workLogsWorkItems","w":8,"h":3,"chartSettings":{"showLegend":true,"name":""},"xAxis":{"type":"category","title":"Person","rotation":90,"granularity":"hour"},"yAxis":{"type":"duration","title":"Tracked,

hours"},"series":{"fieldY":"PeriodLength","fieldX":"User.Name"},"dataAdapters":[{"name":"FillMissingDatePoints","params":"PeriodLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":36,"x":0}

Shortfalls per Person per Period

This table shows the difference (shortfall) between the total amount of hours a person is expected to log within a given date-range, minus the amount of hours they actually logged.

For example, if during a given week, a person logs time each day from Monday to Friday, the value of ‘Days Tracked’ for that week will be 5. If they only track 30 hours in total, their shortfall will be 10 hours (5 days tracked * 8 hours a day - 30 hours logged) . If the shortfall value is negative, this means that the amount of time tracked exceeds the expected total (that person has logged more hours than they were expected). Note that in the example, the assumption is that a person is expected to log 8 hours a day, which amounts to 28800 seconds. This can be changed by changing "format":{"expression":"({0}*28800 - {1}) to the desired value. The time-range can be set using the UI on the Dashboard.

Image Modified

{"type":"FlatTable","id":"","query":"$apply=groupby((User/Name),aggregate(PeriodLength

with

sum

as

PeriodLength,

WorklogDate/ShortDate

with

countdistinct

as

DaysTracked))","endpoint":"worklogsOnly","title":"Shortfalls","subTitle":null,"w":4,"h":3,"columns":[{"field":"User.Name","title":"Person","width":-100},{"field":"DaysTracked","title":"Days

Tracked","formatter":"","width":120},{"field":"PeriodLength","title":"Tracked","formatter":"MathExpression","format":{"expression":"{0}/

3600","resultFormat":"#.##"},"width":120},{"field":["DaysTracked","PeriodLength"],"title":"Shortfall","formatter":"MathExpression","format":{"expression":"({0}*28800

-

{1})

/

3600","resultFormat":"#.##"},"width":120}],"autoSubtitle":false,"timeframeFilter":null,"y":16,"x":0,"odataVersion":"v3.2"}

Individual Shortfalls per Day

This table is similar to the one

above

; however,

it shows the daily shortfall values for each individual person/day over a certain period

, which can be defined via the Dashboard.

Image Modified

{"type":"FlatTable","id":"","query":"$apply=groupby((User/Name,

WorklogDate/ShortDate),aggregate(PeriodLength

with

sum

as

PeriodLength))","endpoint":"worklogsOnly","title":"Shortfalls

By

Days","subTitle":null,"w":4,"h":3,"columns":[{"field":"User.Name","title":"Person","width":-100},{"field":"WorklogDate.ShortDate","title":"Date","formatter":"CustomDateTime","format":"DD.MM.YYYY","width":-100},{"field":"PeriodLength","title":"Tracked","formatter":"MathExpression","format":{"expression":"{0}/

3600","resultFormat":"#.##"},"width":120},{"field":"PeriodLength","title":"Shortfall","formatter":"MathExpression","format":{"expression":"(28800

-

{0})

/

3600","resultFormat":"#.##"},"width":120}],"autoSubtitle":false,"timeframeFilter":null,"y":19,"x":0,"odataVersion":"v3.2"}

Time Logged per Activity by Each Team Member

This table shows the total time logged per Activity by each team member for a given time period which can be set using the Dashboard UI.

Image Modified

{"id":"","query":"$apply=groupby((User/Name,ActivityType/Name),aggregate(PeriodLength

with

sum

as

PeriodLength))&$orderby=User/Name,PeriodLength

desc","endpoint":"worklogsOnly","type":"FlatTable","title":"Team

Activity

Type

Time","subTitle":"","w":4,"h":3,"columns":[{"field":"User.Name","title":"Person","width":-100},{"field":"ActivityType.Name","title":"Activity

Type","width":-100},{"field":"PeriodLength","title":"Tracked","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":44,"x":0,"odataVersion":"v3.2"}

Total Time Tracked per Iteration

This table shows the total time tracked against each Iteration, and compares it with the associated ‘Original Estimate’ and ‘Effort’ values as defined in DevOps. If such values are not displayed, this means that they were not set previously.

Image Modified

{"id":"","query":"$apply=groupby((System_IterationPath),aggregate(TrackedItself

with

sum

as

PeriodLength,

Microsoft_VSTS_Scheduling_Effort

with

sum

as

Effort,

Microsoft_VSTS_Scheduling_OriginalEstimate

with

sum

as

OriginalEstimate))&$orderby=System_IterationPath","endpoint":"workItems","type":"FlatTable","title":"By

Iteration","subTitle":"","w":4,"h":3,"columns":[{"field":"System_IterationPath","title":"Iteration","width":-100},{"field":"PeriodLength","title":"Tracked","width":100,"formatter":"TimeLength"},{"field":"OriginalEstimate","title":"Original

Estimate","width":100,"formatter":""},{"field":"Effort","title":"Effort","width":100,"formatter":""}],"autoSubtitle":false,"timeframeFilter":null,"y":22,"x":0,"odataVersion":"v3.2"}

Items Completed by Date Range

This widget shows a list of Product Backlog Items that were delivered (marked as ‘Done) within a given date-range. Information includes the person that each item is assigned to, the total time tracked against each item, as well as Effort and tags.

Image Modified

{"id":"","query":"$select=TrackedTotal,System_Id,System_Title,System_TeamProject,System_WorkItemType,System_State,System_AssignedTo,Microsoft_VSTS_Scheduling_Effort,CustomStringField1&$filter=TrackedTotal

gt

0

and

System_State

eq

'Done'&$orderby=TrackedTotal

desc&customFields=System.Tags&workItemsFilter=System_WorkItemType

in

('Bug',

'Product

Backlog

Item')","endpoint":"workItemsHierarchyAllLevels","type":"FlatTable","title":"Items

delivered

(Done)","subTitle":"","w":6,"h":3,"columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100},{"field":"System_AssignedTo","title":"Assigned

To","width":150},{"field":"TrackedTotal","title":"Tracked","width":70,"formatter":"TimeLength"},{"field":"Microsoft_VSTS_Scheduling_Effort","title":"Effort","width":60},{"field":"CustomStringField1","title":"Tags","width":150,"formatter":""}],"autoSubtitle":false,"timeframeFilter":null,"y":0,"x":0,"odataVersion":"v3.2"}

Items Not Completed During a Given Date Range

This widget shows a list of items that have time tracked against them within a given period, however, have not yet been updated to the ‘Done’ state. The period can be defined using the date picker on the UI. The items can be expended to see the state of their children items.

Image Modified

{"type":"HierarchyTable","id":"","title":"Items

not

delivered

(expand

children)","subTitle":null,"w":7,"h":3,"odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","query":"$select=System_Id,System_Title,System_TeamProject,System_WorkItemType,System_State,System_AssignedTo,TrackedTotal,TrackedItself,

Microsoft_VSTS_Scheduling_Effort,System_AreaPath,HasChildren,

CustomStringField1&$filter=TrackedTotal

gt

0

and

System_State

ne

'Done'&$orderby=TrackedTotal

desc&customFields=System.Tags&workItemsFilter=System_WorkItemType

in

('Bug',

'Product

Backlog

Item')","hierarchyField":"System_Id","childrenEndpoint":"workItemsHierarchy","childrenQuery":"({hierarchyField})/Children?{query}","columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100,"expand":true},{"field":"System_State","title":"State","width":100},{"field":"System_AssignedTo","title":"Assigned

To","width":150},{"field":"TrackedTotal","title":"Tracked","width":100,"formatter":"TimeLength"},{"field":"Microsoft_VSTS_Scheduling_Effort","title":"Effort","width":100,"formatter":""},{"field":"CustomStringField1","title":"Tags","width":150,"formatter":""}],"autoSubtitle":false,"timeframeFilter":null,"y":5,"x":0}

Total Story Points Delivered by Date Range

This widget shows the total amount of Story Points delivered within any given date range (the state of the associated Work Items should be set to ‘Done’).  The date-range should be set using the filters in the UI.

Image Modified

{"type":"Number","id":"","query":"$apply=filter(System_State

eq

'Done'

and

TrackedTotal

gt

0)/aggregate(Microsoft_VSTS_Scheduling_Effort

with

sum

as

Effort)&workItemsFilter=System_WorkItemType

in

('Bug',

'Product

Backlog

Item')","odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","title":"SP

delivered

(Done)","subTitle":null,"fieldName":"Effort","w":2,"h":1,"timeframeFilter":null,"autoSubtitle":false,"y":0,"x":6}

Total Story Points Delivered by Date Range with a Tag

This widget is identical to the one above, however, only the Work Items with a specific tag are taken into consideration for this calculation. This is defined by the contains(CustomStringField1, 'maintenance') parameter, ‘maintenance’ being the value used in our example.

Image Modified

{"type":"Number","id":"","query":"$apply=filter(System_State

eq

'Done'

and

TrackedTotal

gt

0

and

contains(CustomStringField1,

'maintainance'))/aggregate(Microsoft_VSTS_Scheduling_Effort

with

sum

as

Effort)&customFields=System.Tags&workItemsFilter=System_WorkItemType

in

('Bug',

'Product

Backlog

Item')","odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","title":"SP

delivered

'maintainance'

(Done)","subTitle":null,"fieldName":"Effort","w":2,"h":1,"timeframeFilter":null,"autoSubtitle":false,"y":4,"x":6}

Total Time Spent on Bugs

This widget shows the total amount of time spent on bug-fixing (Work Item Type = Bug) within any given date-range.  

Image Modified

{"type":"Number","id":"","query":"$apply=aggregate(TrackedTotal

with

sum

as

PeriodLength)&workItemsFilter=System_WorkItemType

in

('Bug')

and

System_TeamProject

ne

'Internal'","odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","title":"Bugs","subTitle":null,"fieldName":"PeriodLength","formatter":"TimeLength","w":2,"h":1,"timeframeFilter":null,"autoSubtitle":false,"y":3,"x":0}

Epics sum

This widget shows the sum of time for each epic by month and year.

{"type":"HierarchyTable","id":"","title":"Epics

By

Month","subTitle":null,"w":8,"h":4,"endpoint":"workItemsHierarchy","query":"

$select=System_Id,System_WorkItemType,System_TeamProject,System_Title,TrackedTotal,TrackedItself,HasChildren&$filter=TrackedTotal

gt

0

and

HasChildren

eq

true&$orderby=TrackedTotal

desc","hierarchyField":"System_Id","childrenQuery":"({hierarchyField})/AllWorklogs?$apply=compute(concat(cast(year(Timestamp),Edm.String),concat(cast('-',Edm.String),cast(month(Timestamp),Edm.String)))

as

yearMonth)/groupby((yearMonth),aggregate(PeriodLength

with

sum

as

TrackedTotal))","hierarchyType":"workItems","columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100,"expand":true,"child":{"field":"yearMonth"}},{"field":"TrackedTotal","title":"Tracked","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"childrenEndpoint":"workItemsHierarchy","y":19,"x":0,"odataVersion

":"v3.2"}

Budgets by Activity type

This widget shows the drill-down of Activity types per Budget. It has a custom hierarchyField usage and example of how to display values from different fields into 1 column (with aggregate/compute)

Image Added

{"type":"HierarchyTable","id":"","title":"Budget/Activity

type","subTitle":null,"w":8,"h":4,"endpoint":"budgets","query":"$apply=groupby((Id,BudgetName,Tracked,Comment),aggregate(0

with

sum

as

junk))/compute(Tracked

as

PeriodLength)","hierarchyField":"Id","childrenQuery":"({hierarchyField})/Worklogs?$apply=groupby((ActivityType/Name,Budget/Id),aggregate(PeriodLength

with

sum

as

PeriodLength))&$orderby=PeriodLength","columns":[{"field":"BudgetName","title":"Budget","expand":true,"width":-100},{"field":"Comment","title":"Comment","width":-100},{"field":"ActivityType.Name","title":"Activity","width":-100},{"field":"PeriodLength","title":"Budget

Total","width":-100,"formatter":"TimeLength"}],"autoSubtitle":true,"timeframeFilter":null,"childrenEndpoint":"budgets","y":16,"x":0,"odataVersion":"v3.2","hierarchyType":"rootExpandable"}

Total Time Tracked by Month

This widget shows the sum total of all time tracked per each month for the selected time period.

Image Added

{"title":"Total

Time

Tracked

by

Month","subTitle":"","id":"","query":"$apply=compute(concat(cast(year(Timestamp),Edm.String),concat(cast('-',Edm.String),cast(month(Timestamp),Edm.String)))

as

yearMonth)/groupby((yearMonth),aggregate(PeriodLength

with

sum

as

PeriodLength2))&$orderby=yearMonth","endpoint":"worklogsOnly","type":"Columns","w":6,"h":4,"chartSettings":{"backgroundColor":"#38ACEC","showLegend":true,"name":""},"xAxis":{"type":"date","title":"","rotation":90,"granularity":"month","minGridDistance":50},"yAxis":{"type":"duration","title":""},"series":{"fieldY":"PeriodLength2","fieldX":"yearMonth"},"autoSubtitle":false,"timeframeFilter":null,"y":13,"x":0,"dataAdapters":[{"name":"FillMissingDatePoints"}],"odataVersion":"v3.2"}

Filter by Custom Field value

Image Modified

This widget shows an example of how to filter by the specific value of your DevOps custom field on a workitem.

{"type":"FlatTable","id":"","query":"$select=CustomStringField1,CustomStringField2,TrackedItself,System_Id,System_Title,System_WorkItemType,System_TeamProject&$filter=CustomStringField1

eq

'Demo1'

and

CustomStringField2

eq

'Demo2'

and

TrackedItself

gt

0

&$orderby=TrackedItself

desc&customFields=Custom.DemoField,Custom.DemoField2","odataVersion":"v3.2","endpoint":"workItems","title":"Filter

by

custom

field

value","subTitle":null,"w":4,"h":3,"columns":[{"field":"CustomStringField1","title":"DemoField","width":100},{"field":"CustomStringField2","title":"DemoField2","width":100},{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100},{"field":"TrackedItself","title":"Tracked","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":18,"x":0}

Workitems without tracked time

This widget only shows workitems that do not have any time tracked on them.

Image Added

{"type":"FlatTable","id":"","query":"$select=System_TeamProject,System_WorkItemType,System_Title,System_Id,TrackedItself&$filter=TrackedItself

eq

0&$orderby=TrackedItself

desc&$top=10","odataVersion":"v3.2","endpoint":"workItems","title":"Workitems

without

tracked

time","subTitle":null,"w":4,"h":3,"columns":[{"field":"System_Id","title":"ID","formatter":"WorkItemId","width":60},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"Title","formatter":"WorkItemIndicator","width":-100},{"field":"TrackedItself","title":"Tracked","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":22,"x":0}

Tracked time on each work item by person - individual worklogs

This widget allows you to see each individual time log by user on every work item and the total time tracked.

Image Added

{"type":"HierarchyTable","id":"","title":"Tracked

time

on

each

work

item

by

person","subTitle":null,"w":8,"h":4,"odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","query":"$select=System_Id,System_Title,System_WorkItemType,System_TeamProject,TrackedTotal,System_AreaPath,Microsoft_VSTS_Common_ActivatedDate,Microsoft_VSTS_Common_ClosedDate,System_AssignedTo,System_State,CustomStringField1,CustomStringField2,HasChildren,TrackedItself&workItemsFilter=System_TeamProject

eq

'SuperProject'&$filter=TrackedTotal

gt

0&$orderby=TrackedTotal

desc&customFields=Custom.DemoField,Custom.DemoField,Custom.DemoField,Custom.DemoField,Custom.DemoField","hierarchyField":"System_Id","childrenEndpoint":"workItemsHierarchyAllLevels","childrenQuery":"({hierarchyField})/AllWorklogs?$apply=groupby((User/Name,WorklogDate/ShortDate),aggregate(PeriodLength

with

sum

as

TrackedTotal))&$orderby=User/Name,TrackedTotal

desc","hierarchyType":"rootExpandable","columns":[{"field":"System_Id","title":"WorkItem

ID","formatter":"WorkItemId","width":-50},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"WorkItem

Title","formatter":"WorkItemIndicator","width":-150,"expand":true},{"field":"User.Name","title":"Name","width":150},{"field":"ActivityType.Name","title":"Activity

Type","width":-100},{"field":"System_AreaPath","title":"Area","width":100},{"field":"CustomStringField1","title":"Custom

Field

1","width":-100},{"field":"CustomStringField2","title":"Custom

Field

2","width":-100},{"field":"WorklogDate.ShortDate","title":"Time

Entry

Date","width":-150},{"field":"System_AssignedTo","title":"Developer

Assigned","width":150},{"field":"System_State","title":"State","width":80},{"field":"Microsoft_VSTS_Common_ActivatedDate","title":"Start

Date","width":-150},{"field":"Microsoft_VSTS_Common_ClosedDate","title":"Closed

Date","width":-150},{"field":"TrackedTotal","title":"Total

Hours","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":194,"x":0}

Tracked time on each work item by person's total tracked time

This widget allows you to see the total tracked time by user on every work item.

Image Added

{"type":"HierarchyTable","id":"","title":"Tracked

time

on

each

work

item

by

person's

total

tracked

time","subTitle":null,"w":8,"h":4,"odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","query":"$select=System_Id,System_Title,System_WorkItemType,System_TeamProject,TrackedTotal,System_AreaPath,Microsoft_VSTS_Common_ActivatedDate,Microsoft_VSTS_Common_ClosedDate,System_AssignedTo,System_State,CustomStringField1,CustomStringField2,HasChildren,TrackedItself&workItemsFilter=System_TeamProject

eq

'SuperProject'&$filter=TrackedTotal

gt

0&$orderby=TrackedTotal

desc&customFields=Custom.DemoField,Custom.DemoField,Custom.DemoField,Custom.DemoField,Custom.DemoField","hierarchyField":"System_Id","childrenEndpoint":"workItemsHierarchyAllLevels","childrenQuery":"({hierarchyField})/AllWorklogs?$apply=groupby((User/Name),aggregate(PeriodLength

with

sum

as

TrackedTotal))&$orderby=User/Name,TrackedTotal

desc","hierarchyType":"rootExpandable","columns":[{"field":"System_Id","title":"WorkItem

ID","formatter":"WorkItemId","width":-50},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"WorkItem

Title","formatter":"WorkItemIndicator","width":-150,"expand":true},{"field":"User.Name","title":"Name","width":150},{"field":"System_AreaPath","title":"Area","width":150},{"field":"CustomStringField1","title":"Custom

Field

1","width":-150},{"field":"CustomStringField2","title":"Custom

Field

2","width":-150},{"field":"WorklogDate.ShortDate","title":"Time

Entry

Date","width":-150},{"field":"System_AssignedTo","title":"Developer

Assigned","width":150},{"field":"System_State","title":"State","width":80},{"field":"Microsoft_VSTS_Common_ActivatedDate","title":"Start

Date","width":-150},{"field":"Microsoft_VSTS_Common_ClosedDate","title":"Closed

Date","width":-150},{"field":"TrackedTotal","title":"Total

Hours","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":67,"x":0}

Tracked time on each work item by activity type

This widget allows you to see each individual time log by activity type on every work item and the total time tracked.

Image Added

{"type":"HierarchyTable","id":"","title":"Tracked

time

on

each

work

item

by

activity

type","subTitle":null,"w":8,"h":4,"odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","query":"$select=System_Id,System_Title,System_WorkItemType,System_TeamProject,TrackedTotal,System_AreaPath,Microsoft_VSTS_Common_ActivatedDate,Microsoft_VSTS_Common_ClosedDate,System_AssignedTo,System_State,CustomStringField1,CustomStringField2,HasChildren,TrackedItself&workItemsFilter=System_TeamProject

eq

'SuperProject'&$filter=TrackedTotal

gt

0&$orderby=TrackedTotal

desc&customFields=Custom.DemoField,Custom.DemoField,Custom.DemoField,Custom.DemoField,Custom.DemoField","hierarchyField":"System_Id","childrenEndpoint":"workItemsHierarchyAllLevels","childrenQuery":"({hierarchyField})/AllWorklogs?$apply=groupby((ActivityType/Name),aggregate(PeriodLength

with

sum

as

TrackedTotal))&$orderby=ActivityType/Name,TrackedTotal

desc","hierarchyType":"rootExpandable","columns":[{"field":"System_Id","title":"WorkItem

ID","formatter":"WorkItemId","width":-50},{"field":["System_Title","System_WorkItemType","System_TeamProject"],"title":"WorkItem

Title","formatter":"WorkItemIndicator","width":-150,"expand":true},{"field":"ActivityType.Name","title":"Activity

Type","width":150},{"field":"System_AreaPath","title":"Area","width":150},{"field":"CustomStringField1","title":"Custom

Field

1","width":-150},{"field":"CustomStringField2","title":"Custom

Field

2","width":-150},{"field":"WorklogDate.ShortDate","title":"Time

Entry

Date","width":-150},{"field":"System_AssignedTo","title":"Developer

Assigned","width":150},{"field":"System_State","title":"State","width":80},{"field":"Microsoft_VSTS_Common_ActivatedDate","title":"Start

Date","width":-150},{"field":"Microsoft_VSTS_Common_ClosedDate","title":"Closed

Date","width":-150},{"field":"TrackedTotal","title":"Total

Hours","width":100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":62,"x":0}

Epics with their children items

This widget filters to only show Epic work items and their children, along with the total time tracked and time tracked individually on each work item. If your top work items are not Epics and you wish to see the top level work item with its children, then simply remove the $filter=System_WorkItemType eq 'Epic' part of the widget definition.

Image Added

{"type":"HierarchyTable","id":"","title":"Epics

with

children","subTitle":"","w":8,"h":4,"odataVersion":"v3.2","endpoint":"workItemsHierarchyAllLevels","query":"$select=System_WorkItemType,System_Id,System_TeamProject,System_Title,System_State,System_AssignedTo,System_IterationPath,TrackedTotal,TrackedItself&$filter=System_WorkItemType

eq

'Epic'&workitemsFilter=System_TeamProject

eq

'SuperProject'","hierarchyField":"System_Id","childrenEndpoint":"workItemsHierarchy","childrenQuery":"({hierarchyField})/Children?filter=System_WorkItemType

eq

'Task'","hierarchyType":"rootExpandable","columns":[{"field":["System_Id","System_WorkItemType","System_TeamProject"],"title":"ID","formatter":"WorkItemIdWithIndicator","width":-150},{"field":"System_Title","title":"Work

Item

Title","formatter":"","width":-400},{"field":"System_State","title":"Status","formatter":"","width":-100},{"field":"System_AssignedTo","title":"Assigned

To","formatter":"","width":-200},{"field":"System_IterationPath","title":"Iteration

Path","formatter":"","width":-250},{"field":"TrackedItself","title":"Tracked

On

This","width":-100,"formatter":"TimeLength"},{"field":"TrackedTotal","title":"Tracked

Total","width":-100,"formatter":"TimeLength"}],"autoSubtitle":false,"timeframeFilter":null,"y":48,"x":0}