Use custom tables and columns

After creating a custom column or custom table, you can then use it in your visual mode queries.

Use a custom column

To use a custom column:

1. Open the chart editor by either selecting Create > Chart from the global navigation or selecting Add chart from a dashboard.

2. Make sure your query is using Visual mode. You can’t use custom columns in SQL mode.

3. Select the data source containing the custom column.

4. Add your custom column. Custom columns appear in the schema browser just as any other column would, and you can add them as a column or filter in your visual mode query as you normally would.

5. Build the rest of your visual mode query.

6. Select Run query.

If you switch to SQL mode, you can see what the generated query looks like. The custom SQL you’ve written to create the custom column is inserted directly into the query.

Use a custom table

To use a custom table:

1. Open the chart editor by either selecting Create > Chart from the global navigation or selecting Add chart from a dashboard.

2. Make sure your query is using Visual mode. You can’t use custom tables in SQL mode.

3. Select the data source containing the custom table.

4. Add your column from your custom table. Custom tables appear in the schema browser just as any other table would. You can add columns from your custom table as a column or filter in your visual mode query as you normally would.

5. Build the rest of your visual mode query.

6. Select Run query.

When you use a column from your custom table in visual mode queries, Atlassian Analytics injects the custom SQL you’ve written to create the custom table into the FROM clause of the query.

Usage tips

• When a custom column or table is edited and re-saved, queries are automatically updated for all of the charts using that custom column or table.

• Setting foreign keys on columns in a custom table works the same way as setting foreign keys on regular tables in your data source.

• When updating a table without making changes to the query (for example, SELECT * FROM table), you must make at least one change to the query for the query to be evaluated. For example, you can add a temporary comment or dummy column.

Use a custom column to change a column’s data type

There may be times when you need to change a column’s data type, but you can’t change it in your original data source. You can use custom columns to change the column’s data type in Atlassian Analytics (but it won’t make the change to your original data source).

To change a column’s data type, create a custom column in the table containing the column that you want to change. For ease of use, we recommend giving your new custom column the same name as the original column.

In the custom column’s SQL, you’ll need to use the syntax specific to your data source to convert the column’s data type.

Converting unsupported data types

Atlassian Analytics does not support some data types such as JSON objects, and XML objects. If this is the case, the SQL type of the column in your Atlassian Analytics schema will show as Unsupported type. While you could create custom tables or custom columns to flatten those objects or cast their values as strings, we recommend handling this in your original data source, not in Atlassian Analytics, so you have more flexibility and better long-term management.

SQL syntax examples to change strings to dates

Here are some common examples of how to change string data types to dates:

Google BigQuery syntax

CAST("table_name"."column_name" AS DATE)

MySQL syntax

STR_TO_DATE(table_name.column_name, '%m-%d-%Y')

PostgreSQL syntax

DATE(table_name.column_name)