Table Transformer - Clickable links to Jira Issues after calculations

Matias G Labrana

Good day community,

Taking in consideration the following tutorial link: 

 

Creating clickable links to the Jira issues

I know is possible to create Jira links to the issues based after the following query in Table transformer:

<span>SELECT</span>

<span>'Status'</span><span>,</span>

FORMATWIKI<span>(</span><span>"["</span><span>+</span> <span>COUNT</span><span>(</span><span>*</span><span>)</span> <span>+</span><span>"|"</span> <span>+&nbsp; &nbsp;</span>&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span>"<a href="https://jira.stiltsoft.com/issues/jql=issuekey%20in%20" target="_blank" rel="nofollow noopener noreferrer">https://jira.stiltsoft.com/issues/jql=issuekey%20in%20</a>("&nbsp;</span><span>+&nbsp;</span>

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ARRAY<span>(</span>T1<span>.</span><span>'Key'</span><span>)</span> <span>+</span> <span>")"</span> <span>+</span> <span>"]"</span><span>)</span> <span>AS</span> <span>'COUNT'</span>

<span>FROM</span> T1 <span>GROUP</span> <span>BY</span> T1<span>.</span><span>'Status'</span>

<span>UNION</span> <span>ALL</span> CORRESPONDING

<span>SELECT</span> <span>"Total"</span> <span>AS</span> <span>'Status'</span><span>,</span>

FORMATWIKI<span>(</span><span>"["</span><span>+</span> <span>COUNT</span><span>(</span><span>*</span><span>)</span> <span>+</span><span>"|"</span> <span>+</span>

<span>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"<a href="https://jira.stiltsoft.com/issues/?jql=issuekey%20in%20" target="_blank" rel="nofollow noopener noreferrer">https://jira.stiltsoft.com/issues/?jql=issuekey%20in%20</a>("</span> <span>+</span>&nbsp;

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ARRAY<span>(</span>T1<span>.</span><span>'Key'</span><span>)</span> <span>+</span> <span>")"</span> <span>+</span> <span>"]"</span><span>)</span> <span>AS</span> <span>'COUNT'</span>

<span>FROM</span> T1

 

Now - I would like to know if we can write a query after calculations, because for example when I want to make a sum or group some values, the linkage is disappearing, for example here:

Thanks!

Stiltsoft support

Hi @Matias G Labrana ,

As you've mentioned the Table Transformer macro, we suppose that you are talking about our Table Filter, Charts & Spreadsheets for Confluence app.

The described example works with a Jira Issues table where each ticket (KEY-1, KEY-2 and so on) is placed into a separate row. Later these rows are grouped by statuses, and the ranges of grouped keys are taken as arrays to form the ending of the Jira link.

In your example you just sum the already grouped tickets. And it is expected behavior that the result number can't have any link.

So, you may just use an external SELECT

SELECT *

FROM

(SELECT * FROM T*)

and look into this simplified example of creating clickable links.

We mean, that in the internal SELECT you perform the calculations and in the external SELECT you take the required Jira link from your Jira (all the issues in these three statuses) and "put it on the calculated number".

If you need further assistance, please refer to our support portal.