Overview

This is a guideline how to use Metrics Query Language (MQL ) in PERF. This language is based on an Expression Builder which allows you to create advanced custom metrics via providing formula-like expressions about how to select, aggregate, or filter data.

To open it, go to Project Configuration → Data Sources → Custom Metrics → Advanced Configuration Over PERF Data. Click Calculation to enter or edit the expression.

A windows with metric calculation rule will open. You will see 3 areas there:

1. Information pane: available fields, functions, operations, projections, restrictions and orders - these are "building blocks" for any expression
2. Edit pane: here you will enter your expression
3. Preview pane: here you will see a generated chart. 

In the editing area you should write code to generate new chart. For more detailed instructions about coding see Section Expression Builder for PERF Custom Metrics#Use the Expression Builder below.

By default, section at the bottom of the window is empty. Choose a chart type and click Preview to see a chart. In case there are any syntax or content errors, the area will display an error message. If there is no data to calculate in this very project, the area will remain empty. 

Data Representation

To build custom metrics, PERF uses data from project tracking systems, such as JIRA, in case they are connected to this node. Each issue a from a project tracking system has a number of predefined attributes (see Section Expression Builder for PERF Custom Metrics#Available Fields below). When you make requests in the expression builder, you are operating with a plain denormalized table, where PERF keeps this data.

What a "denormalization" means? For example, in case an issue is assigned to two different sprints then such an issue will appear twice in the data table. 

Or if an issue has more then just one WorkLog Entry then the whole row will be repeated for every WorkLog Entry.

Available Fields

Quick Start Examples

The simple example of the chart that displays Number of bugs and sub-bugs found by all reporters.

The more sophisticated example below shows team velocity, which is literally the time that team spends for the issue in a certain status.

What is the logic here?

• First you group data by Sprint Name. Each sprint then have grouping by Status.
• The y-axis displays Original Estimate time from JIRA. As far as JIRA calculates time in minutes, we do divide it with a constant value = 60 to get hours. Name Velocity, hours is required argument of the divide() function, but chart does not display it.
• Next projection of Sprint Start Date has the false argument, that means we do not display it on the y-axis, but the expression builder is able to count it.
• Restriction filters only those sprints, that have ended after 2017-11-01.
• We order data by Sprint Start Date. Always order by defined projection! In case we do not define this projection before, the Order function will receive the empty value and the expression builder will not be able to display the chart.

Resolver

Expression Builder Essentials

An expression (so called "criteria") consists of the following:

1,2 - Operation with Projection

3 - Restriction (optional)

4 - Order (optional)

What is Operation?

Operation is a function to add a projection. There may be a regular projection that builds a range of discrete values or a cumulative projection that builds a values sum for previous periods:

1 - addProjection(projection)

2 - addCumulativeProjection(projection)

Cumulative Projection is useful to show an evolving trend in values (e.g. Cumulative Flow Diagram).

What is Projection?

Projection is a data series you want to see in the chart. Or it can be an invisible data series, but which still used in other elements of the expression, for example for ordering.

Each row like:

adds a coordinate axis to the chart.

Next row will define data series to see in the chart.  Every new row will add new series to the chart.

Chart displays the name of each data series below as a legend (by default - it takes name of each projection). You can add an optional string value as the last argument in the function to define another name to display in the legend:

addProjection(groupProperty("Sprint Name"))
			.addProjection(count("Key", "Issues count")) //default name - "Key"

Chart will display the name of the (Y) axis below. 

Available Projections

You can find description of all available projections below.

What is a Restriction?

A restriction is a limitation applied to data you want to see in the chart. So that you can decide what exactly is important to see in the chart and what should be filtered out.

Here you can use the mathematical logic of AND and OR, as well as such expressions as =, >, <, ≥, ≤, ≠. You can find description of these functions in the corresponding list below.

Note: pay attention to the order of the operations and separate them with brackets in order to avoid mistakes. Take a look into two different issues:

Available Restrictions

A full list of the restrictions available below.

What is an Order?

This operation is in charge of ranking values: lowest-highest or reversed.

You can change that with ascending or descending function. Functions are the same for numbers and strings.

Also, this operation is optional. In case you do not use it, chart will display values in a undefined order.

Order the function values with:

• asc(string) - ascending function
• desc(string) - descending function

Examples

Let us suggest the ways you might use the expression builder. Examples below are based on custom metrics for JIRA, the most widely used data source in PERF.

Use case 1: I want to know who submitted bugs and sub-bugs on my project and how many bugs submitted by each person

Try

addProjection(groupProperty("Reporter"))
   			.addProjection(count("Key", "Issues Count"))
			.addRestriction(or(eq("Type", "Bug"), eq("Type", "Sub-bug")))

You should see:

OR a bit sophisticated - order by count of bugs to easier see a leader

addProjection(groupProperty("Reporter"))
   			.addProjection(count("Key", "Issues Count"))
			.addRestriction(or(eq("Type", "Bug"), eq("Type", "Sub-bug")))
   			.addOrder(asc("Issues Count"))

Result:

(chart type is Column)

Use case 2: I want to know who submitted bugs and sub-bugs for the last sprint

Try:

addProjection(groupProperty("Reporter"))
            .addProjection(count("Key", "Issues Count"))
            .addRestriction(and
								(
                        		  or(eq("Type", "Bug"), eq("Type", "Sub-bug")),
                        		  eq("Sprint Name", "R1.6 Sprint 4") //specific sprint ID
                				)
							)
            .addOrder(desc("Issues Count"))

You should see:

(chart type is Column)

Use case 3: I wonder how many issues are in every sprint after August 2017

Try:

addProjection(groupProperty("Sprint Name"))
			.addProjection(count("Key"))
            .addProjection(groupProperty("Sprint Start Date", false))
            .addRestriction(gt("Sprint Start Date", "2017-09-01"))
            .addOrder(asc("Sprint Start Date"))

You should see:

(chart type is Column)

Use case 4: I want to know number of issues per Label for a particular sprint

Try:

addProjection(groupProperty("Labels"))
        	.addProjection(count("Key", "Issue Count"))
        	.addRestriction(eq("Sprint Name", "R1.6 Sprint 4"))
        	.addOrder(asc("Labels"))

You should see:

(chart type is Column)

Use case 5: I wonder how much time on average is spent for implementing a user story of a specific size

Try:

.addProjection(groupProperty("Story Points"))
.addProjection(divide(avg("WorkLog Time Spent Σ"), constValue(60), "Avg hours"))
.addProjection(divide(min("WorkLog Time Spent Σ"), constValue(60), "Min hours"))
.addProjection(divide(max("WorkLog Time Spent Σ"), constValue(60), "Max hours"))
.addOrder(asc("Story Points"))

You should see:

(chart type is Table)

Use case 6: I'd like to know how many issues product builds cover. I want to highlight product build naming is a very specific one

Try:

addProjection(groupProperty("Fix Version Name"))
              .addProjection(count("Key"))
.addProjection(groupProperty("Fix Version End Date", false))
.addRestriction(like("Fix Version Name", "Drop*" ))
.addOrder(desc("Fix Version End Date"))

You should see:

(chart type is Column)

Use case 7: I want to know a number of issues by version. It's also good to know from the chart a version end date

Try:

addProjection(joinText("res", groupProperty ("Fix Version Name"), weekOfYear("Fix Version End Date")))
              .addProjection(count("Key"))
.addRestriction(gt("Fix Version Start Date", "2018-06-01"))

You should see:

(chart type is Column)

Use case 8: I'm interested in tasks distribution between QA engineers

Try:

.addProjection(groupProperty("Responsible QA"))
.addProjection(count("Key", "Issues Count"))

You should see:

(chart type is Column)

Use case 9: I want to know for how long epics stay in an open status

Try:

addProjection(joinForGrouping("Stale Epics", groupProperty("Status"), groupProperty("Epic Summary")))
.addProjection(minusDate(currentDate(), groupProperty("Created Date"), "Days"))
.addRestriction(eq("Type","Epic"))
.addRestriction(eq("Status","Open"))
.addRestriction(gt("Created Date","2018-01-01"))
.addOrder(desc("Days"))

You should see:

(chart type is Column)

Use case 10: I want to track the growth of a business value delivered by development teams over time

Try:

.addProjection(month("Resolution Date"))
.addProjection(sum("Business Value"))
.addRestriction(eq("Type","Story"))
.addOrder(asc("Resolution Date"))

You should see:

(chart type is Column)

Use case 11: Calculate the Technical debt via data in Task Tracking System

Try:

.addProjection(day("Created Date"))                                        // issue creation date
.addProjection(divide(sum("Original Estimate"), constValue(60), "hours"))  // issue estimation
.addRestriction(hasAny("Labels", "backend"))                               // labels to track issues

You should see:

(chart type is Line)

Use case 12: I want to know how much time is spent on business tasks (aka Lead Time)

Try:

subCriteria(
criteria()
.addProjection(groupProperty("Key"))
.addProjection(month("Resolution Date", "months for order", false)) 
.addProjection(sum("Time in Status", "total")) 
.addRestriction(or(
eq("Status in History", "Assets Needed"),
eq("Status in History", "Estimate Needed"),
eq("Status in History", "Estimate Provided"),
eq("Status in History", "Open"),
eq("Status in History", "In Progress"),
eq("Status in History", "Ready for QA"),
eq("Status in History", "In QA"),
eq("Status in History", "In UAT"),
eq("Status in History", "Ready for Release"),
eq("Status in History", "Released in Production"),
eq("Status in History", "Verified in Production"),
eq("Status in History", "Reopened"),
eq("Status in History", "Closed")))
.addRestriction(or(
eq("Type", "Story"),
eq("Type", "Task"),
eq("Type", "Enhancement"),
eq("Type", "Bug")))
)
.addProjection(groupProperty("months for order")) 
.addProjection(divide(avg("total"), constValue(1440), "avg days")) 
.addRestriction(isNotEmpty("months for order"))
.addRestriction(gt("months for order", "2019-12"))
.addOrder(asc("months for order"))

You should see:

(chart type is Spline)

Use case 13: I need to know a number of issues by Priorities vs. Types in a particular sprint

Try:

addProjection(joinForGrouping("Result", groupProperty("Type"), groupProperty("Priority"))) 
            .addProjection(count("Key"))      
            .addRestriction(eq("Sprint Name", "R1.6 Sprint 4"))
            .addOrder(asc("Type"))

You should see:

(chart type is Column)

Use case 14: I wonder what is a percent of issues by Priorities vs. Types in a particular sprint

Try:

addProjection(joinForPercentage("Result", groupProperty("Type"), groupProperty("Priority")))
            .addProjection(count("Key"))
            .addRestriction(eq("Sprint Name", "R1.6 Sprint 4"))
            .addOrder(asc("Type"))

You should see:
 

(chart type is Column)

Use case 15: I want to know sum of original & remaining estimate (converted to hours) per sprint

Try:

addProjection(groupProperty("Sprint Name"))
        	.addProjection(divide(sum("Original Estimate"), constValue(60), "Original Estimate, hours"))
        	.addProjection(divide(sum("Remaining Estimate"), constValue(60), "Remaining Estimate, hours"))
        	.addOrder(asc("Original Estimate, hours"))

You should see:

 (chart type is Column)

Use case 16: I wonder how many incidents and service requests there were in scope of epics so far

Try:

addProjection(joinForGrouping("Result", groupProperty("Epic Summary"), groupProperty("Type") ))
.addProjection(count("Key"))
.addRestriction(or(eq("Type","Incident"), eq("Type","Service Request")))
.addOrder(desc("Key"))

You should see:

(chart type is Area (stacked))

Use case 16: Find all non-closed bugs by priorities over current status - to understand how many hi-priority bugs are open at the moment

Try:

.addProjection(joinForGrouping("res", groupProperty("Priority"), groupProperty("Status")))
  .addProjection(count("Key"))
  .addRestriction(and(or(eq("Type", "Bug"), eq("Type", "Sub-bug")), neq("Status", "Closed")))
  .addOrder(asc("Priority"))

You should see:

(chart type is Column (stacked))

Use case 17: I want to see all bugs at my project by streams over months

Try:

addProjection(joinForGrouping("r", month("Created Date"), groupProperty("Components")))
  .addProjection(count("Key", "Bugs count"))
  .addRestriction(or(eq("Type", "Bug"), eq("Type", "Sub-bug")))
  .addOrder(asc("Created Date"))

You should see:

(chart type is Doughnut)

Use case 18: I want to know who of developers were mostly assigned to fix bugs during last months

Try:

addProjection(joinForGrouping("r", month("Created Date"), groupProperty("Assignee")))
  .addProjection(count("Key", "Bugs count"))
  .addRestriction(or(eq("Type", "Bug"), eq("Type", "Sub-bug")))
  .addOrder(asc("Created Date"))

You should see:

(chart type is Data Table)

Use case 19: I want to know a number of defects by Origin (a custom field)

Try:

addProjection(groupProperty("Origin"))
.addProjection(count("Key", "Calls Count"))

You should see:

(chart type is Doughnut)

Use case 20: I want to know a number of defects by month sliced by a Custom Field value

Try:

.addProjection(joinForGrouping("res", month("Created Date"), groupProperty("Origin")))//Origin is a custom field
  .addProjection(count("Key"))
  .addRestriction(gt("Created Date", "2018-06-01"))
  .addOrder(asc("Created Date"))

You should see:

(chart type is Columns (stacked))

Use case 21: I want to know a Release Frequency by months

Try:

subCriteria(
    criteria()
        .addProjection(joinForGrouping("res", month("Fix Version End Date"), groupProperty("Fix Version Name")))
        .addProjection(groupProperty("Fix Version Name", false))
        .addProjection(month("Fix Version End Date", "Fix Version End Date Month", false))
        .addRestriction(eq("Status","Closed"))
        .addRestriction(isNotEmpty("Fix Version Name"))
)
.addProjection(groupProperty("res"))
.addProjection(count("Fix Version Name"))
.addProjection(groupProperty("Fix Version End Date Month", false))
.addRestriction(isNotEmpty("Fix Version End Date Month"))
.addOrder(asc("Fix Version End Date Month"))

You should see:

(chart type is Columns (stacked))

Try:

subCriteria(
  criteria()
    .addProjection(groupProperty("Key"))
    .addProjection(month("Resolution Date", "months for order", false)) // month of issue resolved date
    .addProjection(sum("Time in Status", "total"))                      // sum of time in statuses blow
    .addRestriction(or(
      eq("Status in History", "Resolved"),                              // statuses we want to track (minutes)
      eq("Status in History", "QA In Progress"),
      eq("Status in History", "Verified")))
)
.addProjection(groupProperty("months for order"))                       // group issues by month
.addProjection(divide(avg("total"), constValue(60), "avg hours"))       // average time of issues in statuses (convert to hours)
.addProjection(divide(min("total"), constValue(60), "min hours"))       // minimum time
.addProjection(divide(max("total"), constValue(60), "max hours"))       // maximum time
.addRestriction(isNotEmpty("months for order"))                         // filter issues without resolution date
.addOrder(asc("months for order"))                                      // order values by month

You should see:

(chart type is Column)

Use case 23: I want to know a cumulative time spent by a team over sprints

Try:

addProjection(groupProperty("Sprint Name"))
.addCumulativeProjection(sum("WorkLog Time Spent"))
.addProjection(groupProperty("Sprint Start Date", false))
.addOrder(asc("Sprint Start Date"))

You should see:

Use case 24: Test Management Metrics

If you have some test-management-related data in your JIRA, for example the Zephyr plugin, you may set up a few metrics in PERF by that data.

Tickets by Type

.addProjection(groupProperty("Type"))
.addProjection(count("Key"))

Chart type: columns
Tail: select >= number of types to see all types (max 1000)

Tests creation by Weeks

.addProjection(weekOfYear("Created Date")) // or weekOfMonth("Created Date")
.addProjection(count("Key"))
.addRestriction(eq("Type", "Test")) // tracking type
.addOrder(asc("Created Date"))

Chart type: columns
Tail: 12-24 (looks ok, max 1000)

Tests by Assignee

.addProjection(groupProperty("Assignee"))
.addProjection(count("Key"))
.addRestriction(eq("Type", "Test")) // tracking type

Chart type: columns
Tail: select >= assignee number to see all assignee (max 1000)

Tests by Status

.addProjection(groupProperty("Status"))
.addProjection(count("Key"))
.addRestriction(eq("Type", "Test")) // tracking type

Chart type: columns
Tail: select >= total statuses number to see all statuses (max 1000)

Tests by Components

.addProjection(groupProperty("Components"))
.addProjection(count("Key"))
.addRestriction(eq("Type", "Test")) // tracking type
.addOrder(desc("Key"))

Chart type: columns
Tail: select >= total components number to see all components (max 1000)

Use case 25: Number of Stories grouped by Status

addProjection(groupProperty("Status"))
            .addProjection(count("Key"))     
            .addRestriction(eq("Type", "Story"))

Chart type: columns

Use case 26: Scope creep (created items) by Weeks or Sprints

Let's track new items created in JIRA since a particular date e.g. Jan 1, 2020. This can be useful to observe the scope creep on a fixed-price project, or a particular release of that project.

addProjection(joinForGrouping("r", weekOfYear("Created Date"), groupProperty("Type"))) 
.addProjection(count("Key"))
.addRestriction(or(                                 // adjust to required list of issue types
  eq("Type", "Story"),
  eq("Type", "Epic"),
  eq("Type", "Additional Requirement")
))
.addRestriction(gt("Created Date", "2020-01-01"))   // you may put another threshold here
.addOrder(asc("Created Date"))

Chart type: Stacked Columns

Example of the output:

Similar goal - but tickets are sliced by Sprints, using a "Creation Date" of each sprint:

addProjection(joinForGrouping("r", groupProperty("Sprint Name"), groupProperty("Type"))) 
.addProjection(groupProperty("Sprint Start Date", false))
.addProjection(count("Key"))
.addRestriction(or(
  eq("Type", "Story"),
  eq("Type", "Epic"),
  eq("Type", "Improvement"),
  eq("Type", "Task")
))
.addRestriction(gt("Created Date", "2019-01-01"))
.addRestriction(isNotEmpty("Sprint Name"))
.addOrder(asc("Sprint Start Date"))

Chart type: Stacked Columns

Output:

Use case 27: Epics report with a percentage of Bugs for each Epic

For example, show a list of epics in the project - with a % of bugs vs. other issues per each Epic.

Use case 28: Sprint by sprint view on Story Points delivered vs. Logged hours

Use Case 29: Granularity of estimations (in SP) by sprints

addProjection(joinForGrouping("r", groupProperty("Story Points"), groupProperty("Sprint Name")))
.addProjection(groupProperty("Sprint Start Date", false))
.addProjection(count("Key"))
.addRestriction(gte("Story Points", 0))
.addRestriction(isNotEmpty("Sprint Name"))
.addOrder(asc("Sprint Start Date"))
.addOrder(asc("Story Points"))

Chart type: Data table

Use Case 30: Reference line for minimal velocity

addProjection(groupProperty("Sprint Name"))
 .addProjection(count("Key","Actual Velocity"))
 .addProjection(joinText("Min Velocity",constValue(100)))
 .addProjection(groupProperty("Sprint Start Date", false))
 .addRestriction(gt("Sprint Start Date", "2019-09-01"))
 .addOrder(asc("Sprint Start Date"))

Output:

Use Case 31: Reference line for target work logs

addProjection(weekOfMonth("WorkLog Date"))
.addCumulativeProjection(divide(sum("WorkLog Time Spent"), constValue(60), "Hours spent"))
.addCumulativeProjection(max(constValue(300),"Target"))
.addRestriction(gt("WorkLog Date","2021-01-01"))
.addOrder(asc("WorkLog Date"))

Output:

Use Case 32: Ready for development items in the backlog

addProjection(groupProperty("Labels"))
.addProjection(count("Key"))
.addRestriction(hasAny("Labels", "DoR_Passed"))
.addRestriction(or(
	neq("Sprint State", "ACTIVE"),
	isEmpty("Sprint Name")
	))

Output:

Use Case 33: Defect leakage by priority

addProjection(joinForGrouping("res", month("Created Date"), groupProperty("Priority")))
.addProjection(count("Key"))
.addRestriction(eq("Custom Field Name","Affected Environment"))
.addRestriction(eq("Custom Field Value","Production"))
.addRestriction(gt("Created Date","2019-01-01"))
.addOrder(asc("Created Date"))

Output:

Use Case 34: I want to use 2 custom fields in my calculation

Custom fields:

• Project Team
• Work type category

addProjection(groupProperty("Work type category"))
.addProjection(sum("Story Points", "SP"))
.addRestriction(eq("Project Team", "T and M"))

Output:

Use Case 35: I want to know percentage of carryover tickets (tickets completed within 2+ sprints) 

subCriteria(
criteria()
.addProjection(groupProperty("Key"))
.addProjection(count("Sprint Name", "sprint count"))
.addProjection(month("Resolution Date", "month"))
)
.addProjection(joinForPercentage("asd",groupProperty("month"),groupProperty("sprint count"),"2","3","4","5","6","7","8","9","10"))
.addProjection(count("Key"))
.addRestriction(isNotEmpty("month"))
.addOrder(asc("month"))

Output: