OUTDATED ARTICLE - for the latest version of this article, please navigate to: 7pace Timetracker's Reporting Widgets Gallery
Find a collection of custom widgets requested by our 7pace Timetracker users by copying the widget JSON definition and importing the widget to any of your existing reports. Don't see what you're looking for? Consider enlisting our professional services to have a custom widget created for you.
How to view tracked time grouped by DevOps "Activity Type"
How to view tracked time by estimations (Effort, Original Estimate, Completed Work, Remaining Work)
How to view work items filtered by tags
How to view your last 100 worklogs for a specific date period
How to view the pace of work items
How to view tracked time by DevOps "Area Path"
How to view time tracked per person per project
How to view time summary per project
How to view time tracked per budget
How to view time tracked per budget grouped by activity type
How to view time per custom work item field
How to view tracked time per person in a bar chart
How to view shortfalls per person for a pre-defined date period
How to view Individual shortfalls per day
How to view time logged by activity type per team member
How to view total time tracked per iteration
How to view items completed by date range
How to view items not completed during a given date range
How to view total story points per date range
How to view total story points with a specific tag
How to view total time spent fixing bugs
How to show the sum of time for each epic by month and year
How to view Budgets by Activity type
How to view total time tracked by month
How to filter by custom field value
How to view Workitems without tracked time
Tracked time on each work item by person - individual worklogs
Tracked time on each work item by person's total tracked time
Tracked time on each work item by activity type
Epics with their children items
Find users and work items without tracked time Excel tables
Postman collection
How to import the Postman collection data to your workspace
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:
- 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 - Click on "OData collection"
- Click on the "Fork" button
- Choose the workspace where the collection should be stored.
The current collection has several variables which you should change according to your chosen environment:
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:
And our reporting feature supports filtration by these tags. Here is an example of the Work Item table filtered by 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"}
Latest workLogs
This flat table provides a list of your latest 100 workLogs for the selected date period. Recommended for "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 tables provides a list of work items (all levels) with calculated Pace based on Effort/Original Estimate/Story Point as 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, this shows the daily shortfall values for each individual person / day over a certain period. This period 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
{"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"}
{"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"}
Tracked time on each work item by person - individual worklogs
{"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}
Comments
0 comments
Please sign in to leave a comment.