Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Table transformer - Count issues in epic, show percentage done

Kántor Tibor September 27, 2021

Hi all,

I want to report progress of epics by creating a table with epics and the percent ratio of done/all issues in epics. This seems easy, just join the table with the Epic name, however my epic names contain special characters.

Here's the scenario:

Table 1 T1 is a jira query returning the epics with the epic name as column

Table 2 T2 is a jira query which returns all issues in epics returend by table 1

 

What I want to do(pseudo script):

Select T1.'Epic name'

(count T2.'Key' where status is resolved or closed) / count all T2.'Key')*100

FROM T1

join via epic names

 

The result I'd like to have:

Epic from T1Percent done
epic 110
epic 230

 

My problem is that joining does not work correctly because the epic names in T1 are messed up, and do not display correctly due to the special characters displayed as HTML codes: & as & , Ü as Ü and I cannot find any other values which could be used. Interestingly the epic names coming from T2 are perfectly fine...

There is also an open bug regarding this: https://jira.atlassian.com/browse/CONFSERVER-54431

Is there any way around this? Any help would be appreciated!

Thanks!

1 answer

1 accepted

3 votes
Answer accepted
Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2021

Hi @Kántor Tibor ,

Please, give us the screenshot (or a manually created table with dummy data) of your second Jira Issues table where you have correct Epic Names, related issues and their statuses.

Maybe we'll be able to count different statuses regarding only this table?

For example, create a Gantt chart as it is shown here or use a Pivot Table macro to aggregate your data?

Kántor Tibor September 28, 2021

Hi @Katerina Kovriga _Stiltsoft_ ,

I thought about the pivot table method, however we might have Epics which do not have any issues in them yet, they were just created as the result of a planning.

Anyway, here are the dummy tables:

T1:
Type | Key | Epic Name
Epic | ABC-1 | Online&Marketing
Epic | ABC-2 | "Fire"&"Water"
Epic | ABC-8 | This and that

T2:
Type | Key | Summary | Status | Epic Name
Task | ABC-3 | sum3 | New | Online&Marketing
Task | ABC-4 | sum4 | Closed | Online&Marketing
Story | ABC-5 | sum5 | New | Online&Marketing
Task | ABC-6 | sum6 | Closed | "Fire"&"Water"
Story | ABC-7 | sum7 | Closed | "Fire"&"Water"

Table T2 shows the correct characters in Epic name.

What I want to achieve:

Type | Key | Percent done
Epic | ABC-1 | 33
Epic | ABC-2 | 100
Epic | ABC-8 | 0


Despite this I did manage to make it work with a rather crude solution:

I wrapped T1 in a table transformer macro, got the html codes and created a long REPLACE() chain with the most probable special characters in my region:

SELECT *,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T1.'Epic Name', "&#x20;", ""), "&#x21;", "!"), "&#x22;", "\""), "&quot;", "\""), "&#x23;", "#"), "&#x24;", "$"), "&#x25;", "%"), "&#x26;", "&"), "&amp;", "&"), "&#x27;", "'"), "&#x28;", "("), "&#x29;", ")"), "&#x2A;", "*"), "&#x2B;", "+"), "&#x2C;", ","), "&#x2D;", "-"), "&#x2E;", "."), "&#x2F;", "/"), "&#x3A;", ":"), "&#x3B;", ";"), "&#x3C;", "<"), "&lt;", "<"), "&#x3D;", "="), "&#x3E;", ">"), "&gt;", ">"), "&#x3F;", "?"), "&#x40;", "@"), "&#x5B;", "["), "&#x5C;", ""\""), "&#x5D;", "]"), "&#x5E;", "^"), "&#x5F;", "_"), "&#x60;", "`"), "&#x7B;", "{"), "&#x7C;", "|"), "&#x7D;", "}"), "&#x7E;", "~"), "&#xA0;", " "), "&nbsp;", " "), "&#xB4;", "´"), "&#xC0;", "À"), "&#xC1;", "Á"), "&#xC2;", "Â"), "&#xC4;", "Ä"), "&#xC5;", "Å"), "&#xC8;", "È"), "&#xC9;", "É"), "&#xCC;", "Ì"), "&#xCD;", "Í"), "&#xD2;", "Ò"), "&#xD3;", "Ó"), "&#xD4;", "Ô"), "&#xD5;", "Õ"), "&#xD6;", "Ö"), "&#xD9;", "Ù"), "&#xDA;", "Ú"), "&#xDB;", "Û"), "&#xDC;", "Ü"), "&#xDF;", "ß"), "&#xE0;", "à"), "&#xE1;", "á"), "&#xE4;", "ä"), "&#xE8;", "è"), "&#xE9;", "é"), "&#xEC;", "ì"), "&#xED;", "í"), "&#xF2;", "ò"), "&#xF3;", "ó"), "&#xF5;", "õ"), "&#xF6;", "ö"), "&#xF9;", "ù"), "&#xFA;", "ú"), "&#xFC;", "ü"), "&#x2013;", "–"), "&#x2014;", "—"), "&#x2018;", "‘"), "&#x2019;", "’"), "&#x201A;", "‚"), "&#x201C;", "“"), "&#x201D;", "”") as 'JoEpicName'
FROM T1

After that I wrapped this and T2 in another table transformer and counted the issues etc.:

SELECT
T1.'T',
T1.'Key',
ROUND((COUNT(CASE WHEN T2.'Status' = "Closed" THEN 1 END) / COUNT(T2.'Key'))*100,0)+"%" as 'Percent done'
FROM T1
LEFT JOIN T2 ON T2.'Epic Name' = T1.'JoEpicName'
GROUP BY T1.'T', T1.'Key'
ORDER BY T1.'Key' ASC

This does the trick for now, but I wonder if there's a more elegant, non-brute-force solution. :) (sorry for the crude tables, for some reason I could not post it proper tables)

Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2021

Now I see why you couldn't use the simple Pivot macro and the second table for your case - I didn't think about the empty Epics, decided that maybe you merge the tables to somehow reuse them further or smth.

What concerns the first query, I've shared the case with our developers - if they come up with a better/shorter solution, I'll return to this thread.

Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 29, 2021

Hi @Kántor Tibor ,

Here I am with an alternative solution from our developers: they suggest setting up a new method for the htmlDecode string (globally or via the HTML macro).

Wed 5-1.png

Wed 5-2.png

Wed 5-3.png

The query for the HTML macro:

<script>

String.prototype.htmlDecode = function()

{ var doc = new DOMParser().parseFromString(this, "text/html");

return doc.documentElement.textContent;

}

</script>

The query for the Table Transformer macro:

SELECT *, T1.'Column'->htmlDecode() as 'Epic Name' FROM T*

Hope this workaround may be helpful.

Like # people like this
Kántor Tibor September 29, 2021

Thanks @Katerina Kovriga _Stiltsoft_ ! This is a nice solution! I set it up as a global user macro since html macro is not enabled in our instance.

Preeti Bhole April 1, 2024

Thanks @Kate , this solution worked for me too

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
7.13.0
TAGS
AUG Leaders

Atlassian Community Events