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

Open

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

Open

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.

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:

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

You should see:

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

Result:

(chart type is Column)

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

Try:

You should see:

(chart type is Column)

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

Try:

You should see:

(chart type is Column)

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

Try:

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:

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:

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:

You should see:

(chart type is Column)

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

Try:

You should see:

(chart type is Column)

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

Try:

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:

You should see:

(chart type is Column)

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

Try:

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:

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:

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:

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:

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:

You should see:

(chart type is Area (stacked))

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

Try:

You should see:

(chart type is Column (stacked))

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

Try:

You should see:

(chart type is Doughnut)

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

Try:

You should see:

(chart type is Data Table)

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

Try:

You should see:

(chart type is Doughnut)

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

Try:

You should see:

(chart type is Columns (stacked))

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

Try:

You should see:

(chart type is Columns (stacked))

Use case 23: I want to know issues taking average, minimum and maximum time in particular statuses (e.g. from Resolved to Closed)

Try:

You should see:

(chart type is Column)

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

Try:

You should see:

Use case 25: Test Management Metrics over Zephyr data

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

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

Tests creation by Weeks

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

Tests by Assignee

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

Tests by Status

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

Tests by Components

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

Use case 26: Number of Stories grouped by Status

Chart type: columns

Use case 27: 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.

Chart type: Stacked Columns

Example of the output:

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

Chart type: Stacked Columns

Output:

Use case 28: Scope creep per week vs. Delivery per week

Challenge: to balance the incoming flow of work with team capacity.

How to solve: understand a correlation between the amount of work being added every week (i.e. a "scope screep") against the amount of delivered work for the same week.

How it helps: if trend of a scope creep grows faster than a trend of delivered work (both - per weeks), then it's time to raise a flag and review team capacity or discuss a scope freeze with stakeholders. For Fixed-Fee type of projects it's critical to have close-to-zero scope creep in order to thoroughly monitor it. Each item within a scope creep must be absolutely clear and approved by a Project/Delivery Manager.

A couple custom metrics for that:  

Metric 1 - Scope creep by weeks

Metric 2 - Delivery by weeks

Resulting view:

Use case 29: 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 30: View progress of a scope delivery by Priority Buckets

Imagine there's some indicator being used across the whole Jira backlog (e.g. labels) to markup the whole work into following buckets -

• First Priority (e.g. Must-Have) - tasks written in the contract (statement of work, SOW), "must have" to deliver

• Second Priority (e.g. Should-Have) - tasks not necessarily mentioned in the contract, but accepted by vendor in some other form (emails, verbal agreements)

• Not a Priority (e.g. Could-Have) - nice-to-have tasks, great to deliver them although it's definitely a "stretch scope".

Challenge: to be fully concentrated on tasks/features required as a top priority, and avoid spending time on priorities 2 and 3.

How to solve: you should define what tasks/features must be prioritized as 1, 2 and 3 buckets.

How it helps: The above might be useful on Fixed-Fee projects where it's crucial to carefully track the scope of work in backlog to avoid a budget overspend. So, this helps to make sure a team meets deadlines as well as to eliminate wasting your project budget. 

The output:

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

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

Chart type: Data table

Use Case 30: Reference line for minimal velocity

Output:

Use Case 33: Reference line for target work logs

Output:

Use Case 34: Ready for development items in the backlog

Output:

Use Case 35: Defect leakage by priority

Output:

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

Custom fields:

• Project Team

• Work type category

Output:

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

Output:

Use Case 38: I want to know he number of tickets (per month) that weren’t resolved in time within a specified deadline (the date from “due to”)

Output: