Create custom tables and columns

Write SQL that generates custom columns or tables in your data source. Rather than storing the data results, Atlassian Analytics stores the SQL and inserts it directly into your visual mode query.

Here are some common use cases for custom columns and tables:

• Use a CASE statement to create a column that categorizes rows.

• Flatten your database by creating a custom table that joins multiple tables together.

• Create simple calculated columns, such as converting cents to dollars.

• Define standard key metrics and calculations for your teammates.

Create a custom column

To create a custom column:

1. Select Data from the global navigation.

2. Select the data source that you want to add a custom column to.

3. Go to the Schema tab.

4. Expand the table you’d like to add the custom column to.

5. Select Add custom column.

6. Provide the required settings and SQL to create the custom column.

7. Select Add column to save.

When you create a custom column, it will appear like any other column in the table when you’re in the Schema tab. But when you view its column properties, you’ll see that its SQL type is custom.

Once your custom column is ready, you can use it in your visual mode queries.Learn more about how to use custom columns.

Custom column settings

Grouping

If you’re creating a category or other groupable value, select Dimension. If your value is numeric, select Measure.

Aggregated

If you’re aggregating a measure, such as taking a count or an average of something, select the Aggregated checkbox.

Type

If your custom column is a dimension, select the data type of your column. Measures are given type Number by default.

This does not convert your custom column to the specified data type. You’re simply telling Atlassian Analytics how the column can be used. To convert your column’s data type, use data source-specific syntax in the custom column’s SQL query.

SQL

Provide the SQL query to generate your custom column. Remember to use the SQL syntax specific to your data source type.

Custom columns can only use columns from the table you’re creating them in, so do not include the SELECT keyword or FROM clause in the SQL you provide. If you need to create a custom column that uses more than one table in the schema, you’ll need to create a custom table instead.

A custom column example

In the jira_issue table, we want to always convert the time_spent_secs column into minutes. Rather than having to divide the time_spent_secs by 60 every time we create a chart, we’ll create a custom column to do this for us.

To create this custom column, we do the following:

1. In the Schema tab of our data source’s settings, we expand the Jira issue table.

2. We select Add custom column.

3. We give the custom column a name and description.

4. For the Grouping, we select Measure because our custom column will only contain numeric values.

5. We deselect Aggregated.

6. For the SQL, we use time_spent_secs/60.

7. We select Add column to save.

Create a custom table

Unlike custom columns, you can create custom tables using columns for multiple tables in the schema. This can be especially useful for flattening two tables.

To create a custom table:

1. Select Data from the global navigation.

2. Select the data source that you want to add a custom table to.

3. Go to the Schema tab.

4. Select Add custom table.

5. Give your custom table a name.

6. Provide the SQL to create the custom table. Unlike custom columns, you can use a complete SQL statement to create your custom table—just make sure your SQL statement doesn’t end in a semicolon (;). Atlassian Analytics injects this SQL as a subquery into your visual mode queries, so ending with a semicolon will cause it to fail.

7. Optionally select Format query to apply formatting to your SQL statement.

8. Select Add table to save.

When you create a custom table, it will appear like any other table in the Schema tab You’ll know which tables are custom tables because they’ll have a tag labeled Custom table next to their table name.

Once your custom table is ready, you can use it in your visual mode queries. Learn more about how to use custom tables.

Atlassian Analytics evaluates your SQL and displays any error messages in the schema viewer. Avoid setting aliases in the SQL statement. Instead, first save the custom table then set column aliases like you normally would in a regular table. If an alias in the SQL query does not match the column name, you’ll get an error when you query the column.