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


Overview

This is a guideline how to use Metrics Query Language (MQL (smile)) 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. 


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. 

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

Available Fields


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

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 


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.

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)

The sequence of elements is fixed:

  1. addProjection(<..>)

  2. .addProjection(<..>)

  3. .addRestriction(<...>)

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


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



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


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.


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.


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



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



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


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.

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

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\StatusOpenClosed
Major13
Minor21


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\StatusOpenClosed
Major33.375
Minor66.725


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:


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


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


Available Restrictions

A full list of the restrictions available below.

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

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


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:

(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 17: 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 18: 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 19: 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 20: 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 21: 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 22: 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))


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

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

.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 26: Number of Stories grouped by Status


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



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


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.

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


Metric 2 - Delivery by weeks
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:


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.

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. 


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

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


Use Case 32: 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

If needed, data from this chart can be easily exported to Excel for further processing.


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 33: 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 34: 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 35: 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 36: 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 37: 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:


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

  

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: