The SQL for JIRA Progress Custom Field is a convenient way to show percentage values as well as a label beside 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:
...
| Section | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
We get the following sql:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 |
| Warning | ||
|---|---|---|
| ||
As can be seen, the query of the image has been made for a particular issue, in this case the issue with identifier 10201. 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 10201 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 |