Our documentation now lives on the Atlassian Support site at https://support.atlassian.com/analytics/resources/.
What is SQL mode?
- Cat Africa
Queries are the cornerstone for creating charts. This is where you’ll pull the data you want to show. All charts must start with an initial query. To help you remember this, all new charts always have the first "Query" step conveniently added and expanded!
If you feel more comfortable creating SQL queries from scratch, you can do so by using SQL mode in your “Query” step. If you added columns or filters in visual mode then switched to SQL mode, you’ll see the same auto-generated SQL query that also appears in the SQL preview in visual mode’s advanced drawer. The key difference here is you can edit that SQL query to include more complex SQL functions that may not be feasible to do via visual mode.
1. Choose a data source
Before you can start building your query, you need to decide from where to query the data. Choose one of your connected data sources by selecting the data sources dropdown.
Like in visual mode, you can only query one data source at a time in SQL mode. If you’d like to query multiple data sources in your chart, you’ll need to create a query for each data source then merge those queries together in Visual SQL.
2. Select SQL mode
To use SQL mode, select SQL in your query.
Building queries in visual mode then switching to SQL mode is a great way to generate the basic structure of return values—and even joins—before editing the query with more specific needs.
3. Write your SQL query
The SQL syntax used for your SQL mode query must be the syntax for your data source’s underlying database type. However, the SQL syntax in all other Visual SQL steps is SQLite.
Use Command + Shift + F to nicely format your SQL query.
4. Execute your query
When you feel like your query is ready, select Run Query to execute it. When visual mode is the active query mode, the auto-generated SQL query is executed. The result table and chart preview will update with your query results.
You can also use Cmd + Enter (for Mac) or Ctrl + Enter to run your query.
After running your first query, you can start adding other Visual SQL steps to further transform your data.
Switching between visual and SQL mode
Making changes and executing the query in visual mode updates the query in SQL mode, but this isn’t true for the reverse—making changes and executing the query in SQL mode doesn’t affect what’s shown in visual mode. If you’re switching between visual mode and SQL mode, your changes to visual mode don’t impact your returned results until you re-execute your query from visual mode. Also, be aware that re-executing the query in visual mode may override changes you’ve made in SQL mode.
Executed SQL query and completion time
After executing your query, you can view the amount of time it took to complete next to the “Run query” button. Selecting the completion time shows the executed query, including the values used for any variable controls referenced in your query.
