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
MinColour: Color for the value 0.
MaxColour: Color for the value 1.
Fill: The colors representing the progress. (Not valid since version 3.0.x)

Example:

SELECT '93.8%' as "label",
0.938 as "value",
'#FF0000' AS minColour,
'#00FF00' AS maxColour
from issues i
where i.key= '${key}'

`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" custom field.

The first step is to get the query to get the parameters' label, 'value', 'minColor' and 'maxColor' 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:

Percentage of subtasks performed

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,
  '#FF0000' AS minColour, 
  '#00FF00' AS maxColour
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

Change Issue Identifier

As can be seen, the query of the image has been made for a particular issue, in this case the issue with identifier DEMO-6. But we want to calculate the percentage for the issue that is being displayed in JIRA at that time. Therefore, we need to take the key of the issue as a variable. For this, we replace the identifier DEMO-6 with the expression ${key}. The add-on is smart enough to replace the value of the expression with the identifier of the issue being displayed.

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.

As you can see, it is easy to use the custom field, you just have to get the appropriate SQL for the information that we want to display.
We can show any information that we imagine, and this is the great potential of the custom field, we just need to create a query and the custom field will take care of the rest.

Browser not supported