Blog

Atlassian Jira is often used in conjunction with Atlassian Confluence: there are a lot of built-in and third party plug-ins, which allow one to paste information from Jira on a Confluence page. Most of such plug-ins just list some part of Jira information: issue lists, issue counts and so on. CelesteCS Math plug-in goes further: it provides an ability to perform calculations over specified Jira issue fields, such as Story points or Progress.

To accomplish that one may use the one of the following CelesteCS Math's field value returning functions:

  • JIRAISSUEFIELDVALUES(string jql_request, string field_name, string value_if_null [, string app_link_name])
  • JIRAISSUEFIELDVALUESBYFIELDID(string jql_request, string field_id, string value_if_null [, string app_link_name])

The difference between JIRAISSUEFIELDVALUES and JIRAISSUEFIELDVALUESBYFIELDID is that in the first one the field name, like 'Time Spent' is specified, while in the second one the field identifier, like 'timespent'  is used. Feel free to read the 'Determining the field ID by its name' article regarding field IDs.

The other parameters are self-explanatory by their names. Here is the full list of parameters:

  • string jql_request - a JQL query to filter out issues. Example: 'Project = TEST'.
  • string field_name/field_id - field name or identifier. Example: 'Time Spent' / 'timespent'.
  • string value_if_null - value, which will be used in the case when there is no such field in some issue. Example: '0'
  • app_link_name - optional application link name. If not specified, the primary application link will be used. Example: 'My company Jira'.

Both these functions return an array of the specified field values of all issues, confirming the specified JQL request.

Using field retrieving function in conjunction with the others

Having those two functions is only a half of the deal. The set of fields will be retrieved, but how to perform the calculations over them? The following set of CelesteCS Math functions have been tested to be used in conjunction with Jira issue fields retrieving functions:

  • AND - returns TRUE if all parameters are non-zero values.
  • AVERAGE - returns average of all arguments.
  • CONCAT - combines the text from multiple strings.
  • COUNT - counts the number of cells that contain numbers.
  • COUNTA - counts the number of cells that are not empty.
  • MAX - returns the largest of all arguments.
  • MIN - returns the smallest of all arguments.
  • OR - returns TRUE if any of the arguments is non-zero.
  • SUM - Returns sum of all arguments.

All these functions support any number of input arguments so it may take the result of JIRAISSUEFIELDVALUES as an input.

Business Cases

Calculating the total number of all issues' Story Points in the project

Use one of the following variants:

  • SUM ( JIRAISSUEFIELDVALUES ('Project=TEST', 'Story Points', 0))
  • SUM ( JIRAISSUEFIELDVALUESBYFIELDID ('Project=TEST', 'customfield_10006', 0))

Calculating the maximum number of watchers

Use one of the following variants:

  • MAX ( JIRAISSUEFIELDVALUES ('Project=TEST', 'Watchers', 0))
  • MAX ( JIRAISSUEFIELDVALUESBYFIELDID ('Project=TEST', 'watches', 0))

Calculating the latest creation date

Use one of the following variants:

  • TEXT(MAX ( JIRAISSUEFIELDVALUES ('Project=TEST', 'Created', DATE(2000, 01, 01))), 'dd MMM yyyy')
  • TEXT(MAX ( JIRAISSUEFIELDVALUESBYFIELDID ('Project=TEST', 'created', DATE(2000, 01, 01))), 'dd MMM yyyy')

Read the 'CelesteCS Math Plugin: Working with Datetime values' article regarding working with date and time values.

Limitations

Some issue fields are not stored as a part of the issue and processed in an unusual way, thus these fields cannot be retrieved by the issue values returning functions. Here is the list of such fields:

  • Attachment
  • Comment
  • Log Work
  • Security Level
  • Thumbnail
  • Time Tracking