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.

Open

Available Fields

Quick Start Examples

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

Open

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.

Open

Expression Builder Essentials

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

Open

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.

First row will define the axis of abscissae (X).

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

Open

To select few types of issues use joinForGrouping function: 

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

This will group data by the second property within groups by first property.

In the above example data will be grouped by Type, and each type's group then will have data grouped by Priority.

Open

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

addProjection(groupProperty("Sprint Name")) .addProjection(count("Key"))

Open

addProjection(groupProperty("Sprint Name")) .addProjection(count("Key")) .addProjection(sum("Story Points"))

Open

addProjection(groupProperty("Sprint Name")) .addProjection(count("Key")) .addProjection(sum("Story Points")) .addProjection(sum("Remaining Estimate"))

Open

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. 

Open

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:

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

Open

And

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

Open

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:

Open

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

Result:

Open

(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:

Open

(chart type is Column)

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

Try:

You should see:

Open

(chart type is Column)

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

Try:

You should see:

Open

(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:

Open

(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:

Open

(chart type is Column)

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

Try:

You should see:

Open

(chart type is Column)

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

Try:

You should see:

Open

(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:

Open

(chart type is Column)

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

Try:

You should see:

Open

(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:

Open

(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:

Open

(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:
 

Open

(chart type is Column)