Magento Business Intelligence Help Center

Using the SQL Report Builder

BROUGHT TO YOU BY

Avatar

Hey! You need Admin permissions to do this.

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:

  1. Writing a query
  2. Running the query and viewing results
  3. Creating a visualization
  4. Saving the report

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 (Options) icon in the top right corner of a SQL-based chart, then 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:

Inserting a table into the SQL editor.

Click for a closer look!

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:

Running the query and viewing results.
Click for a closer look!

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;
  • and the Labels, or x-axis values. 

Here's a quick look at what the visualization process looks like:

For a detailed walkthrough of how to create a visualization, please 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:

Naming and saving the report to a dashboard.
Click for a closer look!

Wrapping up

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 tutorial to get started.

Was this article helpful?
1 out of 1 found this helpful

Comments

  • Avatar
    ferdinand

    The sql report builder is missing a lot of tables that I can see from the data warehouse. How do I add them? The tool is always complaining that the table does not exist.

Powered by Zendesk