Jira Issues macro - Issue Type as text

[David_Bakkers]

When using the Jira Issues macro in Confluence, the value returned for the issue Type field isn't text, it's the icon for that issue type:

Is there a way to return the text value of the Issue Type, like 'Story', 'Epic', 'Risk' etc?

When exporting the table to a CSV, the Type column is always empty. Also, when using third party charting or filtering macros, the icon has no meaning to be used for text filtering or charting.

There's feature request JRASERVER-60945 for being able to return the Issue Type as text, not an icon, using JQL.... but it's been in 'gathering interest' state since 2016!

[Dominic Lagger · Accepted solution]

Hi @David_Bakkers 

I think, the problem isn't the JIRA Issue macro. The problem is in JIRA itself. Because there, it's also not possible to get the item type as a text. 

And for both products, I'm not aware, this is possible. 

The only workaround would be a custom field in JIRA, with is dependent of the issue tpye field. 

Regards, Dominic

[David_Bakkers]

I have a solution. Well, for Confluence anyhow.

If you have the Stiltsoft Table Filter and Charts for Confluence app, you can use the Table Transformer macro to parse the contents of the 'T' column in the Jira Issues macro's table, find each instance of the issue type and convert it into a new column called 'Type'

Here is the SQL to use in the Table Transformer macro:

SELECT
  'T',
  CASE
    WHEN
      'T' = "Epic"
    THEN
      "Epic"
    WHEN
      'T' = "Story"
    THEN
      "Story"
    WHEN
      'T' = "Sub-task"
    THEN
      "Sub-task"
  END
  as 'Type',
  *
FROM
  T1

(Add in any extra WHEN / THEN cases to cover all the issue types in your project or what you're expecting Jira to return)

Here is an example of a resulting table:

Presto! The issue types as text.

[Andrey Khaneev _StiltSoft_]

Hi David,

The query can be simplified to

SELECT 'T' 'Type' FROM T1

[David_Bakkers]

Hello Andrey

I tried that simplified query, but the resulting table only had one column, 'Type'.

I found I still had to select the other columns to add, like this:

SELECT 'T' 'Type', 'Summary', 'Status', 'Test Result' FROM T1

This also excluded the source 'T' column from the resulting table.

[Dominic Lagger]

Hi @David_Bakkers 

This is awesome! Nice one! Thanks for sharing your solution! 

[Jacob Jung]

Hi @David_Bakkers  and  @Dominic Lagger 

I know this is a little late to the party, but this is the work around I found. I came across this post as I needed to compare the issue type against other content that is string based. 

Thank you for sparking the idea with your post! If we can compare column 'T' with text, then there is text already there. Confluence is likely modifying the format once it hits the table and could be why we see the picture.

The work around I found was to simply cast the column to remove the additional post processing.

For the following examples, an issue type of Task is used. This would be at the same level as a Story.

Casting

---

Straight inline cast to string using VARCHAR()

SELECT
   T1.'T' AS T,
   CAST( T1.'T' AS VARCHAR(64)) AS 'Type'
FROM T1

Output

The 'Type' cell will then contain plain text Task

Casting with Hyper Link

---

If you would like to keep the issue hyper link that the originally comes with the picture of 'T' in the same cell. It can be re-inserted with FORMATWIKI.

Where the '_______' is your cloud instance.

DECLARE @var_url AS VARCHAR(64) = "https://_______.atlassian.net/browse/";

SELECT
   T1.'T' AS T,
   FORMATWIKI("[" + CAST(T1.'T' AS VARCHAR(64)) + "|" + CONCAT(@var_url + T1.'Key') + "]") AS 'Type'
FROM T1

Output

The 'Type' cell will then contain hyper linked text Task to the associated Jira issue.

All the best.