How can I create a flat table with a few custom fields from Jira using Atlassian Analytics?

[Diego Mastroianni]

In Jira Advanced Issue Search, I can include a few custom columns in the results and extract that as a flat table. When I create a query in Analytics, I need to retrieve data from the "Jira Issue" table which has custom fields as rows (Name and Value). Say I have 2 jira issues and 2 custom fields, I'd like to see a table with 2 rows (one per issue) and 3 columns (issue key and the 2 custom fields I select).

Right now I can see the results in 4 rows (2 per issue). I thought "Pivot" would help, but it is greyed out and I can't find documentation on it.

I could create small queries one variable at a time and then try to join the tables. This is fine for 2 custom fields, but I want to see 10+, and that seems like brute force to me. Is there a more elegant way?

[Jessie Turpin · Accepted solution]

Hi Diego! 

If you'd like to have the custom fields be their own columns and don't want to create one query per custom field, you could consider creating custom columns in the Issue field table. This would allow you to use fewer queries in your chart while also having each custom field be a column. 

Here is an example piece of code of how you could create a custom column in the Issue field table based on one of your custom fields: 

CASE WHEN name = 'Custom field name' THEN value END

Note: Custom columns added to the schema will only be available in visual mode queries. 

If you have any trouble creating the custom column(s), please don't hesitate to create a support ticket and our support team will be able to help you further. 

Cheers,

Jessie

[Diego Mastroianni]

Thank you Jessie. This is helpful. Sorry for missing the reply earlier.

No problems in creating a custom column. I just had to be a bit more specific and add:

CASE WHEN `Issue field`.`name` = 'CUSTOM FIELD NAME' THEN `Issue field`.`value` END

Otherwise it was throwing an error of ambiguous columns when trying to merge with jira_issue table.

For other people looking for this, the next question would be that if you have multiple fields, say Custom 1, Custom 2, Custom 3, you would end up with 3 rows for the same issue ID, each with one of the custom fields populated. You can apply a "MAX" function to each of them to have them being reported in a single row.