The SQL Report Builder is a report builder with options: you can run a query for the sole purpose of retrieving a table of data, or you can turn those results into a report. In this article, we’ll give you some tips for building a visualization from a SQL query. Here's a look at everything we'll cover:
SQL report basics
Before we dive into the how-to, let’s go over the terminology we use in the SQL Report Builder.
Series
The column that you want to measure is referred to as a Series in the SQL Report Builder. Common examples are revenue, items sold, and marketing spend.
At least one column must be set as a Series to create a visualization.
Category
The column you want to use to segment your data is called a Category. This is just like the Group By feature in the Visual Report Builder.
For example, if you want to segment your customers’ lifetime revenue by their acquisition source, the column that contains acquisition source would be specified as the Category. More than one column can be set as a Category.
Note: Dates/timestamps can also be used as Categories. They are just another column of data in your query and must be formatted and ordered as desired in the query itself.
Labels
These are applied as x-axis labels. When analyzing data trending over time, the year and month columns are generally specified as labels. More than one column can be set to be Label.
Now that we’ve got the terminology down, let’s move on to the query.
Writing the query
Before you dive into your query, there are a few things you should keep in mind:
- The SQL Report Builder uses Redshift SQL.
- If you’re creating a report with a time series, be sure to
ORDER BYthe timestamp column(s). This will ensure that the timestamps are plotted in the right order on the report. - The
EXTRACT()function is great to use for parsing out the day, week, month, or year of the timestamp. This is useful when the the time interval you’d like to use on the report is daily, weekly, monthly or yearly.
To get started, open up the SQL Report Builder by clicking Report Builder > SQL Report Builder.
As an example, let’s consider this query that returns the monthly total number of items sold for each product:
SELECT SUM("qty") AS "Items Sold", "products's name" AS "product name",
EXTRACT(year from "Order date") AS "year",
EXTRACT(month from "Order date") AS "month"
FROM "items"
WHERE "products's name" LIKE '%Jeans'
GROUP BY "products's name", "year","month"
ORDER BY "year" ASC,"month" ASC
LIMIT 3500
This query would return this table of results:
Click for a closer look!
Creating the visualization
Now that we have our results, how do we go about creating the visualization? To get started, click the Chart tab in the Results pane. This will display the Chart settings tab.
When a query is first executed, the report may look inscrutable because all columns in the query are plotted as Series:
Click for a closer look!
Don’t worry; we’ll fix that in just a moment. For our example, we want this to be a line chart that trends over time. To create it, we’ll use these settings:
Series
The Items sold column must be chosen as the Series since we want to measure it. After you define a Series column, you’ll see a single line plotted in the report.
Category
In our case, we want to view each product as a different line in the report. To do this, we'll set Product name as the Category.
Labels
The columns year and month must be used as labels on the x-axis to be able to view Items Sold as trending over time.
Note that the query must contain an ORDER BY clause on the labels if they are date/time columns.
Here’s a quick look at how we created this visualization, from running the query to setting up the report:
Click for a closer look!
Selecting a chart type
The Pie Chart option should be used if there is only a single Series selected in the chart settings. You'll get some interesting results otherwise.
In our example, we're using the Line Chart type. You can use a different chart type if you like - simply click the icons above the chart options section to change it:
Saving the visualization
That’s it! If you want to use this report again, give the report a name and click the Save button in the top right corner when you’re happy with how the results look.
In the dropdown, select Chart as the Type and then a dashboard to save the report to.
Wrapping up
We know what we covered in this article was pretty basic, but you have to walk before you can run. Our goal here was to familiarize you with the terminology used in the SQL Report Builder and give you a solid foundation for creating SQL visualizations.
Want to take it a step further? Check out our query optimization tutorial or our list of SQL resources.