| Table of Contents | ||||
|---|---|---|---|---|
|
Data Sources
Custom metrics can be created using the following data sources:
...
Data Source type | Table |
|---|---|
Task Tracking System | Ticket |
TicketHistory | |
Worklog | |
Sprints | |
Releases | |
Version Control | Branch |
Commit | |
PullRequest | |
Tag |
...
If data source is configured, then each table has a list of columns, which are added on the Axis X and Y.
| Info |
|---|
To build a custom metric with using historical data you can use not only TicketHistory table but also a function ticket_snapshots. |
Ticket_snapshots
A snapshot is a representation of tickets within a defined time interval, allowing for a quick view of the most recent ticket activity over specific periods.
Examples:
To get historical data of tickets changes with type “Bug” and which have already moved in done status:
| Code Block |
|---|
select * from ticket_snapshots(
ARRAY(select key from ticket where type = 'Bug'))
sn where is_done(sn); |
To get list of tickets changes in the specified time interval and date range:
| Code Block |
|---|
select * from ticket_snapshots(
ARRAY(select key from ticket),
'week',
'2023-02-17 14:24:15.000',
'2023-03-17 14:24:15.000'
);
|
To get historical data for the specific tickets with interval - 1 month
| Code Block |
|---|
select * from ticket_snapshots(
ARRAY['EPMTEST-0001', 'EPMTEST-0003'],
'month'
) |
Mapping Azure DevOps Board with Ticket table
...
 | Text |
 | Date |
 | Numeric |
 | Array |
 | Boolean |
 | JSON |
| Info |
|---|
Note:
|
| Anchor | ||||
|---|---|---|---|---|
|
...
Table | Condition |
|---|---|
Ticket JOIN with | |
TicketHistory | Ticket.id=TicketHistory.workitem_id |
Worklog | Ticket.id=Worklog.workitem_id |
Sprint | Sprint.id=any(Ticket.sprints) |
Release | Release.id=any(Ticket.fix_releases) |
TicketHistory JOIN with | |
Ticket | TicketHistory.workitem_id=Ticket.id |
Worklog | TicketHistory.workitem_id=Worklog.workitem_id |
Sprint | Based on situation |
Release | Based on situation |
Worklog JOIN with | |
Ticket | Worklog.workitem_id=Ticket.id |
TicketHistory | Worklog.workitem_id=TicketHistory.workitem_id |
Sprint | Based on situation |
Release | Based on situation |
Sprint JOIN with | |
Ticket | Sprint.id=any(Ticket.sprints) |
TicketHistory | Based on situation |
Worklog | Based on situation |
...