Did you know… …That there’s a Training Video for this topic? Click here to check it out!
The SQL Report Builder allows you to directly query your data warehouse, view the results, and quickly transform them into a chart. The best part about using SQL to build reports is that you don’t need to wait on update cycles to iterate on columns you create. If the results don’t look quite right, you can quickly edit and re-run the query until things match your expectations.
In this article, we’ll walk you through the ins and outs of using the SQL Report Builder. After you’ve learned your way around, check out our SQL for visualizations tutorial or try optimizing some of the queries you’ve written.
Here’s an overview of what we’ll cover in this article:
To get started creating a new SQL report, click Report Builder or the Add Report button at the top of any dashboard. In the Report Picker screen, click SQL Report Builder to open the SQL editor.
To edit a report, click the gear () icon in the top right corner of a SQL-based chart and click Edit.
Let’s get started!
Writing a query
Following the guidelines for query optimization, write a query in the SQL editor.
Important! Metrics in SQL reports When you insert a metric into a SQL report, the current definition of the metric will be used.
If the metric is updated in the future, the SQL report will not reflect the changes. You will have to manually edit the report to have the changes take effect.
Using the buttons at the top of the sidebar, you can toggle between lists of tables and metrics available for use in the SQL Report Builder. If you don’t see what you’re looking for in the list, try searching for it using the search bar at the top of the sidebar.
You can also use the sidebar in the SQL editor to insert metrics, tables, and columns directly into your queries by hovering over them and clicking the Insert button:
Running the query and viewing results
When you’re done writing your query, click the Run Query button. The results will display in a table below the SQL editor:
If something looks amiss in the results, you can edit the query and re-run it until you’re satisfied.
You might sometimes see messages below the editor with EXPLAIN in them. If you see one of these, that means that your query hasn’t run and needs a bit of fine-tuning.
After you’re done editing your query, you can move onto either creating a visualization or saving your work to a dashboard.
Creating a visualization
To create a visualization with your query results, click the Chart tab in the Results pane. In this tab, you’ll select:
- The Series, or the column you want to measure, such as Items sold.
- The Category, or the column you want to use to segment your data, such as acquisition source.
- The Labels, or X-axis values.
Here’s a quick look at what the visualization process looks like:
For a detailed walk through of how to create a visualization, refer to our Creating visualizations from SQL queries tutorial.
Saving the report
Before you can save your work, you’ll have to give the report a name. Remember to follow the best practice guidelines for naming and choose something that clearly conveys what the report is!
Click the Save button at the top right corner of the SQL editor and select the report Type (Chart or Table). To wrap things up, select the dashboard to save the report to and click the Save to Dashboard button.
Analyze Your Data
SQL Report Builder
The SQL Report Builder gives you the power to directly query your data warehouse, view the results, and quickly transform them into a report. Using SQL also allows you to utilize SQL functions that aren’t available in the Visual or Cohort Report Builders, thus giving you greater control over your data.
We’d like to mention that calculated columns created using SQL aren’t dependent on update cycles, meaning you can iterate on them as you please and immediately see results.
Note that this only applies to the structure of the column, not the freshness of the data. Fresh data is still dependent on successfully completed update cycles.
|This is perfect for…||This isn’t so great for…|
|Intermediate/advanced analysts||Beginners - you need to know SQL.|
|The SQL savvy||Simple analyses - writing a query can be more work than simply using the Visual Report Builder.|
|Building one-time-use calculated columns||Sharing with others - consider your audience: do they understand SQL? If not, they may be confused by how the report is built.|
|Data with one-to-many relationships|
|Testing a new column or analysis|
Cohort Report Builder
Unlike the Visual Report Builder, the Cohort Report Builder is meant for a single purpose - analyzing and identifying behavioral trends of similar user groups over time. Using the Cohort Report Builder doesn’t require any SQL savvy, so you can dive right in without hesitation if you’re just starting out.
|This is perfect for…||This isn’t so great for…|
|Intermediate/advanced analysts||Beginners - you’ll need practice defining cohorts.|
|Identifying behavioral trends over time||Qualitative analysis - it can be done, but requires our assistance.|
If you want to try something a bit more challenging, why not try writing a query that’s optimized for visualization? Check out our Creating visualizations from SQL queries tutorialto get started.