These queries allow to display displaying icons of issue types, statuses, and piorities priorities
For this, you need to use a function named IMG()
The formula construction is: IMG(TableName.ICONURL, width,height)
For example: IMG(it.ICONURL,16,16) where:
...
16,16 - width 16 px, height 16 px.
...
| Panel | ||
|---|---|---|
| ||
EXAMPLES |
| Info |
|---|
PROJECTNAME* is a tentative name in our queries. Please note that in your JQLs you need to use real names of your Projects, Users, Sprints ,etc. |
Add Issue type icon
| Code Block | ||
|---|---|---|
| ||
select IMG(it.ICONURL,16,16) as `Type`, LINKISSUE(i.`KEY`) as `Issue` from ISSUE i join ISSUETYPE it on it.ID=i.TYPEID where JQL='project=PROJECTNAME' |
...
Add a description tothe Issue type icon
In this case, we gonna combine two functions IMG and CONCAT
| Info |
|---|
CONCAT |
...
takes a variable number of string arguments and concatenates (or joins) them into a single string. It requires a minimum of two input values; otherwise, CONCAT will raise an error. |
| Code Block | ||
|---|---|---|
| ||
select IMG(it.ICONURL,16,16,CONCAT(it.NAME, ': ', it.DESCRIPTION)) as `Type`, LINKISSUE(i.`KEY`) as `Issue` from ISSUE i join ISSUETYPE it on it.ID=i.TYPEID where JQL='project=PROJECTNAME' |
...
Join Status Name and Status color through the function LOZENGESTATUS
| Code Block | ||
|---|---|---|
| ||
select LINKISSUE(i.`KEY`) as `Issue`, LOZENGESTATUS(sc.COLOR, s.NAME, true) as `Status` from ISSUE i join WORKFLOWSTATUSSTATUS s on s.ID = i.STATUSID join WORKFLOWSTATUSCATEGORY sc on sc.ID = s.CATEGORYID where JQL='project=PROJECTNAME ORDER BY updated ASC' |
Transforming each column that contains an Icon you,finally, receive a table kind of this:
| Code Block | ||
|---|---|---|
| ||
select IMG(it.ICONURL,16,16,CONCAT(it.NAME, ': ', it.DESCRIPTION)) as `Type`,
LINKISSUE(i.`KEY`) as `Issue`, IMG(ip.ICONURL,16,16 ) as `Priority`,
LOZENGESTATUS(sc.COLOR, s.NAME, true) as `Status`,
x.DISPLAYNAME as `Assignee`, y.DISPLAYNAME as `Reporter`,
FORMATDATE(i.CREATED,'yyyy-MM-DD' |
) as `Created`
from ISSUE i
join STATUS s on s.ID = i.STATUSID
join WORKFLOWSTATUSCATEGORY sc on sc.ID = s.CATEGORYID
join USER x on i.ASSIGNEEID=x.ACCOUNTID
join USER y on i.REPORTERID=y.ACCOUNTID
join ISSUETYPE it on it.ID=i.TYPEID
join ISSUEPRIORITY ip on ip.ID=i.PRIORITYID
where JQL='assignee = currentUser()' |
