Table transformer - Count issues in epic, show percentage done

[Kántor Tibor]

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 T1	Percent done
epic 1	10
epic 2	30

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!

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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]

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}]

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}]

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).

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.

[Kántor Tibor]

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]

Thanks @Kate , this solution worked for me too