Warning
This functionality is soon to be replaced by Custom Metrics V2. No additional actions required, support team will contact you in advance

Table of Contents

exclude	^[ \t\n]*$

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:

Information pane: available fields, functions, operations, projections, restrictions and orders - these are "building blocks" for any expression
Edit pane: here you will enter your expression
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.

Image Removed

Image Added

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.

Image Modified

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

Image Modified

Available Fields

Expand

title	Click to see the list

Info

title

Tip

Most of the fields are just the same you have in a JIRA issue card. In addition there are some more complicated fields.

Field	Example value

Image Removed

Image Added
	Key	EPMPRF-2345
	Type	Bug/Improvement/Story/Task/etc.
	Priority	Blocker/Critical/Major/Minor/etc.
	Severity	Blocker/Critical/Major/Minor/etc.
	Component (deprecated)	"Documentation", "Backend", "Email Subsystem", etc.
	Components	[Array type field] "Documentation", "Backend", "Email Subsystem", etc. Note: Used with such restrictions as "hasAll" and "hasAny", impossible to use with other restrictions
	Label (deprecated)	"doc", "client", "wow", etc. Note: labels in upper-case and lower-case are recognized as different.
	Labels	[Array type field] "doc", "client", "wow", etc. Note: labels in upper-case and lower-case are recognized as different. Used with such restrictions as "hasAll" and "hasAny", impossible to use with other restrictions
	Environment	"IE9", "Windows 10"
	Story Points	3/5/8/13/etc.
	Sprint Name	R1.6 Sprint 1
	Sprint Start Date	2/14/2018
	Sprint End Date	2/28/2018
	Sprint State	In progress/Closed/etc.
	Status	Open/In progress/Verified/Closed/etc.
	Resolution	Approved/Fixed/Implemented/Won't fix
	Assignee	Jane Doe
	Fix Version Name	R1.6
	Fix Version End Date	10/12/2018
	Fix Version Start Date	10/22/2018
	Reporter	ivan_smirnov@epam.com (please note: all letters in low register)
	Created Date	9/15/2018
	Updated Date	9/21/2018
	Resolution Date	9/27/2018
	Due Date	9/30/2018
	Original Estimate	22h
	Remaining Estimate	3h
	Epic Key	EPMPRF-234
	Epic Summary	"Login page"
	WorkLog Date	4/17/2018
	WorkLog Reporter	Ivan Smirnov
	WorkLog Time Spent	3h
	Resolver	Jane Doe
	Lead Time	time spent on a task completion: starting from the moment of its creation and finishing when it's closed
	Cycle Time	time spent by a task in work, i.e. in progress
	Time in Status	time spent by an issue in its workflow statuses
	Status in History	set of statuses the issue stayed in during its life time
	Time to Reaction	time elapsed between issue creation and their status changed for the first time
	WorkLog Time Spent Σ	time spent including time spent on implementing sub items
	Remaining Estimate Σ	time remaining including time remaining on implementing sub items
	Original Estimate Σ	time estimation including estimates for sub items
	Custom Field Name - deprecated, starting from 2.1.38 Release, User can use more then one custom field via indicating name of a field.	JIRA custom field name. PERF can process the custom fields of the following types: string number user Make sure that you have defined the corresponding Custom Field in your project tracking system before its usage.
	Custom Field Value - deprecated	Value for a custom field
	Summary	Ticket title
	Issue name	Summary text provided for a ticket

Info

title

Dates format

It is better to use dates in an expression in the following formats:

yyyy-mm-dd
mm/dd/yyyy
Month-yy
Month

Date is one of the formats above is sorted in an ascending/descending order properly on the axis X.

The dates in the following formats will likely be sorted improperly on the axis X:

m/d e.g. 3/14 meaning March 14.
Mmm-yy
D-Mmm e.g. 14-Mar
D-Mmm-yy e.g. 14-Mar-20
Month day, yyyy e.g. March 14, 2020
Any date format accompanied with time, e.g. 3/14/20 1:30 PM, 3/14/20 13:30.

Quick Start Examples

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

Image Removed

Image Added

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.

Image Removed

Image Added

Code Block
Resolver

Expression Builder Essentials

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

Image Modified

1,2 - Operation with Projection

3 - Restriction (optional)

4 - Order (optional)

Warning

The sequence of elements is fixed:

addProjection(<..>)
.addProjection(<..>)
.addRestriction(<...>)
.addOrder(<...>)

Expression breaking this sequence will not work.

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:

Code Block

theme	FadeToGrey

.addProjection( <type of projection> ( <"values"> ))

adds a coordinate axis to the chart.

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

theme

Code Block

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

