Once you’ve learned how to query Jira custom fields, you can begin learning other ways to query those custom fields in Atlassian Analytics. This article will cover how to turn your Jira custom field into a custom column or custom table that you can easily query in multiple charts. One benefit of turning your Jira custom field into a custom column or custom table is that it can simplify the complexity of the chart(s) on your dashboard.

Querying Jira custom fields as a custom column

Custom columns can be added to an existing table in your Atlassian Data Lake data source. Any custom column you create will need to reference a column within that table. If you want to reference a column from a different table you will need to create a custom table instead. To create a custom column for your Jira custom field, follow these steps:

1. In Atlassian Analytics, select Data from the top navigation bar.

2. Select the Atlassian Data Lake connection that you would like to customize the schema.

3. In your Atlassian Data Lake connection, navigate to the Schema tab.

4. Scroll down until you see the "Issue field" table under “Jira family of products”. Then click the caret to expand the table.

5. Select Add Custom Column to begin adding the custom column to the “Issue field” table.

6. A modal will open where you can name the custom column, set it as a measure or dimension, and then write the SQL used to create the custom column.

In this example, we’ll create a custom column based on a custom field named “Goal”. The SQL used to create the custom column for our custom field is:

CASE WHEN `Name` = 'Goal' THEN `Value` ELSE 'No goal' END

7. Select Add Column to save the custom column to your “Issue field” table.

Now you will be able to easily query your custom field in any visual mode queries you create.

Querying Jira custom fields as a custom table

A custom table is similar to a custom column, with the exception that the SQL used to create a custom table can reference any table within the data source’s schema. To create a custom table with your Jira custom field(s) follow these instructions:

1. In Atlassian Analytics, select Data from the top navigation bar.

2. Select the Atlassian Data Lake connection that you would like to customize the schema.

3. In your Atlassian Data Lake connection, navigate to the Schema tab.

4. Select Add Custom Table at the top of your Atlassian Data Lake connection’s schema.

5. A modal will open where you can name the custom table and provide the SQL used to create the table.

In this example, we’ll create a custom table where we can turn each custom field into it’s own unique column in the table. Additionally, we can include the issue key or any other columns of our choosing.

WITH `Custom fields` AS (
  SELECT * FROM (SELECT `jira_issue`.`issue_key` AS `Issue key`, `jira_issue_field`.`Name`, `jira_issue_field`.`Value`
FROM `jira_issue`
INNER JOIN `jira_issue_field` ON `jira_issue_field`.`issue_id` =`jira_issue`.`issue_id`
WHERE `jira_issue_field`.`Name` IN ('Goal', 'Gated', 'Approver groups')
	)
PIVOT (MAX(`Value`)
		   FOR `Name` IN('Goal', 'Gated', 'Approver groups'))
  )
SELECT * FROM `Custom fields`

When copying and pasting this SQL into your custom table modal, you’ll need to switch the references of ‘Goal’, ‘Gated’, and ‘Approver Groups’ to the name of your desired custom field(s). Be sure any capitalization in your custom field name(s) matches how the field appears in the Jira products.

6. Select Add Table to save the custom table to your Atlassian Data Lake connection’s schema.

Now you will be able to easily query the custom fields in your custom table in any visual mode queries.