Create a chart

Creating a Chart with the UI

Forest Admin provides a straightforward UI to configure the charts you want.
First, turn on the Layout Editor mode (1), then click Add a new chart (2).
To add a new dashboard, click on + New while in Layout Editor mode (0).
Then add a Name and optionally a Description (1) and select a chart type (2):
Next, in Simple mode, you need to provide the following information, depending on your chart type:
    1 collection
    1 aggregate function (count, sum, …)
    1 group by field
    1 time frame (day, week, month, year) option.
    1 or multiple filters.

Creating a Chart with SQL

This option is only available to Starter plan and higher. However, it's free to try in non-production environments.
The Query mode is only available for SQL databases.
For security reasons, only SELECT queries are allowed.
The Query mode has been designed to provide you with a flexible, easy to use and accessible interface when hard questions need to be answered. Simply type SQL queries using the online editor and visualize your data graphically.
The syntax of the SQL examples below can be different depending on the database type (SQLite, MySQL, Postgres, SQL Server, etc.). Please, refer to your database documentation for more information.

Single value

The returned column must be named value. In the following example, we simply count the number of customers.
1
SELECT COUNT(*) AS value
2
FROM customers;
Copied!

Single value (with growth percentage)

The returned columns must be named value and previous. In the following example, we simply count the number of Appointments booked in January 2018 and compare this value to the number of Appointments booked in the previous month.
1
SELECT current.count AS value, previous.count AS previous
2
FROM (
3
SELECT COUNT(*)
4
FROM appointments
5
WHERE start_date BETWEEN '2018-01-01' AND '2018-02-01'
6
) as current, (
7
SELECT COUNT(*)
8
FROM appointments
9
WHERE start_date BETWEEN '2017-12-01' AND '2018-01-01'
10
) as previous;
Copied!

Repartition

The returned columns must be named key and value. In the following example, we simply count the number of transactions distributed by status.
1
SELECT transactions.status AS key, COUNT(*) AS value
2
FROM transactions
3
GROUP BY status;
Copied!

Time-based

The returned columns must be named key and value. In the following example, we simply count the number of appointments per month.
1
SELECT DATE_TRUNC('month', start_date) AS key, COUNT(*) as value
2
FROM appointments
3
GROUP BY key
4
ORDER BY key;
Copied!

Objective

This chart is not available for Mongodb databases yet.
The returned columns must be named key and objective. In the following example, we set manually the objective to 750.
1
SELECT COUNT(orders) AS value, 750 AS objective
2
FROM orders
3
WHERE orders.created_at < date_trunc('year', now());
Copied!

Leaderboard

This chart is not available for Mongodb databases yet.
The returned columns must be named key and value and LIMIT must be defined. In the following example, we limited the leaderboard to 10 items.
1
SELECT companies.name AS key, SUM(transactions.amount) AS value
2
FROM transactions
3
JOIN companies ON transactions.beneficiary_company_id = companies.id
4
GROUP BY key
5
ORDER BY value DESC
6
LIMIT 10;
Copied!
Last modified 1yr ago