Image Removed

Image Added

To select few types of issues use joinForGrouping function:

FadeToGrey

Code Block
theme

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

Image Added

Info

title

Note

For defining the axis of abscissa (X) you can use groupProperty, joinForGrouping projections only. You can use other possible projections for defining the axis of ordinates (Y).

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

theme

Code Block

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

Image Removed

Image Added

FadeToGrey

Code Block
theme

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

Image Removed

Image Added

FadeToGrey

Code Block
theme

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

Image Removed

Image Added

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:

Code Block

language	java

themeFadeToGrey

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

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

Image Removed

Image Added

Available Projections

You can find description of all available projections below.

Expand

title	Click to see the list

Here is the list of functions that you can use in the axis definition:

Info
There are values of the function in brackets. Find the possible values (the same as in JIRA) in the Fields Names list above.

Function(arguments)

Description

avg(string)

the average (arithmetic mean) of all input values

constValue(number)

represents numeric constant value e.g. for arithmetical operations divide(sum("Original Estimate"), constValue(60), "Original Estimate in Hours"))

count(key)

counts the amount of items

count(key, string)

counts the amount of issues, displays the alias "string"

currentDate(string)

provides today's date; useful in minusDate

day(string)

extracts day in format YYYY-MM-DD from some date time filed. Can be used in restrictions only. e.g. eq(day("Created Date"), "2018-01-01")

day(string, string)

identical to day(string) but gives ability to define some alias to the result

day(string, string, boolean)

identical to day(string) but gives ability to define some alias to the result and control visibility by boolean flag

divide(projection, projection, string)

arithmetical operation division. e.g. divide(sum("Original Estimate"), sum("Story Points"), "H per SP")

divide(projection, projection, string, boolean)

identical to divide(projection, projection, string) but with ability to control result visibility

groupProperty(string)

uses some field for grouping e.g. group by sprint groupProperty("Sprint Name")

groupProperty(string, boolean)

identical to groupProperty(string) but with ability to control result visibility (false makes it invisible)

groupProperty(string, string, boolean)

identical to groupProperty(string) but with ability to control result visibility and output name e.g. groupProperty("Sprint Name", "Sprint", true)

joinForGrouping(string, projection, projection)

two dimension grouping e.g. calculate count of issues in different statuses per priority
addProjection(joinForGrouping("Result", groupProperty("Priority"), groupProperty("Status"))).addProjection(count("Key"))

Priority\Status	Open	Closed
Major	1	3
Minor	2	1

joinForPercentage(string, projection, projection)

calculates percent in two-dimensional grouping (for the 2nd dimension) e.g. calculate count of issues in different statuses per priority
addProjection(joinForPercentage("Result", groupProperty("Priority"), groupProperty("Status"))).addProjection(count("Key"))

Priority\Status	Open	Closed
Major	33.3	75
Minor	66.7	25

joinText(string, boolean, projection[])

to show a particular label text at axis X including information from 2+ fields. Boolean parameter: if 'true' - text becomes invisible.

joinText(string, projection[])

to show a particular label text at axis X including information from 2+ fields

max(string)

maximum value across all input values

min(string)

minimum value across all input values

minus(projection, projection, string)

arithmetical operation subtraction. e.g. minus(sum("Original Estimate"), sum("Remaining Estimate"), "Original - Remaining")

minus(projection, projection, string, boolean)

identical to minus(projection, projection, string) but with ability to control result visibility

minusDate(projection, projection, string)

calculates difference between two dates e.g.
minusDate(groupProperty("Sprint End Date"), groupProperty("Sprint Start Date"), "Sprint Duration")

minusDate(projection, projection, string, boolean)

identical to minusDate(projection, projection, string) with ability to control result visibility

month(string)

extracts month in format YYYY-MM from some date time filed. Can be used in restrictions only. e.g. eq(month("Created Date"), "2018-01")

month(string, string)

identical to month(string) but gives ability to define some alias to the result

month(string, string, boolean)

identical to month(string) but gives ability to define some alias to the result and control visibility by boolean flag (false means invisible)

multiply(projection, projection, string)

arithmetical operation multiplication. e.g. multiply(constValue(24), constValue(7), "24x7")

multiply(projection, projection, string, boolean)

identical to multiply(projection, projection, string) but with ability to control result visibility

plus(projection, projection, string)

arithmetical operation addition. e.g. plus(constValue(1), constValue(1), "1+1")

plus(projection, projection, string, boolean)

identical to plus(projection, projection, string) but with ability to control result visibility

plus(string, boolean, projection[])

arithmetical operation addition for multiple projections with ability to control result visibility. e.g. plus("1+1+1", true, constValue(1), constValue(1), constValue(1))

