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 OData collection.

3. Click Fork.

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

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

Open

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.

{"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.

{"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:

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

{"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':

{"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.

{"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.

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).

{"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.

{"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.

{"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.

{"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.

{"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. 

{"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.

{"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.

{"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.

{"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.

{"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.

{"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.

{"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.

{"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.

{"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.  

{"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)

{"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.

{"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

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.

{"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.

{"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.

{"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.

{"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.

{"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}