Generate a recurring report with SQL

In this example, we'll create a SQL task and a corresponding schedule to generate a recurring report from a Postgres database.

To follow along, you'll need a DB you can connect to. We'll be using the Demo DB, may already be present as a resource in your workspace. If you don't have the Demo DB or would like to connect to your own database, make sure you add a PostgreSQL or MySQL resource first.

Create a task

First, let's create a new SQL task from the UI. Visit the home page, click the New... button, and finally click Task to reach the Create a Task page.

Select "SQL" as the task type, and give your task a readable name. For this example, we'll call it Top 5 Accounts.

We'll want to restrict our query by country, so let's add a new parameter. We'll call it Country, and leave it as a required Short text parameter. Notice that it's automatically assigned a slug. You can give it a description here, too.

Click continue. In this section, you'll build your task. Select the Demo DB (or the name of the resource you want to connect to) as your Database resource. In the Query field, write a select query to generate your report. In this example, we're looking for the company name, country, and total dollars of the top five accounts by total dollars in a particular country.

sql
Copied
1
SELECT company_name, country, total_dollars
2
FROM accounts
3
WHERE country = :country
4
ORDER BY total_dollars DESC
5
LIMIT 5;

In the Query arguments section, make sure you add country as an argument, with a value of {{params.country}}. We use query arguments here to avoid SQL injection attacks.

Click continue. In this section, you can define a custom timeout on the task as well as additional permissions. For this example, the defaults are perfectly acceptable.

Click Create task to finish.

Set up the schedule

Select Create schedule from the dropdown under Edit task on the task page.

Give your schedule a readable name, an optional description, and an appropriate schedule. This will be a weekly report, so we're scheduling it to run on every Monday using cron syntax.

In the Parameters section, we can define the parameter values that we want to use when this schedule runs. This is a report of accounts in the USA, so we'll add that as the country.

Select Create new schedule to finish.

Wrapping up

You've set up a recurring weekly report! The schedule page will show you a list of recent runs generated by the schedule and when the next three runs will be.

Click through any of these runs to see the output of the query.