property(string)

use some field e.g. property("Key")

property(string, string)

identical to property(string) but with ability to control output name e.g. property("Key", "Ticket Identificator")

quarter(string)

extracts quarter in format YYYY Q1, YYYY Q2 and etc. from some date time filed. Can be used in restrictions only. e.g.

quarter(string, string)

identical to quarter(string) but gives ability to define some alias to the result

quarter(string, string, boolean)

identical to quarter(string) but gives ability to define some alias to the result and control visibility by boolean flag

sum(string)

sum across all input values

weekOfMonth(string)

extracts week of month in format YYYY-MM(W) from some date time filed. Can be used in restrictions only. e.g. eq(weekOfMonth("Created Date"), "2018-01(1)")

weekOfMonth(string, string)

identical to weekOfMonth(string) but gives ability to define some alias to the result

weekOfMonth(string, string, boolean)

identical to weekOfMonth(string) but gives ability to define some alias to the result and control visibility by boolean flag

weekOfYear(string)

extracts week of year in format YYYY(WW) from some date time filed. Can be used in restrictions only. e.g. eq(weekOfYear("Created Date"), "2018(18)")

weekOfYear(string, string)

identical to weekOfYear(string) but gives ability to define some alias to the result

weekOfYear(string, string, boolean)

identical to weekOfYear(string) but gives ability to define some alias to the result and control visibility by boolean flag

year(string)

extracts year in format YYYY from some date time filed. Can be used in restrictions only. e.g. eq(year("Created Date"), "2018")

year(string, string)

identical to year(string) but gives ability to define some alias to the result

year(string, string, boolean)

identical to year(string) but gives ability to define some alias to the result and control visibility by boolean flag

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:

Code Block

language	java
theme	FadeToGrey

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

Image Removed

Image Added

Code Block
And

Code Block

language	java
theme	FadeToGrey

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

Image Removed

Image Added

Available Restrictions

A full list of the restrictions available below.

Expand

title	Click to see the list

The following functions will help you to adjust data series in the chart to specific needs:

Function(arguments)	Description
and(restriction, restriction)	and
and(restriction[])	and (array of restrictions)
eq(string, object)	equal
gt(string, object)	greater than
gte(string, object)	greater than or equal
lt(string, object)	less than
lte(string, object)	less than or equal
neq(string, object)	not equal
or(restriction, restriction)	or
or(restriction[])	or (array of restrictions)
like(string, object)	define mask to filter items; 'string' - by what field to search a match; 'object' - by what symbols to search, it is case sensitive
notLike(string, object)	the same as like restriction, but not like
hasAny(string, string[])	"string" - array type field; "string[]" - list of field values. Finds issues, which has any of specified values in specified field Note: When using with single value field this restriction works like "or"* restriction*
hasAll(string, string[])	"string" - array type field; "string[]" - list of field values. Finds issues, which has all specified values in specified field Note: Impossible to use with single value fields
isEmpty (string)	check if a field is empty, string is a field name
isNotEmpty (string)	check if a field is not empty, string is a field name

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

Code Block

themeMidnight


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

You should see:

Image Removed

Image Added

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

Code Block

theme	Midnight

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

Result:

Image Removed

Image Added

(chart type is Column)

Use case 2:

I

I want to know who submitted bugs and sub-bugs for the last sprint

Try:

Code Block

language	java

themeMidnight

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:

Image Removed

Image Added

(chart type is Column)

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

Try:

Code Block

theme	Midnight

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

Info

Please note that in order to have sorting by Sprint Start Dates you should add a line

.addProjection(groupProperty("Sprint Start Date", false))

You should see:

Image Removed

Image Added

(chart type is Column)

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

Try:

Code Block

theme	Midnight

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

You should see:

Image Removed

Image Added

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

Code Block

