Reports#

Think of reports as chunks of data that business users can access on their own, without having to bug someone who knows SQL.

Report examples#

  • All transactions (plus details) for a particular customer
  • Unprocessed orders received in a particular date range
  • Data processing errors for email sending

All of these reports are useful to customer service, sales, or other business teams for investigating customer issues, or monitoring business performance.

Here's a screenshot of a report

How Reports Work#

A report is a SQL query in which you have specified user parameters. These parameters are extracted and presented for the end-user to fill out in a form. When the user runs the report they are given the generated dataset to manipulate to download.

Let me walk you through an example from Beekeeper. This report shows the number of emails we deliver by day. I want business users to be able to get this information for an arbitrary date range.

Paramterized SQL#

Report SQL

This is a simple SQL query in which I have added some parameters. I'm telling beekeeper that instead of providing a start-date and end-date, I want the user to be able to specify these values. By including a parameter type, I'm limiting the user to that type of value.

In this example I'm telling Beekeeper I want the user to provide two values:

  1. Start Date
  2. End Date

I'm also limiting them so they have to enter a valid date.

Generated Forms#

Saving this report generates the following user interface for users to use. Notice they are prompted for dates.

Report Form

Results#

When the report is run, it generates a resultset for the user to interact with or download, nowhere do they need to look at SQL.

Results Screenshot

Reference#

Parameter Format#

You use a parameter by surrounding it with double curly braces, like this:

{{parameterName: parameterType}}

The parameter type is not case sensitive, so datetime, DateTime, and dateTime all do the same thing.

A Quick Example:

select * from users 
where
  first_name = {{usersFirstName: String}}
and
  created_at::date >= {{startDate: Date}}

Supported Parameter Types#

  • No type (string)
  • String - any text value, will be surrounded by quotation marks
  • RawString - any text value, will not be surrounded by quotation marks
  • Date - Date with no time
  • DateTime - Date and time
  • Int - Any whole number
  • Double - Any number with a decimal
  • StringList - Any number of strings (one per line)
  • IntList - Any number of whole numbers (one per line)