The SQL for JIRA Progress Custom Field is a convenient way to show percentage values as well as a label beside the bar.

The SQL for JIRA query have to return some specific columns in order to configure the bar:

• Value: a decimal number between 0 and 1. It represents the progress.
• Label: A free text shown on the right of the bar
• Fill: The colors representing the progress.

Example:

select '33.3%' as "label", 0.333 as "value", '{gradient:["green", "yellow", "red"]}' as fill

from issues i where i.key=?

The fill column is quite customizable with solid colors as well as gradients. Examples:

• { color: "#ff1e41" } 
• { color: 'rgba(255, 255, 255, .3)' } 
• { gradient: ["red", "green", "blue"] } 
• { gradient: [["red", .2], ["green", .3], ["blue", .8]] } 
• { gradient: [ ... ], gradientAngle: Math.PI / 4 } 
• { gradient: [ ... ], gradientDirection: [x0, y0, x1, y1] } 

Default: { gradient: ["#3aeabb", "#fdd250"] }

Example

Suppose our issues are divided into subtasks. This is common when our tasks require the completion of several processes to be able to solve them. We may need an analysis, testing, I+D ...

We may be interested to know what percentage of the subtasks have been performed. This can be achieved with "Progress Bar".

The first step is to get the query to get the parameters' label, 'value' and 'grill' that our custom field needs, and for this we help from the add-on SQL for JIRA Driver.

We open the SQL for JIRA Driver console and build our sql:

We get the following sql:

SELECT
(CAST(resolutions.solved AS FLOAT) / CAST(totals.total AS FLOAT)) AS VALUE,
  concat(CAST((CAST(resolutions.solved AS FLOAT) / CAST(totals.total AS FLOAT))*100 AS VARCHAR(5)), '%') AS label,
  '{gradient:["red", "yellow", "green"]}' AS fill 
FROM
  (
    SELECT
      ifnull(NULLIF(COUNT(*), 0), 1) AS total 
    FROM
      issues i 
      INNER JOIN
        issuesubtasks isb 
        ON isb.parentid = i.id 
    WHERE
      i.jql = 'issue = ${key}' 
  )
  totals,
  (
    SELECT
      COUNT(*) AS solved 
    FROM
      issues i 
    WHERE
      i.jql = 'parent = ${key} and resolution is not EMPTY' 
  )
  resolutions

Obtaining the sql, we created a custom field of type SQL for JIRA Progress and we insert our sql in the description of the custom field:

With these simple steps, we already have our custom field that measures the percentage of subtasks resolved in an issue.