themeMidnight
.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:
Image Modified
(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:
Code Block
theme Midnight
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:
Image Removed
Image Added
(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:
Code Block
theme
Midnight
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:
Image Removed
Image Added
(chart type is Column)
Use case 8: I'm interested in tasks distribution between QA engineers
Try:
Code Block
themeMidnight
.addProjection(groupProperty("Responsible QA")) .addProjection(count("Key", "Issues Count"))
You should see:
Image Removed
Image Added
(chart type is Column)
Use case 9: I want to know for how long epics stay in an open status
Try:
Code Block
theme Midnight
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:
Image Removed
Image Added
(chart type is Column)
Use case 10: I want to track the growth of a business value delivered by development teams over time
Try:
Code Block
theme Midnight
.addProjection(month("Resolution Date")) .addProjection(sum("Business Value")) .addRestriction(eq("Type","Story")) .addOrder(asc("Resolution Date"))
You should see:
Image Removed
Image Added

(chart type is Column)
Use case 11:
Calculate
Calculate the Technical debt via data in Task Tracking System
Try:
Code Block
.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:
Image Removed
Image Added
(chart type is Line)
Use case 12: I want to know how much time is spent on business tasks (aka Lead Time)
Try:
Code Block
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:
Image Removed
Image Added
(chart type is Spline)
Use case 13: I need to know a number of issues by Priorities vs. Types in a particular sprint
Try:
Code Block
theme Midnight
addProjection(joinForGrouping("Result", groupProperty("Type"), groupProperty("Priority"))) .addProjection(count("Key")) .addRestriction(eq("Sprint Name", "R1.6 Sprint 4")) .addOrder(asc("Type"))
You should see:
Image Removed

Image Added
(chart type is Column)

Use case 14: I wonder what is a percent of issues by Priorities vs. Types in a particular sprint
Try:
Code Block
themeMidnight
addProjection(joinForPercentage("Result", groupProperty("Type"), groupProperty("Priority"))) .addProjection(count("Key")) .addRestriction(eq("Sprint Name", "R1.6 Sprint 4")) .addOrder(asc("Type"))
You should see:

Image Removed
Image Added

(chart type is Column)
Use case 15: I want to know sum of original & remaining estimate (converted to hours) per sprint
Try:
Code Block
theme Midnight
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:
Image Removed
Image Added
(chart type is Column)
Use case 16: I wonder how many incidents and service requests there were in scope of epics so far
Try:
Code Block
language java
themeMidnight
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:
Image Removed

Image Added
(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:
Code Block
language java
themeMidnight
.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:
Image Removed
Image Added
(chart type is Column (stacked))
Use case 18: I want to see all bugs at my project by streams over months
Try:
Code Block
language java
themeMidnight
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:
Image Removed
Image Added
(chart type is Doughnut)
Use case 19: I want to know who of developers were mostly assigned to fix bugs during last months
Try:
Code Block
language java
themeMidnight
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:
Image Removed
Image Added
(chart type is Data Table)

Use case 20: I want to know a number of defects by Origin (a custom field)
Try:
Code Blocktheme
Midnight
Code Block
addProjection(groupProperty("Origin")) .addProjection(count("Key", "Calls Count"))
You should see:
Image Removed
Image Added
(chart type is Doughnut)

Use case 21: I want to
know
know a number of defects by month sliced by a Custom Field value
Try:
Code Block
theme Midnight
.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:
Image Removed
Image Added
(chart type is Columns (stacked))
Use case 22: I want to know a Release Frequency by months
Try:
Code Block
theme Midnight
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:
Image Removed
Image Added
(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:
Code Block
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:
Image Removed
Image Added
(chart type is Column)

Use case 24: I want to know a cumulative time spent by a team over sprints
Try:
Code Block
themeMidnight
addProjection(groupProperty("Sprint Name")) .addCumulativeProjection(sum("WorkLog Time Spent")) .addProjection(groupProperty("Sprint Start Date", false)) .addOrder(asc("Sprint Start Date"))
You should see:
Image Removed

Image Added

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
Code Block
.addProjection(groupProperty("Type")) .addProjection(count("Key"))
Chart type: columns
Tail: select >= number of types to see all types (max 1000)
Tests creation by Weeks
Code Block
.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
Code Block
.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
Code Block
.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
Code Block
.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 26: Number of Stories grouped by Status

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

Tip
If similar view is needed by another type of issue - just modify the Restriction clause, e.g. put "Epic" there instead of a "Story".

Chart type: columns
Image Removed
Image Added

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.
Code Block
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:
Image Removed
Image Added

Similar goal - but tickets are sliced by Sprints, using a "Creation Date" of each sprint:
Code Block
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:
Image Removed
Image Added

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:
Code Blocktitle
Metric 1 - Scope creep by weeks
Code Block
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", "Bug"), eq("Type", "Task"), eq("Type", "Sub-task") )) .addRestriction(gt("Created Date", "2020-09-01")) // you may put another threshold here .addOrder(asc("Created Date"))
Code Blocktitle

Metric 2 - Delivery by weeks
Code Block
addProjection(joinForGrouping("r", weekOfYear("Resolution Date"), groupProperty("Type"))) .addProjection(count("Key")) .addRestriction(or( // adjust to required list of issue types eq("Type", "Story"), eq("Type", "Bug"), eq("Type", "Task"), eq("Type", "Sub-task") )) .addRestriction(gt("Resolution Date", "2020-09-01")) // you may put another threshold here .addOrder(asc("Resolution Date"))

Resulting view:
Image Removed
Image Added

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.
Image Removed
Image Added
Info
Please make sure that Epic issue type is not ignored on a Workflows step of configuration.

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.

Code Block
---- First priority ----- .addProjection(joinForGrouping("res", groupProperty("Type"), groupProperty("Status"))) .addProjection(count("Key")) .addRestriction(hasAny("Labels", "sow")) .addOrder(asc("Status")) .addRestriction(eq("Type", "Story")) ---- Second priority ---- .addProjection(joinForGrouping("res", groupProperty("Type"), groupProperty("Status"))) .addProjection(count("Key")) .addRestriction(hasAny("Labels", "scope_plan")) .addOrder(asc("Status")) .addRestriction(eq("Type", "Story")) --- NOT a priority --- .addProjection(joinForGrouping("res", groupProperty("Type"), groupProperty("Status"))) .addProjection(count("Key")) .addRestriction(eq("Type", "Story")) .addRestriction(neq("Status", "Closed")) .addRestriction(hasNo("Labels", "scope_plan")) .addRestriction(hasNo("Labels", "sow")) .addOrder(asc("Status"))
The output:
Image Removed
Image Added
Use case 31: Sprint by sprint view on Story Points delivered vs. Logged hours
Image Removed
Image Added

Use Case 32: Granularity of estimations (in SP) by sprints
Code Block
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
Image Removed
Image Added
Tip
If needed, data from this chart can be easily exported to Excel for further processing.
Image Modified

Use Case 30: Reference line for minimal velocity
Code Block
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:
Image Removed
Image Added

Use Case 33: Reference line for target work logs
Code Block
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:
Image Removed
Image Added

Use Case 34: Ready for development items in the backlog
Code Block
addProjection(groupProperty("Labels")) .addProjection(count("Key")) .addRestriction(hasAny("Labels", "DoR_Passed")) .addRestriction(or( neq("Sprint State", "ACTIVE"), isEmpty("Sprint Name") ))
Output:
Image Removed
Image Added

Use Case 35: Defect leakage by priority
Code Block
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:
Image Removed
Image Added

Use Case 36: I want to use 2 custom fields in my calculation
Custom fields:
Project Team
Work type category

Code Block
addProjection(groupProperty("Work type category")) .addProjection(sum("Story Points", "SP")) .addRestriction(eq("Project Team", "T and M"))
Output:
Image Removed
Image Added

Use Case 37: I want to know percentage of carryover tickets (tickets completed within 2+ sprints)
Code Block
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:
Image Modified

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

Code Block
subCriteria( criteria() .addProjection(groupProperty("Key")) .addProjection(month("Resolution Date","month")) .addProjection(minusDate(groupProperty("Resolution Date"), groupProperty("Due Date"), "diff")) .addRestriction(gte("Resolution Date", "2021-01-01")) ) .addProjection(groupProperty("month")) .addProjection(count("Key", "Issues Count")) .addRestriction(gt("diff", 0)) .addOrder(asc("month"))
Output:
Image Removed
Image Added
excerpt-include
EPMDMO-ga-entity-PerfEPMDMO-ga-entity-Perf Include PageEPMDMO-ga-toplevel-ASSUREEPMDMO-ga-toplevel-ASSURE
Integrations
Integrations
name Copyright
nopanel true

Version	Old Version 89	New Version Current
Changes made by	Aleksandr Bogush	Denis Olevanov (Unlicensed)
Saved on	Jan 13, 2022	Jan 18, 2022

Page Comparison

Versions Compared

Key

Overview

Data Representation

Available Fields

Tip

Dates format

Quick Start Examples

Expression Builder Essentials

What is Operation?

What is Projection?

Note

Available Projections

What is a Restriction?

Available Restrictions

What is an Order?

Examples

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

Use case 2:

I want to know who submitted bugs and sub-bugs for the last sprint

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

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

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

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

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

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

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

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

Use case 11:

Calculate the Technical debt via data in Task Tracking System

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

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

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

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

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

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

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

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

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

Use case 21: I want to

know a number of defects by month sliced by a Custom Field value

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

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

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

Use case 25: Test Management Metrics over Zephyr data

Use case 26: Number of Stories grouped by Status

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

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

Metric 1 - Scope creep by weeks

Metric 2 - Delivery by weeks

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

Use case 30: View progress of a scope delivery by Priority Buckets

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

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

Use Case 30: Reference line for minimal velocity

Use Case 33: Reference line for target work logs

Use Case 34: Ready for development items in the backlog

Use Case 35: Defect leakage by priority

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

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

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

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